Finding a value in multiple columns in Oracle table

Question

I have a table like below

ID         NUMBER 1  NUMBER 2   NUMBER 3    LOC
1-14H-4950          0616167    4233243      CA
A-522355            1234567                 TN
A-522357            9876543                 WY
A-522371            1112223                 WA
A-522423    1234567 2345678 1234567         NJ
A-A-522427  9876543 6249853 6249853         NJ

and I have a bunch of values (1234567, 9876543, 0616167, 1112223, 999999...etc) which will be used in where clause, if a value from where clause found in one of the three Number columns (Number 1 or Number 2 Number 3) then I will have to write that to output1 (its like VLOOKUP of Excel).

If the value is found in more than one of the three columns then it will be different output2 with a flag as MultipleMatches. If the value is not found in any of the three columns then it should be in Output2 with flag as No Match. I tried using self join and or clauses, but not able to get what I want.

I want to write the SQL to generate both outputs. Outputs will include all the columns from the above table. For eg:

Output 1 from above sample data will look like
ID         NUMBER 1  NUMBER 2   NUMBER 3    LOC
1-14H-4950          0616167    4233243      CA
A-522371            1112223                 WA

Output 2 will be like:

ID         NUMBER 1  NUMBER 2   NUMBER 3    LOC Flag
A-522423    1234567 2345678 1234567         NJ  Multiple Match
A-A-522427  9876543 6249853 6249853         NJ  Multiple Match
1234                                            No Match     

                                   

Answer 1

I want to write the SQL to generate both outputs.

One SELECT operator cannot produce two output sets.

The main question is, why split the output when that the difference is only in the FLAG column? If you really need two different output of the result, then you can do this:

1. (Rightly) create a common cursor for the query, where the FLAG column will be calculated and split the output screens already in the UI.

    drop table test_dt;
    create table test_dt as 
    select '1-14h-4950' id,null num1,616167 num2,4233243 num3,'ca' loc  from dual  
    union all 
    select 'a-522355',null ,1234567,null,'tn' from dual union all 
    select 'a-522357',null ,9876543,null,'wy' from dual union all 
    select 'a-522371',null ,1112223,null,'wa' from dual union all 
    select 'a-522423',1234567,2345678,1234567,'nj'  from dual union all 
    select 'a3-522423',null,null,null,'nj'  from dual union all 
    select 'a-a-522427',9876543,6249853,6249853,'nj'  from dual;
    --
    select 
        d.*,
        case when t.cc_ndv=0 and t.cc_null=3 then 'Not matching'
             when t.cc_ndv=(3-t.cc_null) then 'Once'
        else 'Multiplay match'     
        end flag     
        --t.cc_ndv,
        --t.cc_null
      from  test_dt d ,lateral(
           select  
              count(distinct  case level when 1 then num1
                                            when 2  then num2
                                            when 3  then num3
                                    end ) cc_ndv,
              count(distinct  case level when 1 then nvl2(num1,null,1)
                                            when 2  then nvl2(num2,null,2)
                                            when 3  then nvl2(num3,null,3)
                                    end ) cc_null
          from dual connect by level<=3 and sys_guid()is not null
          ) t;

Or

2. create a procedure(see to dbms_sql.return_result) that returns a some data sets. Process these data of cursors / datasets separately.