The correct form of - listagg with case statement

user_10457877 0
Viewed 1395

I get error: ORA-00907: missing right parenthesis, but i can't find the wrong stuff.

(select listagg(sp.name
||' : '||
(case when count(distinct sp.name) < 1 then NULL else szf.piece END) as cou_1, ',') 
WITHIN GROUP (ORDER BY sp.name,cou_1)
from sk_positions sp, sk_stock_f SZF, sk_stock SZ 
where SZF.CODE_ID =SK.ID AND SP.RID = SZF.RID_U AND SZF.ID_SZ = SZ.ID
and sp.sk_u = (%sk%) and SZF.piece != 0)

I think, i have problem in listagg - case.

listagg oracle11g case oracle sql
2 Answers

The error is here:

szf.piece END) as cou_1
               ^

You cannot alias a sub expression but only the complete expression for the column.In Listagg, it should come after the within group () is complete.

something like this

WITHIN GROUP (ORDER BY sp.name,cou_1) as cou_1
user_79985910

This is not allowed in Oracle.You are missing single quotes for wildcard search criteria.

sp.sk_u = (%sk%)

Correct syntax is below ( only LIKE works with such criteria search not = )

sp.sk_u LIKE ('%sk%')

Complete query should be as below 

(select listagg(sp.name ||' : '||(case when count(distinct sp.name) < 1 
                                       then NULL 
                                       else szf.piece 
                                       END) as cou_1, ',') 
WITHIN GROUP (ORDER BY sp.name,cou_1)
from sk_positions sp, sk_stock_f SZF, sk_stock SZ 
where SZF.CODE_ID =SK.ID
      AND SP.RID = SZF.RID_U 
      AND SZF.ID_SZ = SZ.ID
      and sp.sk_u LIKE ('%sk%') 
      and SZF.piece != 0)
user_108778600
Related