5 Replies Latest reply: Apr 29, 2010 11:00 AM by elmasduro RSS

    complex query

    elmasduro
      hi all, i have the following scenario that i cannot figure out. here is some sample data
      WITH table as
      (
      SELECT To_Date('1/02/2001','mm/dd/yyyy') eff_eff_date, 2 id, 111 num  FROM dual UNION all
      SELECT To_Date('1/20/2001','mm/dd/yyyy') eff_date, 2 id, 112 num FROM dual UNION all
      SELECT To_Date('10/31/2004','mm/dd/yyyy') eff_date, 2 id, 113 num  FROM dual UNION all
      SELECT To_Date('2/1/2004','mm/dd/yyyy') eff_date, 2 id, 114 num  FROM dual UNION all
      SELECT To_Date('10/02/2002','mm/dd/yyyy') eff_date, 2 id, 115 num FROM dual union all
      SELECT To_Date('1/5/2007','mm/dd/yyyy') eff_date, 2 id, 116 num  FROM dual union ALL
      
      SELECT To_Date('1/02/2002','mm/dd/yyyy') eff_date, 3 id, 117 num  FROM dual UNION all
      SELECT To_Date('2/25/2008','mm/dd/yyyy') eff_date, 3 id, 118 num FROM dual UNION all
      SELECT To_Date('1/30/2005','mm/dd/yyyy') eff_date, 3 id, 119 num FROM dual UNION all
      SELECT To_Date('4/21/2005','mm/dd/yyyy') eff_date, 3 id, 155 num  FROM dual UNION all
      SELECT To_Date('12/02/2002','mm/dd/yyyy') eff_date, 3 id, 111 num FROM dual UNION all
      
      SELECT To_Date('1/02/2010','mm/dd/yyyy') eff_date, 4 id, 144 num FROM dual 
      
      
      ),
      table2 AS
      (
      SELECT To_Date('1/2/2007','mm/dd/yyyy') eff_date, 2 id, 'test1' name FROM dual UNION ALL
      SELECT To_Date('1/9/2008','mm/dd/yyyy') eff_date, 2 id, 'test3' name  FROM dual UNION ALL
      SELECT To_Date('2/25/2008','mm/dd/yyyy') eff_date, 3 id, 'test2' name FROM dual UNION ALL
      SELECT To_Date('1/21/2009','mm/dd/yyyy') eff_date, 4 id, 'test4' name FROM dual UNION ALL
      SELECT To_Date('1/23/2010','mm/dd/yyyy') eff_date, 5 id, 'test4' name FROM dual
      ),
      
      
      
      EFF_EFF_DATE         ID     NUM
      1/2/2001            2     111
      1/20/2001         2     112
      10/31/2004       2     113
      2/1/2004         2     114
      10/2/2002         2     115
      1/5/2007         2     116
      1/2/2007         2     113    ---113 was taking from the max date in table1 <= 1/2/2007 (table 2)
      
      1/2/2001             2     111
      1/20/2001         2     112
      10/31/2004       2     113
      2/1/2004         2     114
      10/2/2002         2     115
      1/5/2007         2     116
      1/2/2009         2     116    ---116 was taking from the max date in table1 <= 1/2/2009(table2)
      
      1/2/2002         3     117
      2/25/2008         3     118
      1/30/2005         3     119
      4/21/2005         3     155
      12/2/2002         3     111
      2/25/2008         3     118  
      
      1/2/2010         4     144
      1/21/2009         4          --   1/21 is less than 1/2/2010 and there is no max date in table  <= to 1/21/2009 so num is null
      
      1/23/2010         5     NULL     --- 1/23 is not in table1 so pull it from table2 and diplay num as null
      i want to write a query that give me the output above. can anybody help? thanks in advance
        • 1. Re: complex query
          Frank Kulash
          Hi,

          This gets the results you requested:
          SELECT       t1a.eff_eff_date
          ,       t1a.id
          ,       t1a.num
          FROM       table1     t1a
          JOIN       table2     t2a     ON     t1a.id     = t2a.id
               --
              UNION ALL
               --
          SELECT       t2b.eff_date     AS eff_eff_date
          ,       t2b.id
          ,       MIN (t1b.num) KEEP (DENSE_RANK LAST ORDER BY t1b.eff_eff_date)     AS num
          FROM              table2       t2b
          LEFT OUTER JOIN      table1       t1b     ON     t1b.id               =  t2b.id
                                         AND     t1b.eff_eff_date     <= t2b.eff_date
          GROUP BY  t2b.eff_date
          ,            t2b.id
          ;
          Thnaks for posting the sample data in a useable form.
          It would help a lot if you explained how you get the results you want.
          For example:
          "The output will have one set of rows for every row in table2. The results I posted have a blank line between sets, but that blank line isn't part of the actual output.
          Each set will have one row from table2, and all the rows from table1 with the same id (if any; note id=5 exists only in table2).
          The rows taken from table1 simply have the data from table1.
          In the rows taken from table2, the num column is the taken from the last row (in order by eff_eff_date) in the same set that is no later than table2.eff_date."
          This is what I believe you want. Did I get it right?

          Do you need the output sorted as in your original message?
          If so, you'll need to add another column (or two) to uniquely identify which row of table2 each output row is associated with. Does table2 have a unique identifier? If it does, then you'll all that unique identifer to each branch of the UNION, and sort by that column first, then by eff_eff_date.
          If you don't want to display that row, then have your front end tool hide it (in SQL*Plus, use the COLUMN ... NOPRINT), or put the UNION in a sub-query, and don't include the unique identifier from table2 in the main SELECT clause (just the ORDER BY clause).
          • 2. Re: complex query
            elmasduro
            Frank, thanks a lot for your help. this is how it works.
            i have two tables, table and table2 are the names in the with clause. both tables have id and eff_date that can match in some cases and some cases will not.
            i can have the following scenario

            1. table has a row but there is no row in table2
            in this case i want to show the row from table and show num column from table2 as null

            2. table has a row(date, id combination) that doesnt match table2. table2 has many rows.
            in this case i want to show all rows from table2 and the row from table. the row that comes
            from table will have a num column value equal to the max date in table2 that is less or equal to the eff_date of the row in table

            3. there is a row in table and many rows in table2. the row in table matches one of the rows in table2(id,eff_date)
            in this case i want to show all rows from table2 and the row from table and num value for row in table will be max date in table2 that is less or equal to the eff_date date

            4. there is one row in table and one or more rows in table 2. the eff_date for all rows in table2 is greater than eff_date of the row in table
            in this case, display all rows from table2 and table. the row in tables will have a num column value of null because the eff_dt is less than the eff_date of all rows in table2 and there is no max date in table2 that is less or equal to eff date in table


            let me know if you have any questions.

            i noticed you used min function. i would think the max function will be use. i will anaylize your query and let you know.
            in the meanwhile, take a look at my explaination and let me know if your query will take care of the scenario above.

            thanks again
            • 3. Re: complex query
              Frank Kulash
              FHi,
              elmasduro wrote:
              Frank, thanks a lot for your help. this is how it works.
              i have two tables, table and table2 are the names in the with clause.
              "TABLE" is not a good table name, even in a WITH clause. Why not call it something reasonable, like table1?
              both tables have id and eff_date that can match in some cases and some cases will not.
              i can have the following scenario

              1. table has a row but there is no row in table2
              in this case i want to show the row from table and show num column from table2 as null

              2. table has a row(date, id combination) that doesnt match table2. table2 has many rows.
              in this case i want to show all rows from table2 and the row from table. the row that comes
              from table will have a num column value equal to the max date in table2 that is less or equal to the eff_date of the row in table

              3. there is a row in table and many rows in table2. the row in table matches one of the rows in table2(id,eff_date)
              in this case i want to show all rows from table2 and the row from table and num value for row in table will be max date in table2 that is less or equal to the eff_date date

              4. there is one row in table and one or more rows in table 2. the eff_date for all rows in table2 is greater than eff_date of the row in table
              in this case, display all rows from table2 and table. the row in tables will have a num column value of null because the eff_dt is less than the eff_date of all rows in table2 and there is no max date in table2 that is less or equal to eff date in table


              let me know if you have any questions.
              That's better, but still not very clear.
              From the description alone, it's very unclear what role id plays in this problem.

              I think I missed something in my earlier post.
              When there a row in table1 has the same date and id as a row in table2, then the output for that pair of rows should not be repeated.
              For example, in the output from my original query, there were two identical rows of output:
              EFF_EFF_DA         ID        NUM
              ---------- ---------- ----------
              02/25/2008          3        118
              02/25/2008          3        118
              Your posted output only had one row like that.
              To remove the duplicates, add a HAVING clause to the second branch of the UNION:
              SELECT       t1a.eff_eff_date
              ,       t1a.id
              ,       t1a.num
              FROM       table1     t1a
              JOIN       table2     t2a     ON     t1a.id     = t2a.id
                   --
                  UNION ALL
                   --
              SELECT       t2b.eff_date     AS eff_eff_date
              ,       t2b.id
              ,       MIN (t1b.num) KEEP (DENSE_RANK LAST ORDER BY t1b.eff_eff_date)     AS num
              FROM              table2       t2b
              LEFT OUTER JOIN      table1       t1b     ON     t1b.id               =  t2b.id
                                             AND     t1b.eff_eff_date     <= t2b.eff_date
              GROUP BY  t2b.eff_date
              ,            t2b.id
              HAVING       LNNVL (MAX (t1b.eff_eff_date) = t2b.eff_date)          -- This line is new
              Aside from the last line, this is exactly what I posted before.
              i noticed you used min function. i would think the max function will be use. i will anaylize your query and let you know.
              in the meanwhile, take a look at my explaination and let me know if your query will take care of the scenario above.
              MIN in this case means that when two or more rows have exactly the same eff_eff_date, then use the lower num. If the combination (eff_eff_date, id, num) is unique in table1, then this situation is impossible, but syntax demands that you provide for it anyway. It wouldn't matter if you used MIN, MAX, SUM, AVG or STRAGG: you just need some aggregate function that returns the unique num.

              Edited by: Frank Kulash on Apr 22, 2010 3:29 PM
              • 4. Re: complex query
                elmasduro
                Thanks a million Frank. i tested a couple of scenarios and seem to be working perfect. i will do a couple other scenario and let you know if encounter problem. thanks again
                • 5. Re: complex query
                  elmasduro
                  Hello Frank and any other member of this forum who would like to help. you help me out with the problem above. the sample data and output below is what i wanted.
                  WITH table1 as
                  (
                  SELECT To_Date('1/02/2001','mm/dd/yyyy') eff_eff_date, 2 id, 111 num  FROM dual UNION all
                  SELECT To_Date('1/20/2001','mm/dd/yyyy') eff_date, 2 id, 112 num FROM dual UNION all
                  SELECT To_Date('10/31/2004','mm/dd/yyyy') eff_date, 2 id, 113 num  FROM dual UNION all
                  SELECT To_Date('2/1/2004','mm/dd/yyyy') eff_date, 2 id, 114 num  FROM dual UNION all
                  SELECT To_Date('10/02/2002','mm/dd/yyyy') eff_date, 2 id, 115 num FROM dual union all
                  SELECT To_Date('1/5/2007','mm/dd/yyyy') eff_date, 2 id, 116 num  FROM dual union ALL
                   
                  SELECT To_Date('1/02/2002','mm/dd/yyyy') eff_date, 3 id, 117 num  FROM dual UNION all
                  SELECT To_Date('2/25/2008','mm/dd/yyyy') eff_date, 3 id, 118 num FROM dual UNION all
                  SELECT To_Date('1/30/2005','mm/dd/yyyy') eff_date, 3 id, 119 num FROM dual UNION all
                  SELECT To_Date('4/21/2005','mm/dd/yyyy') eff_date, 3 id, 155 num  FROM dual UNION all
                  SELECT To_Date('12/02/2002','mm/dd/yyyy') eff_date, 3 id, 111 num FROM dual UNION all
                   
                  SELECT To_Date('1/02/2010','mm/dd/yyyy') eff_date, 4 id, 144 num FROM dual 
                   
                   
                  ),
                  table2 AS
                  (
                  SELECT To_Date('1/2/2007','mm/dd/yyyy') eff_date, 2 id, 'test1' name FROM dual UNION ALL
                  SELECT To_Date('1/2/2009','mm/dd/yyyy') eff_date, 2 id, 'test3' name  FROM dual UNION ALL
                  SELECT To_Date('2/25/2008','mm/dd/yyyy') eff_date, 3 id, 'test2' name FROM dual UNION ALL
                  SELECT To_Date('1/21/2009','mm/dd/yyyy') eff_date, 4 id, 'test4' name FROM dual UNION ALL
                  SELECT To_Date('1/23/2010','mm/dd/yyyy') eff_date, 5 id, 'test4' name FROM dual
                  ),
                   
                   
                   
                  EFF_EFF_DATE         ID     NUM
                  1/2/2001            2     111
                  1/20/2001         2     112
                  10/31/2004       2     113
                  2/1/2004         2     114
                  10/2/2002         2     115
                  1/5/2007         2     116
                  1/2/2007         2     113    ---113 was taking from the max date in table1 <= 1/2/2007 (table 2)
                   
                  1/2/2001             2     111
                  1/20/2001         2     112
                  10/31/2004       2     113
                  2/1/2004         2     114
                  10/2/2002         2     115
                  1/5/2007         2     116
                  1/2/2009         2     116    ---116 was taking from the max date in table1 <= 1/2/2009(table2)
                   
                  1/2/2002         3     117
                  2/25/2008         3     118
                  1/30/2005         3     119
                  4/21/2005         3     155
                  12/2/2002         3     111
                  2/25/2008         3     118  
                   
                  1/2/2010         4     144
                  1/21/2009         4          --   1/21 is less than 1/2/2010 and there is no max date in table  <= to 1/21/2009 so num is null
                   
                  1/23/2010         5     NULL     --- 1/23 is not in table1 so pull it from table2 and diplay num as null
                  however, i want to make a slight change to the query and the output will be almost identical. if you look at the data above, you will see that table2 has
                  SELECT To_Date('1/2/2007','mm/dd/yyyy') eff_date, 2 id, 'test1' name FROM dual UNION ALL
                  SELECT To_Date('1/9/2008','mm/dd/yyyy') eff_date, 2 id, 'test3' name FROM dual UNION ALL

                  and the output i mentioned was
                  EFF_EFF_DATE         ID     NUM
                  1/2/2001            2     111
                  1/20/2001         2     112
                  10/31/2004       2     113
                  2/1/2004         2     114
                  10/2/2002         2     115
                  1/5/2007         2     116
                  1/2/2007         2     113    ---113 was taking from the max date in table1 <= 1/2/2007 (table 2)
                   
                  1/2/2001             2     111
                  1/20/2001         2     112
                  10/31/2004       2     113
                  2/1/2004         2     114
                  10/2/2002         2     115
                  1/5/2007         2     116
                  1/2/2009         2     116    ---116 was taking from the max date in table1 <= 1/2/2009(table2)
                  i would like to change the query that you wrote above so that rows dont get duplicated for each row in table2. so instead of the output above for
                  SELECT To_Date('1/2/2007','mm/dd/yyyy') eff_date, 2 id, 'test1' name FROM dual UNION ALL
                  SELECT To_Date('1/9/2008','mm/dd/yyyy') eff_date, 2 id, 'test3' name FROM dual UNION ALL
                  for example

                  the output should be
                  EFF_EFF_DATE         ID     NUM    Name
                  1/2/2001            2     111     test1    --this is prior to 1/2/2007 so the value of test1 is taking from 1/2/2007
                  1/20/2001         2     112             test1
                  10/31/2004       2     113    test1
                  2/1/2004         2     114            test1
                  10/2/2002         2     115           test1
                  1/5/2007         2     116           test3
                  1/2/2007         2     113          test1---113 was taking from the max date in table1 <= 1/2/2007 (table 2)
                  1/2/2009         2     116          test3 ---116 was taking from the max date in table1 <= 1/2/2009(table2)
                   
                  as you can see, instead of getting all rows from table1 for each row in table2 with same id number, now i am combining them all as the output above to avoid duplicates. same logic should apply to others ids in table table2.

                  how would change the query for this? thanks again

                  Edited by: elmasduro on Apr 29, 2010 8:32 AM

                  Edited by: elmasduro on Apr 29, 2010 8:59 AM