1 Reply Latest reply: Dec 4, 2012 4:10 AM by Nicolas.Gasparotto RSS



      I have requirement to retrieve employees whose ethnicity's primary indicator does not contain multiple Y's or N's. based on this i am trying to update another table.
      for example:
      emplid     Name     Ethnicity     Primary indicator for Ethnicity
      1001     Anna     Asian     N
      1001     Anna     White     N
      1001     Anna     Black     N
      1002     Hari     Asian     Y
      1002     Hari     White     Y
      1003     gopi     Asian     Y
      1003     gopi     white     N
      1004     Gia     Black     N
      1005 John white Y
      1005 John Asian N

      In the above data, i should get only employee id 1003 for Asian ,1004 and 1005 (only the white ethnicity info).

      Edited by: user1121514 on Dec 3, 2012 9:22 PM
        • 1. Re: sql
          Even though it's not Peoplesoft related, here's my my try you can start with :
          SQL> select id,firstname,ethnicity,indicator
            2  from   (select mytable.*,
            3          count(decode(indicator,'N',1)) over (partition by id) as indicator_n,
            4          count(decode(indicator,'Y',1)) over (partition by id) as indicator_y
            5          from mytable)
            6  where  indicator_n<=1 and indicator_y <=1;
                  ID FIRS ETHNI I
          ---------- ---- ----- -
                1003 gopi Asian Y
                1003 gopi white N
                1004 Gia  Black N
                1005 John white Y
                1005 John Asian N