ORA-00923 with CASE command

beatlejus

beatlejus

Member²
Hi,

I am just trying to define a separate field in my report called "EWU" with the CASE command, but get the above mentioned error. What's going wrong?

My query:
select IASSALHEAD.COUNTRY,
'EWU' = CASE iassalhead.country
when iassalhead.country in ('B','BE','SF','FI','F','FR','GR','GR','IRL','IE','I','IT','L','LU','NL','NL','A','AT','P','PT','E','ES') then 'EWU'
else 'Non-EWU'
end
from IAS.IASSALHEAD IASSALHEAD,
IAS.IASSALITEM IASSALITEM

Any help available?
 
The 'EWU' = CASE iassalhead.country construction seems strange. I assume this is what you want:

select IASSALHEAD.COUNTRY,
CASE when iassalhead.country in ('B','BE','SF','FI','F','FR','GR','GR','IRL','IE','I','IT','L','LU','NL','NL','A','AT','P','PT','E','ES') then 'EWU'
else 'Non-EWU'
end
from IAS.IASSALHEAD IASSALHEAD,
IAS.IASSALITEM IASSALITEM
 
Hi,

works fine except from the fact, that I do not know how to give the field a name and sort it. Field name is shown as

Casewheniassalhead.countryin('

I can rename it through the layout tab, but how to sort then with that field.
 
You can provide an alias (AS EWU_COUNTRY) and base your sort order on it (ORDER BY 2, 1) like this:

select IASSALHEAD.COUNTRY,
CASE when iassalhead.country in ('B','BE','SF','FI','F','FR','GR','GR','IRL','IE','I','IT','L','LU','NL','NL','A','AT','P','PT','E','ES') then 'EWU'
else 'Non-EWU'
end AS EWU_COUNTRY
from IAS.IASSALHEAD IASSALHEAD
ORDER BY 2, 1
 
You can also use the alias name in the ORDER BY clause, e.g:

Code: 
SELECT CASE 'bananas'
            WHEN 'x' THEN 'y'
            ELSE 'z'
       END AS bananas
FROM   dual
ORDER BY bananas
 
You must log in or register to reply here.
Back
Top