cancel
Showing results for 
Search instead for 
Did you mean: 

Sub query value

0 Kudos
226

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.
View Entire Topic
SergioG_TX
Active Contributor
0 Kudos

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

0 Kudos

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

coalesce