Skip to Content
avatar image
Former Member

Sub query value

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.
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    May 17 at 05:32 PM
    -1

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

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

      coalesce
  • avatar image
    Former Member
    May 18 at 05:33 PM
    How do I use the left outer join in this scenario; main issue I am facing is how do i conditionally pass the value from select and set 'N/A'. Do you any sample code that i can refer to? 
    Add comment
    10|10000 characters needed characters exceeded