CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
Saad Azhar
Saad Azhar

Reputation: 55

CASE WITH MULTIPLE SELECT STATEMENTS

I'm trying to put a select statement within a case but I'm unable to put it properly.

The first case statement is fine, but the second one is the problem.

Can somebody guide me please with the correct syntax,

select u.unit_id,
       u.order_code,
       u.order_item,
       u.roll_sheet,
       u.date_wound,
       u.date_shipped,
       u.unit_status,
       u.warehouse_code,
       (case
         when u.unit_id_turnup is not null then
          (select u1.onput_id
             from unit u1
            where u.unit_id_turnup = u1.unit_id)
         else
          u.onput_id
       end) "onput id",
       
       (case
         when u.unit_id_turnup is not null then
          (select CASE WHEN U1.UNIT_ID_TURNUP IS NOT NULL
             from unit u1 then (select u3.onput_id
                                  from unit u3
                                 where u1.unit_id_turnup = u3.unit_id) else u1.onput_id)
         else
          u.onput_id
       end) "onput id2",
  from unit u

Upvotes: 0

Views: 791

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

I can speculate that the code that you intend is:

select . . . ,
       coalesce(u1.onput_id, u.onput_id) as onput_id,
       coalesce(u2.onput_id, u1.onput_id, u.onput_id) as onput_id2
from unit u left join
     unit u1
     on u.unit_id_turnup = u1.id left join
     unit u2
     on u1.unit_id_turnup = u2.id

Without sample data and a clear explanation, this is really just a guess. However, this appears to be what you are attempting.

Upvotes: 1

Related Questions