This discussion is archived
9 Replies Latest reply: Jan 4, 2013 1:16 AM by Purvesh K RSS

Multiple columns

976273 Newbie
Currently Being Moderated
Hi I have table
EMP
----
EMPID ENAME EMPID2 EMPID3
1 abc 12 297
The emp empid1,empid2,empid3 are valid only if they are in the ID validation table
ID
--
1
12
297

I would like to write a code that would look for all the ids from emp in ID table and give us a message if any ID is invalid like
select empid,empid2,empid3 from emp exists in (select id from ID) else "invalid empid1","invalid empid2","invalid empid3".Please help me
  • 1. Re: Multiple columns
    Purvesh K Guru
    Currently Being Moderated
    Something this way:
    Is this what you need?
    with emp as
    (
      select 1 id1, 'abc' name, 12 id2, 297 id3 from dual
    ),
    ids as
    (
      select 1 id from dual union all
      select 2 id from dual union all
      select 11 id from dual union all
      select 151 id from dual union all
      select 13 id from dual union all
      select 297 id from dual
    )
    select id1, 
           max(decode(id1, id, 'ID1 is Valid', 'ID1 Invalid')) id1_result,
           max(decode(id2, id, 'ID2 is Valid', 'ID2 Invalid')) id2_result,
           max(decode(id3, id, 'ID3 is Valid', 'ID3 Invalid')) id3_result
      from emp join ids
        on (id in (id1, id2, id3)) group by id1;
    
    ID1                    ID1_RESULT   ID2_RESULT   ID3_RESULT   
    ---------------------- ------------ ------------ ------------ 
    1                      ID1 is Valid ID2 Invalid  ID3 is Valid
  • 2. Re: Multiple columns
    jeneesh Guru
    Currently Being Moderated
    SQL> with emp as
      2  (
      3   select 1 empid,'abc' ename,12 empid2,297 empid3
      4   from dual union all
      5   select 2 empid,'xyz' ename,15 empid2,150 empid3
      6   from dual union all
      7   select 200 empid,'xyz' ename,1500 empid2,160 empid3
      8   from dual
      9  ),
     10  validation_tab as
     11  (
     12   select 1 id from dual union all
     13   select 12 id from dual union all
     14   select 297 id from dual union all
     15   select 150 id from dual
     16  )
     17  select e.empid,e.ename,e.empid2,e.empid3,
     18         max(decode(e.empid,v.id,'Valid','Invalid'))  empid1_status,
     19         max(decode(e.empid2,v.id,'Valid','Invalid')) empid2_status,
     20         max(decode(e.empid3,v.id,'Valid','Invalid')) empid3_status
     21  from emp e left outer join validation_tab v
     22      on ( v.id in (e.empid,e.empid2,e.empid3) )
     23  group by e.empid,e.ename,e.empid2,e.empid3;
    
         EMPID ENA     EMPID2     EMPID3 EMPID1_ EMPID2_ EMPID3_
    ---------- --- ---------- ---------- ------- ------- -------
           200 xyz       1500        160 Invalid Invalid Invalid
             2 xyz         15        150 Invalid Invalid Valid
             1 abc         12        297 Valid   Valid   Valid
  • 3. Re: Multiple columns
    BluShadow Guru Moderator
    Currently Being Moderated
    Like this?
    SQL> ed
    Wrote file afiedt.buf
    
      1  with EMP as (select 1 as empid, 'abc' as ename, 12 as empid2, 297 as empid3 from dual union all
      2               select 2, 'xyz', 34, 87 from dual)
      3      ,ID as (select 1 as id from dual union all
      4              select 12 from dual union all
      5              select 297 from dual union all
      6              select 34 from dual)
      7  --
      8  select emp.*
      9        ,decode(id1.id,null,'empid is Invalid','empid is Valid') as empid_check
     10        ,decode(id2.id,null,'empid2 is Invalid','empid2 is Valid') as empid2_check
     11        ,decode(id3.id,null,'empid3 is Invalid','empid3 is Valid') as empid3_check
     12  from   emp
     13         left outer join id id1 on (empid = id1.id)
     14         left outer join id id2 on (empid2 = id2.id)
     15*        left outer join id id3 on (empid3 = id3.id)
    SQL> /
    
         EMPID ENA     EMPID2     EMPID3 EMPID_CHECK      EMPID2_CHECK      EMPID3_CHECK
    ---------- --- ---------- ---------- ---------------- ----------------- -----------------
             1 abc         12        297 empid is Valid   empid2 is Valid   empid3 is Valid
             2 xyz         34         87 empid is Invalid empid2 is Valid   empid3 is Invalid
  • 4. Re: Multiple columns
    976273 Newbie
    Currently Being Moderated
    Thank you. This helps a lot. Can we create a table only with invalid ids from the results like
    results
    ---------
    INVALID_ ID 1
    INVALID_ID 27
  • 5. Re: Multiple columns
    Manik Expert
    Currently Being Moderated
    Another way:
    WITH emp AS
            (SELECT 1 id1,
                    'abc' name,
                    12 id2,
                    297 id3
               FROM DUAL
             UNION ALL
             SELECT 2 id2,
                    'XYZ' name,
                    555 id2,
                    2 id3
               FROM DUAL),
         ids AS
            (SELECT 1 id FROM DUAL
             UNION ALL
             SELECT 2 id FROM DUAL
             UNION ALL
             SELECT 11 id FROM DUAL
             UNION ALL
             SELECT 151 id FROM DUAL
             UNION ALL
             SELECT 13 id FROM DUAL
             UNION ALL
             SELECT 297 id FROM DUAL)
    SELECT id1,
           name,
           CASE
              WHEN INSTR (str, ',' || id1 || ',') > 0 THEN 'VALID'
              ELSE 'INVALID'
           END
              res1,
           CASE
              WHEN INSTR (str, ',' || id2 || ',') > 0 THEN 'VALID'
              ELSE 'INVALID'
           END
              res2,
           CASE
              WHEN INSTR (str, ',' || id3 || ',') > 0 THEN 'VALID'
              ELSE 'INVALID'
           END
              res3
      FROM emp,
           (SELECT ',' || listagg (id, ',')||',' WITHIN GROUP (ORDER BY 1) str
              FROM ids);
    Cheers,
    Manik.
  • 6. Re: Multiple columns
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Purvesh,

    you solution is nice but in case no id1, id2 or id3 are present in ID validation table no rows are returned.

    I would change it using outer join in this way:
    with emp as
    (
      select   1 id1, 'abc' name,  12 id2, 297 id3 from dual union all
      select 501 id1, 'abc' name, 502 id2, 503 id3 from dual
    ),
    ids as
    (
      select 1 id from dual union all
      select 2 id from dual union all
      select 11 id from dual union all
      select 151 id from dual union all
      select 13 id from dual union all
      select 297 id from dual
    )
    select id1, id2, id3,
           max(decode(id1, id, 'ID1 is Valid', 'ID1 Invalid')) id1_result,
           max(decode(id2, id, 'ID2 is Valid', 'ID2 Invalid')) id2_result,
           max(decode(id3, id, 'ID3 is Valid', 'ID3 Invalid')) id3_result
      from emp left outer join ids
        on (id in (id1, id2, id3)) group by id1, id2, id3;
    
           ID1        ID2        ID3 ID1_RESULT   ID2_RESULT   ID3_RESULT  
    ---------- ---------- ---------- ------------ ------------ ------------
             1         12        297 ID1 is Valid ID2 Invalid  ID3 is Valid
           501        502        503 ID1 Invalid  ID2 Invalid  ID3 Invalid 
    This will work in all cases.

    Regards.
    Al
  • 7. Re: Multiple columns
    jeneesh Guru
    Currently Being Moderated
    user10283379 wrote:
    Thank you. This helps a lot. Can we create a table only with invalid ids from the results like
    results
    ---------
    INVALID_ ID 1
    INVALID_ID 27
    create table invalid_id as
    with emp as
        (
         select 1 empid,'abc' ename,12 empid2,297 empid3
         from dual union all
         select 2 empid,'xyz' ename,15 empid2,150 empid3
         from dual union all
         select 200 empid,'xyz' ename,1500 empid2,160 empid3
         from dual
        ),
       validation_tab as
       (
        select 1 id from dual union all
        select 12 id from dual union all
        select 297 id from dual union all
        select 150 id from dual
       ),
    all_empid as
    (
      select empid
      from emp
      union all 
      select empid2
      from emp
      union all
      select empid3
      from emp
    )
    select e.empid
    from all_empid e left outer join validation_tab v
        on ( e.empid = v.id )
    where v.id is null
    UNION ALL can be changed to UNION if empids are not unique in EMP table
  • 8. Re: Multiple columns
    Purvesh K Guru
    Currently Being Moderated
    Thanks Alberto. I missed that thought.
  • 9. Re: Multiple columns
    Manik Expert
    Currently Being Moderated
    Check this.
    CREATE TABLE invalidids AS
       WITH emp AS
               (SELECT 1 id1,
                       'abc' name,
                       12 id2,
                       297 id3
                  FROM DUAL
                UNION ALL
                SELECT 2 id2,
                       'XYZ' name,
                       555 id2,
                       2 id3
                  FROM DUAL),
            ids AS
               (SELECT 1 id FROM DUAL
                UNION ALL
                SELECT 2 id FROM DUAL
                UNION ALL
                SELECT 11 id FROM DUAL
                UNION ALL
                SELECT 151 id FROM DUAL
                UNION ALL
                SELECT 13 id FROM DUAL
                UNION ALL
                SELECT 297 id FROM DUAL),
            agg AS
               (SELECT ',' || listagg (id, ',') WITHIN GROUP (ORDER BY 1)  str
                  FROM ids),
            t AS
               (SELECT id1,
                       name,
                       id2,
                       id3,
                       CASE
                          WHEN INSTR (str, ',' || id1 || ',') > 0 THEN 'VALID'
                          ELSE 'INVALID'
                       END
                          chkid1,
                       CASE
                          WHEN INSTR (str, ',' || id2 || ',') > 0 THEN 'VALID'
                          ELSE 'INVALID'
                       END
                          chkid2,
                       CASE
                          WHEN INSTR (str, ',' || id3 || ',') > 0 THEN 'VALID'
                          ELSE 'INVALID'
                       END
                          chkid3
                  FROM emp, agg)
       SELECT 'INVALID' chkreason, chkid
         FROM (SELECT DECODE (chkid1, 'INVALID', id1) chkid FROM t
               UNION ALL
               SELECT DECODE (chkid2, 'INVALID', id2) FROM t
               UNION ALL
               SELECT DECODE (chkid3, 'INVALID', id3) FROM t)
        WHERE chkid IS NOT NULL;
    Cheers,
    Manik.

    Edited by: Manik on Jan 4, 2013 3:10 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points