CODEWITHSUNDEEP
sqloracle-databaselistaggnvl
Jacked_Nerd
Jacked_Nerd

Reputation: 237

Using nvl in a listagg SQL Oracle

Having problems using an nvl call within a listagg.

I am new to SQL so I am still iffy on the syntax..

listagg(nvl(asgu.name, ', '),'In waiting')
   within group (order by asgu.name) as asgu_name,   
   case
   when asgu.name is not null then asgu.name
   else 'In Waiting'
   end 'In waiting',

Error: ORA-00923: FROM keyword not found where expected

Upvotes: 1

Views: 3037

Answers (2)

Gary Myers
Gary Myers

Reputation: 35401

The line end 'In waiting' is incorrect. You can use a space in a column alias, but it needs to be in double quotes (ie end "In waiting" )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Your listagg() code should probably look like this:

listagg(coalesce(asgu.name, 'In waiting'), ', ') within group (order by asgu.name) as asgu_names,

The other code doesn't make sense. Either asgu.name is being aggregated or it is not being aggregated. You should have an aggregation function for the second expression.

Upvotes: 3

Related Questions