Michelangelo Pagliarini Michelangelo Pagliarini - 1 month ago 7
SQL Question

sql oracle case 'from keyword not found where expected'

I'm trying to select an existing column and creating another one using the code indented, but I'm getting alternatively the 'ORA-00923: FROM keyword not found where expected' error or the 'missing keyword' error.
(I'm a beginner)

Can you please help me out?

Edit: id is an integer, description is a string
I want to create a column for each id that will serve as a flag when the description value is matching 'VALUE1' or 'VALUE2'. In table 1 there is simply the mapping from the identifier of the values to the string, on table 2 I have all the records.

select id as ID,

'cast( select description from db.table_1
join db.table_2 on table_2.id = table_1.id
case when (table_1.description in ('VALUE1', 'VALUE2')) then '1'
else '0'
end
) as boolean' as DesiredColumnName,

from db.table_2

Answer

Try escaping ' with ''

 select id as ID, 

'cast( select description from db.table_1 
join db.table_2 on table_2.id = table_1.id
case when (table_1.description in (''VALUE1'', ''VALUE2'')) then ''1''
else ''0'' 
end
) as boolean' as DesiredColumnName,
 from db.table_2

But probably you don't want to do that select with long varchar at all.

I think this is what you're looking for

select t2.id, decode(t1.description, ''VALUE1'', 1, ''VALUE2'', 1 ,0) as DesiredColumnName 
 from db.table2 t2 inner join db.table1 t1 on (t2.id = t1.id)