Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

how to get row in IN clause returns NULL values

Latha00May 14 2024

Hi All,

I have these table and query and Oracle version # 11.2

with xxc_person
as
select 123 person_id from dual
union all
select 100 person_id from dual
union all
select 200 person_id from dual;

with xxc_requester
as
select 101 person_id, 111 req_id from dual
union all
select 300 person_id, 222 req_id from dual
union all
select 401 person_id, 333 req_id from dual;


with xxc_trx_type
as
select 'Account Alias' trx_type, 111 req_id from dual;

SELECT (SELECT CASE
                 WHEN z.trx_type = 'Account Alias' THEN 000
                 ELSE NULL
               END
        FROM   xxc_person x
        WHERE  x.person_id IN(SELECT y.person_id
                              FROM   xxc_requester y
                              WHERE  z.req_id = y.req_id))
FROM   xxc_trx_type z 

IN clause y.person_id is returning NULL values but i need to get 000 values from this select eventhoug if it returns NULL values.
Comments
Post Details
Added on May 14 2024
4 comments
95 views