dynamic sql(urgent please help)

[Knarayan]

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

[sambavan]

Moving it to the development forum from Admin forum as it would be the appropriate place for these kind of questions..

Sam

[balajiyes]

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