-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|