Sub query value

[former_member547429]

I am working on the following SQL:

select a.field1 a.field2 b.field3

(select FIELD from table3 where KEY = a.field1) as field4

from TAB1 a inner join TAB2 b on a.key = b.key.

My requirement is display value of FIELD FROM table3 if there is a entry else 'N/A' in field4. I am trying the following and receiving error "NTO clause not allowed for this SELECT statement: subquery is not allowed here"

Any pointers on how can i achieve this...

select a.field1 a.field2 b.field3 
case when (select FIELD into lv_value from TABLE3 where TABLE3.KEY = a.key) is null then 'N/A'
else.
lv_value
end as field4 
from TAB1 a inner join TAB2 b on TAB1.KEY = TAB2.KEY.

[SergioG_TX · Accepted solution]

it looks like you would need to include your table3 as a left join instead of a sub select... then do a case when clause where you have the subquery

[former_member547429 · Accepted solution]

Thanks for the response Sergio. I was able to accomplish this using

coalesce