9 Replies Latest reply: Jan 4, 2013 3:40 AM by Manik RSS

    Multiple columns

    976273
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        Thanks Alberto. I missed that thought.
                        • 9. Re: Multiple columns
                          Manik
                          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