2 Replies Latest reply: Sep 18, 2006 10:21 AM by Laurent Schneider RSS

    SQL HELP

    52325
      Table a has the following fields

      Scenario 1
      =========
      serno, formname, status
      1 FORM1 YES
      2 FORM1 NO
      3 FORM1 NO

      or

      Scenario 2
      =========
      serno, formname, status
      1 FORM1 NO
      2 FORM1 NO
      3 FORM1 NO


      table b has the following fields

      formname
      FORM1

      if formname in table a = formname in table b then
      if there is a record with status = 'YES' in table a, then print only the YES row
      if there are no YES status in table , then print only one "NO" row.

      Thanks
        • 1. Re: SQL HELP
          Nicolas.Gasparotto
          SQL> create table test(serno number, formname varchar2(10), status varchar2(3));

          Table créée.

          SQL>
          SQL> insert into test
            2  select 1, 'FORM1', 'YES' from dual
            3  union all
            4  select 2, 'FORM1', 'NO' from dual
            5  union all
            6  select 3, 'FORM1', 'NO' from dual
            7  union all
            8  select 1, 'FORM2', 'NO' from dual
            9  union all
          10  select 2, 'FORM2', 'NO' from dual
          11  union all
          12  select 3, 'FORM2', 'NO' from dual;

          6 ligne(s) créée(s).

          SQL>
          SQL>
          SQL> select serno, formname, status
            2  from
            3  (select serno, formname, status, row_number() over (partition by formname order by decode(status,'YES',0,1)) rn
            4   from   test)
            5   where rn=1;

               SERNO FORMNAME   STA
          ---------- ---------- ---
                   1 FORM1      YES
                   1 FORM2      NO

          SQL>
          if there is a record with status = 'YES' in table a, then print only the YES row
          Did you want all the rows where it set to yes in case of there is more than one ?
          In that case :
          SQL> update test set status = 'YES' where formname='FORM1' and serno=2;

          1 ligne mise à jour.
          SQL> select serno, formname, status
            2  from
            3  (select serno, formname, status,
            4          row_number() over (partition by formname order by decode(status,'YES',0,1)) rn
            5   from   test)
            6*  where rn=1 or status = 'YES'
          SQL> /

               SERNO FORMNAME   STA
          ---------- ---------- ---
                   1 FORM1      YES
                   2 FORM1      YES
                   1 FORM2      NO

          SQL>
          Nicolas.

          Message was edited by:
          N. Gasparotto
          • 2. Re: SQL HELP
            Laurent Schneider
            also possible, without analytics, is this one :
            SQL> select max(serno) keep (dense_rank first order by status desc) serno,
             2   formname, max(status) status 
             3   from test group by formname;
                 SERNO FORMNAME   STA
            ---------- ---------- ---
                     1 FORM1      YES
                     3 FORM2      NO