6 Replies Latest reply: Jan 28, 2013 12:39 AM by Naren RSS

    Complex logic

    Naren
      Hi


      I have used Analytical functions but couldnt reach the requirment.

      I have a requirement where i need to consider a set as two columns and for that set need to check two exactly available combinations.

      findings are to be done partition by ID and CD and for that paritition need to find exactly available values say 'Paid' and 'AdjD'. If that partition set holds more than these two values or less than these two values then those records should not be picked.
      Example if the partition set has 'Paid','AdjD', 'AdjV' then these this set should not be considered, as it violates the exactly two distinct records match.

      below is the sample set:

      <br>

      WITH T AS
      (

      SELECT 7 ID, 444 CD, 'Paid' NM FROM DUAL
      UNION ALL
      SELECT 7 ID, 491 CD, 'Paid' NM FROM DUAL
      UNION ALL
      SELECT 7 ID, 491 CD, 'AdjD' NM FROM DUAL
      UNION ALL
      SELECT 230 ID, 444 CD, 'AdjD' NM FROM DUAL
      UNION ALL
      SELECT 230 ID, 444 CD, 'Paid' NM FROM DUAL
      UNION ALL
      SELECT 230 ID, 444 CD, 'AdjC' NM FROM DUAL
      UNION ALL
      SELECT 230 ID, 491 CD, 'Paid' NM FROM DUAL
      UNION ALL
      SELECT 6180917 ID, 444 CD, 'Paid' NM FROM DUAL
      UNION ALL
      SELECT 6180917 ID, 444 CD, 'AdjC' NM FROM DUAL
      UNION ALL
      SELECT 6180917 ID, 444 CD, 'AdjD' NM FROM DUAL
      UNION ALL
      SELECT 6180917 ID, 491 CD, 'Paid' NM FROM DUAL
      UNION ALL
      SELECT 11099353 ID, 444 CD, 'AdjD' NM FROM DUAL
      UNION ALL
      SELECT 11099353 ID, 491 CD, 'AdjV' NM FROM DUAL
      UNION ALL
      SELECT 11099353 ID, 491 CD, 'Paid' NM FROM DUAL
      UNION ALL
      SELECT 13093371 ID, 444 CD, 'Paid' NM FROM DUAL
      UNION ALL
      SELECT 13093371 ID, 444 CD, 'AdjD' NM FROM DUAL
      UNION ALL
      SELECT 13093371 ID, 444 CD, 'AdjC' NM FROM DUAL
      UNION ALL
      SELECT 13093371 ID, 491 CD, 'AdjD' NM FROM DUAL
      UNION ALL
      SELECT 13093371 ID, 491 CD, 'Paid' NM FROM DUAL
      UNION ALL
      SELECT 32139609 ID, 444 CD, 'Paid' NM FROM DUAL
      UNION ALL
      SELECT 32139609 ID, 444 CD, 'AdjV' NM FROM DUAL
      UNION ALL
      SELECT 32139609 ID, 491 CD, 'Paid' NM FROM DUAL
      )
      select * FROM T



      From above sample below records needs to be picked up.

      ID     CD     NM
      7     491     Paid
      7     491     AdjD
      13093371     491     AdjD
      13093371     491     Paid

      </br>

      Please help.



      I am using oracle 10g.

      Thanks,
      Naren
        • 1. Re: Complex logic
          Etbin
          Maybe NOT TESTED!
          select id,cd,nm
            from (select id,cd,nm,
                         sum(case when nm = 'Paid' then 1 end) over (partition by id,cd order by null
                                                                     rows between unbounded preceding and unbounded following
                                                                    ) n_paid,
                         sum(case when nm = 'AdjD' then 1 end) over (partition by id,cd order by null
                                                                     rows between unbounded preceding and unbounded following
                                                                    ) n_adjd,
                         sum(case when nm not in ('Paid','AdjD') then 1 end) over (partition by id,cd order by null
                                                                                   rows between unbounded preceding and unbounded following
                                                                                  ) n_other
                    from t
                 )
           where n_paid = 1
             and n_adjd = 1
             and n_other is null
          Regards

          Etbin
          • 2. Re: Complex logic
            Solomon Yakobson
            WITH T AS (
                       SELECT 7 ID, 444 CD, 'Paid' NM FROM DUAL UNION ALL
                       SELECT 7 ID, 491 CD, 'Paid' NM FROM DUAL UNION ALL
                       SELECT 7 ID, 491 CD, 'AdjD' NM FROM DUAL UNION ALL
                       SELECT 230 ID, 444 CD, 'AdjD' NM FROM DUAL UNION ALL
                       SELECT 230 ID, 444 CD, 'Paid' NM FROM DUAL UNION ALL
                       SELECT 230 ID, 444 CD, 'AdjC' NM FROM DUAL UNION ALL
                       SELECT 230 ID, 491 CD, 'Paid' NM FROM DUAL UNION ALL
                       SELECT 6180917 ID, 444 CD, 'Paid' NM FROM DUAL UNION ALL
                       SELECT 6180917 ID, 444 CD, 'AdjC' NM FROM DUAL UNION ALL
                       SELECT 6180917 ID, 444 CD, 'AdjD' NM FROM DUAL UNION ALL
                       SELECT 6180917 ID, 491 CD, 'Paid' NM FROM DUAL UNION ALL
                       SELECT 11099353 ID, 444 CD, 'AdjD' NM FROM DUAL UNION ALL
                       SELECT 11099353 ID, 491 CD, 'AdjV' NM FROM DUAL UNION ALL
                       SELECT 11099353 ID, 491 CD, 'Paid' NM FROM DUAL UNION ALL
                       SELECT 13093371 ID, 444 CD, 'Paid' NM FROM DUAL UNION ALL
                       SELECT 13093371 ID, 444 CD, 'AdjD' NM FROM DUAL UNION ALL
                       SELECT 13093371 ID, 444 CD, 'AdjC' NM FROM DUAL UNION ALL
                       SELECT 13093371 ID, 491 CD, 'AdjD' NM FROM DUAL UNION ALL
                       SELECT 13093371 ID, 491 CD, 'Paid' NM FROM DUAL UNION ALL
                       SELECT 32139609 ID, 444 CD, 'Paid' NM FROM DUAL UNION ALL
                       SELECT 32139609 ID, 444 CD, 'AdjV' NM FROM DUAL UNION ALL
                       SELECT 32139609 ID, 491 CD, 'Paid' NM FROM DUAL
                      )
            SELECT  ID,
                    CD,
                    NM
              FROM  (
                     SELECT  T.*,
                             SUM(DISTINCT CASE NM WHEN 'Paid' THEN 1 WHEN 'AdjD' THEN 2 ELSE 4 END) OVER(PARTITION BY ID,CD) X
                       FROM  T
                    )
              WHERE X = 3
            /
            
                    ID         CD NM
            ---------- ---------- ----
                     7        491 Paid
                     7        491 AdjD
              13093371        491 Paid
              13093371        491 AdjD
            
            SQL>  
            SY.

            Edited by: Solomon Yakobson on Jan 25, 2013 12:37 PM
            • 3. Re: Complex logic
              Frank Kulash
              Hi,

              This is similar to Etbin's solution, but a little more concise:
              WITH     got_cnts   AS
              (
                   SELECT     id, cd, nm
                   ,     COUNT (DISTINCT nm
                               ) OVER (PARTITION BY  id, cd)     AS total_cnt
                   ,     COUNT (DISTINCT CASE
                                      WHEN  nm IN ('AdjD', 'Paid')
                                      THEN  nm
                                        END
                              )     OVER (PARTITION BY  id, cd)     AS target_cnt
                   FROM    t
              )
              SELECT    id, cd, nm
              FROM       got_cnts
              WHERE       total_cnt     = 2
              AND       target_cnt     = 2
              ;
              The output is what you requested:
              `       ID         CD NM
              ---------- ---------- ----
                       7        491 AdjD
                       7        491 Paid
                13093371        491 AdjD
                13093371        491 Paid
              COUNT, like most of the other analytic functions, does not require an ORDER BY clause. I fnd it clearer, as well as easier, just to omit the ORDER BY clause when it's not needed.
              • 4. Re: Complex logic
                Solomon Yakobson
                Just a note, this will work assuming NM is NOT NULL. Otherwise it will return ID, CD combinations whis=ch have NM 'AdjD', 'Paid' and NULL.
                SQL> WITH T AS (
                  2             SELECT 7 ID, 444 CD, 'Paid' NM FROM DUAL UNION ALL
                  3             SELECT 7 ID, 491 CD, 'Paid' NM FROM DUAL UNION ALL
                  4             SELECT 7 ID, 491 CD, 'AdjD' NM FROM DUAL UNION ALL
                  5             SELECT 7 ID, 491 CD, NULL NM FROM DUAL
                  6            ),
                  7  got_cnts   AS
                  8  (
                  9   SELECT id, cd, nm
                 10   , COUNT (DISTINCT nm
                 11           ) OVER (PARTITION BY  id, cd) AS total_cnt
                 12   , COUNT (DISTINCT CASE
                 13          WHEN  nm IN ('AdjD', 'Paid')
                 14          THEN  nm
                 15            END
                 16          ) OVER (PARTITION BY  id, cd) AS target_cnt
                 17   FROM    t
                 18  )
                 19  SELECT    id, cd, nm
                 20  FROM   got_cnts
                 21  WHERE   total_cnt = 2
                 22  AND   target_cnt = 2
                 23  /
                
                        ID         CD NM
                ---------- ---------- ----
                         7        491 AdjD
                         7        491 Paid
                         7        491
                
                SQL> WITH T AS (
                  2             SELECT 7 ID, 444 CD, 'Paid' NM FROM DUAL UNION ALL
                  3             SELECT 7 ID, 491 CD, 'Paid' NM FROM DUAL UNION ALL
                  4             SELECT 7 ID, 491 CD, 'AdjD' NM FROM DUAL UNION ALL
                  5             SELECT 7 ID, 491 CD, NULL NM FROM DUAL
                  6            )
                  7  SELECT  ID,
                  8          CD,
                  9          NM
                 10    FROM  (
                 11           SELECT  T.*,
                 12                   SUM(DISTINCT CASE NM WHEN 'Paid' THEN 1 WHEN 'AdjD' THEN 2 ELSE 4 END) OVER(PARTITION BY ID,CD) X
                 13             FROM  T
                 14          )
                 15    WHERE X = 3
                 16  / 
                
                no rows selected
                
                SQL> 
                SY.
                • 5. Re: Complex logic
                  Naren
                  Thanks Alll :) this worked
                  • 6. Re: Complex logic
                    Rahul_India
                    How were the rows selected?
                    What was the logic?