Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Unanswered: Querying table back and forth using analytical function

    Hi,

    Plan to achieve the following

    1) Join two tables
    2) Pick the first cost (should not be null) value before certain date closest to a particular date defined
    3) If not found, go beyond date and pick first cost value closest to a particular date defined

    Its basically just traversing the data back and forth.

    This is my test data, and query, it works as expected.


    Code:
    create table product (
       id number,
       code number,
       desc1 varchar2(1));
    
    
    create table expiry (
      code number,
      cost number,
      expiry date);
    
    insert into product values (1,6,'a');
    insert into product values (2,7,'b');
    insert into product values (3,6,'c');
    insert into product values (4,8,'d');
    insert into product values (5,2,'e');
    insert into product values (6,4,'f');
    
    insert into expiry values (6,101,to_date('2008/01/02:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
    insert into expiry values (6,100,to_date('2008/01/03:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
    insert into expiry values (6,120,to_date('2008/01/05:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
    insert into expiry values (7,130,to_date('2008/01/05:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
    insert into expiry values (8,140,to_date('2008/01/05:08:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
    insert into expiry values (8,150,to_date('2008/01/05:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
    insert into expiry values (8,160,to_date('2008/01/06:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
    insert into expiry values (9,170,to_date('2008/01/05:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
    insert into expiry values (4,0,to_date('2008/01/03:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
    insert into expiry values (4,50,to_date('2008/01/05:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
    
    
    
    commit;
    Code:
    select distinct p.id, p.code, first_value (e.cost ) over (partition by e.code order by e.expiry desc)
    from 
    product p, expiry e
    where p.code = e.code
    and e.expiry <= to_date('4-Jan-2008')
    and e.cost <> 0
    union all
    
    select distinct p.id, p.code, first_value (e.cost ) over (partition by e.code order by e.expiry asc)
    from product p, expiry e
    where p.code = e.code
    and e.expiry > to_date('4-Jan-2008')
    and p.id not in
    (
    select distinct p.id
    from 
    product p, expiry e
    where p.code = e.code
    and e.expiry <= to_date('4-Jan-2008')
    and e.cost <> 0
    )

    The problem with this query it seems to be redundant and long, i'm trying to improve the query so that it will be more efficient. Any advice will be appreciated

  2. #2
    Join Date
    Feb 2009
    Posts
    62
    Good test case - thanks for that!

    You can implement all of your logic as a CASE statement in the Order BY clause of the First_Value statement:
    Code:
    select distinct
           p.id
          ,p.code
          ,first_value (e.cost ) over (partition by e.code 
                                       order by case when e.expiry <= to_date('4-jan-2008','dd-mon-yyyy') then e.expiry
                                                     else null end desc nulls last
                                                ,case when e.expiry > to_date('4-jan-2008','dd-mon-yyyy') then e.expiry
                                                     else null end asc nulls last) val
    from 
    product p, expiry e
    where p.code = e.code
    and e.cost <> 0
    order by id;
    This simply orders all the rows where the expiry date is <= Jan 4th first, in ascending order, and follows that up with all the rows where date > Jan 4th, in descending order.

  3. #3
    Join Date
    Nov 2008
    Posts
    26
    fantastic use of case in order by clause, and i was not aware theres such keyword to order null values -> nulls last.

    Thanks!


    Quote Originally Posted by JRowbottom
    Good test case - thanks for that!

    You can implement all of your logic as a CASE statement in the Order BY clause of the First_Value statement:
    Code:
    select distinct
           p.id
          ,p.code
          ,first_value (e.cost ) over (partition by e.code 
                                       order by case when e.expiry <= to_date('4-jan-2008','dd-mon-yyyy') then e.expiry
                                                     else null end desc nulls last
                                                ,case when e.expiry > to_date('4-jan-2008','dd-mon-yyyy') then e.expiry
                                                     else null end asc nulls last) val
    from 
    product p, expiry e
    where p.code = e.code
    and e.cost <> 0
    order by id;
    This simply orders all the rows where the expiry date is <= Jan 4th first, in ascending order, and follows that up with all the rows where date > Jan 4th, in descending order.

  4. #4
    Join Date
    Nov 2008
    Posts
    26
    Just wanted to get more understanding on this part of the query

    Code:
          ,first_value (e.cost ) over (partition by e.code 
                                       order by case when e.expiry <= to_date('4-jan-2008','dd-mon-yyyy') then e.expiry
                                                     else null end desc nulls last
                                                ,case when e.expiry > to_date('4-jan-2008','dd-mon-yyyy') then e.expiry
                                                     else null end asc nulls last) val
    Im clear how the partion and first_value works, just want to clarify on order by clause with case

    Lets say we have the following data in expiry,
    Code:
    code | cost | expiry
    
    2       | 10   | 3-Jan-2008
    2       | 20   | 1-Jan-2008
    2       |        | 2-Jan-2008
    2       | 30    | 5-Jan-2008
    2       | 40    | 6-Jan-2008
    Since the partition is by code column , and we are ordering
    1) if expiry <= 4-Jan-2008, then expiry desc
    2) if expiry > 4-Jan-2008, then expiry asc

    so im just not sure how the order by clause if processed.

    Does it like partitions the data by expiry field as well ( <=4-Jan-2008 & >4-Jan-2008), then sorts it within that expirt column partition?

    Or as data come in, if expiry <= 4-Jan-2008, then sort desc, then if next data > 4-Jan-2008, resorts whole thing asc.

  5. #5
    Join Date
    Feb 2009
    Posts
    62
    All it's doing is producing two values for each row fetched, and sorting the data by the first row in descending order, and then for all the rows with the same value in the first column, it sorts by the second column in ascending order..

    If you run this query, you should see what's going on better
    Code:
    with src as (select 2 code , 10 cost , to_date('3-Jan-2008','dd-mon-yyyy') expiry from dual union all
                 select 2       , 20     , to_date('1-Jan-2008','dd-mon-yyyy') expiry from dual union all
                 select 2       , null   , to_date('2-Jan-2008','dd-mon-yyyy') expiry from dual union all
                 select 2       , 30     , to_date('5-Jan-2008','dd-mon-yyyy') expiry from dual union all
                 select 2       , 40     , to_date('6-Jan-2008','dd-mon-yyyy') expiry from dual)
    select code
          ,cost
          ,expiry             
          ,case when expiry <= to_date('4-jan-2008','dd-mon-yyyy') then expiry
                else null end order_by_col_1
          ,case when expiry > to_date('4-jan-2008','dd-mon-yyyy') then expiry
                else null end order_by_col_2
    from   src
    order by 4 desc nulls last, 5 asc nulls last;

  6. #6
    Join Date
    Nov 2008
    Posts
    26
    Allright, got it, thanks alot for the crytal clear explain plus the piece of code.

    I was trying to times the values i get out of the analytic function with a constant, but im getting error, this is what i have done.



    Code:
      
          ,first_value (e.cost ) over (partition by e.code 
                                       order by case when e.expiry <= to_date('4-jan-2008','dd-mon-yyyy') then e.expiry
                                                     else null end desc nulls last
                                                ,case when e.expiry > to_date('4-jan-2008','dd-mon-yyyy') then e.expiry
                                                     else null end asc nulls last) val, val*1.1
    from 
    product p, expiry e

    The error seems to say val - invalid identifier
    Last edited by ajitpal.s; 02-27-09 at 08:24.

  7. #7
    Join Date
    Feb 2009
    Posts
    62
    An alias that you give a column only applies to things that use that query as a data source - they don't apply inside the query itself.

    So instead of
    Code:
    SELECT col_1  val
                ,val*1.1
    FROM    table;
    You have to do either:
    Code:
    SELECT col_1 val
                ,col_1*1.1
    FROM    table;
    or
    Code:
    SELECT val
                ,val*1.1
    FROM   (SELECT col_1 val
                             ,col_1*1.1
                 FROM    table);

  8. #8
    Join Date
    Nov 2008
    Posts
    26
    great. thanks a million

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •