dynamic sql(urgent please help)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: dynamic sql(urgent please help)

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

    Angry

    I am trying to write a dynamic sql where the column name is a month and has to be calculated, for eg the column names are like jan,feb,mar etc and depending upon another date in the table I have the choose that particular month for results.

    if to_char(asof_dt,'mon')='aug' then
    I have to display aug,aug-1mon,aug-2mon,aug-3mon....aug-12mon) as result.
    I try to achieve the result from the following sql but don't know how to specify quotes for to_char(var,'mon') inside the dynamic sql:

    open cv for 'SELECT sum('||caldate||'/2) lt1m,
    sum('||TO_CHAR(TO_DATE(TO_CHAR(TO_DATE(caldate,'mon'),'mm')-1,'mm'),'mon')||')
    FROM
    BAR_WRK_AR WHERE asof_dt=(SELECT MAX(asof_dt) FROM BAR_WRK_AR)
    AND year=asyear';

    The first column works perfectly, I have a problem when trying to derive the 2 column by to_char statement.
    I am trying to look for this subject in oramag but the site seems to be down, any idea why??

    Thanks a ton in advance
    Kavita
    KN

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Moving it to the development forum from Admin forum as it would be the appropriate place for these kind of questions..

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi do this

    declare
    a varchar2(200);
    ip date := '23-SEP-75';
    op varchar2(100);
    begin
    a := 'select TO_CHAR(TO_DATE('||CHR(39)||ip||CHR(39)||',''DD-MON-YY'')) FROM dual';
    execute immediate a INTO op;
    dbms_output.put_line(op);
    end;


    the CHR(39) is nothin but single quots

    writeback @
    jegannathan@sakinfotech.co.in

    Cheers!
    OraKid.

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