complex query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: complex query

  1. #1
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250

    Unhappy

    I have a table x which has the foll. columns
    ctlno
    asof_date
    year
    jan,feb,mar,apr......dec,total

    Now I want to write a query in which I am able the pick up the
    the column depending upon the max date in asof_date column.
    For eg:
    select ctlno,sum(mar)
    from x where to_char(asof_date,'mon')='MAR'
    and asof_date=(select max(asof_date) from x)
    group by ctlno

    I want to be able to change the month according to the month in the max(asof_date). I am able to do this by using decode:
    select ctlno,sum(decode(to_char(asof_date,'mm'),01,jan,0,
    02,feb,0,03,mar,0........))
    from x where to_char(asof_date,'mon')='MAR'
    and asof_date=(select max(asof_date) from x)
    group by ctlno

    Just to know if there is a better way of doing this dynamically.

    Thanx in advance
    KN

  2. #2
    Join Date
    Jan 2001
    Posts
    153
    the other best way around i can immd. think of is using dynamic SQL !! but that should be accomplished by the pl/sql block..

    the code what i ve given is an glimpse..not an complete one..


    cursor s1 is select column_name from cols
    where
    column_name = (select max(asof_date) from x);

    open s1;
    fetch s1 into colname;
    close s1;

    vsqlstr := 'select ctlno,sum('||colname||') from x where
    asof_date=(select max(asof_date) from x)
    group by ctlno ';
    execute immediate vsqlstr into localvar1,localvar2;

    Vijay



    Vijay.s

  3. #3
    Join Date
    Nov 2000
    Posts
    38

    Thumbs up

    Hi,

    Decode is the best option if u r going for SQL. If u r going for PL/SQL and using Oracle 8/8i check for
    OPEN FOR..... to accomplish the task.

    Hope this would help u.

    Rgds,

    BS

Posting Permissions

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


Click Here to Expand Forum to Full Width