Home » Developer & Programmer » Forms » how to update the columns dynamically in sql
| how to update the columns dynamically in sql [message #122249] |
Sat, 04 June 2005 05:42  |
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
|
Member |
|
|
hai,
I working in oracle reports..
I have a table emp with fields
1)ent_id
2)trd_date
3)brokerage
I want to find out the Brokerage paid by the client for each month
So my query will be
select ent_id,sum(brokerage)brk,to_char('mon',trd_dt)mon from emp
where trd_dt between '1-jan-2005' and '15-may-2005'
and ent_id='1182'
group by to_char('mon',trd_dt);
So the result will be
brk mon ent-id
100 jan 1182
200 feb 1182
300 Mar 1182
400 Apr 1182
500 May 1182
But I want the result in this format like
ent_id Jan Feb Mar Apr may
1182 100 200 300 400 500
Please help me to solve this problem
urgent
|
|
|
|
|
|
| Re: how to update the columns dynamically in sql [message #122405 is a reply to message #122271] |
Mon, 06 June 2005 07:13  |
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
|
Member |
|
|
thanks a lot it is working now
The query is
SELECT *
FROM (SELECT (SUBSTR(c.dcn_ent_ctrl_id,1,4)||'00000000')branch,
sum(decode(to_char(c.dcn_date,'mon'),'jan',c.dcn_brokerage_amt,0)) JANUARY,
sum(decode(to_char(c.dcn_date,'mon'),'feb',c.dcn_brokerage_amt,0)) FEBRUARY,
sum(decode(to_char(c.dcn_date,'mon'),'mar',c.dcn_brokerage_amt,0)) MARCH,
sum(decode(to_char(c.dcn_date,'mon'),'apr',c.dcn_brokerage_amt,0)) APRIL,
sum(decode(to_char(c.dcn_date,'mon'),'may',c.dcn_brokerage_amt,0)) MAY,
sum(decode(to_char(c.dcn_date,'mon'),'jun',c.dcn_brokerage_amt,0)) JUNE,
sum(decode(to_char(c.dcn_date,'mon'),'jul',c.dcn_brokerage_amt,0)) JULY,
sum(decode(to_char(c.dcn_date,'mon'),'aug',c.dcn_brokerage_amt,0)) AUGUST,
sum(decode(to_char(c.dcn_date,'mon'),'sep',c.dcn_brokerage_amt,0)) SEPTEMBER,
sum(decode(to_char(c.dcn_date,'mon'),'oct',c.dcn_brokerage_amt,0)) OCTOBER,
sum(decode(to_char(c.dcn_date,'mon'),'nov',c.dcn_brokerage_amt,0)) NOVEMBER,
sum(decode(to_char(c.dcn_date,'mon'),'dec',c.dcn_brokerage_amt,0)) DECEMBER
FROM dtm_contract_note_master c, dtm_contract_note_details d
WHERE c.dcn_date BETWEEN :FROMDATE AND :TODATE
AND (SUBSTR(c.dcn_ent_ctrl_id,1,4)||'00000000') = :BRANCHID
and c.dcn_no=d.dcd_dcn_no
and SUBSTR(d.dcd_dcm_id,1,3)=NVL(:TTYPE,SUBSTR(d.dcd_dcm_id,1,3))
GROUP BY (SUBSTR(c.dcn_ent_ctrl_id,1,4)||'00000000') )
ORDER BY 1;
Here I am using 4 paramaeters fromdate,todate, branchid & ttype
Now I have a small problem ..
If I give from date 15-jan-2005 and to date 25-may-2005 then
it should show me only the months pertaining to fromdate and todate that is it should me jan, feb , mar ,apr , may only
It should not show all the 12 months
Please help me to solve the problem
|
|
|
|
|
|
| Re: HOW TO SELECT the columns DEPENDING UPON THE PARAMETRS IN THE SQL STATEMENT [message #122521 is a reply to message #122271] |
Tue, 07 June 2005 00:12 |
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
|
Member |
|
|
Now I have a small problem ..
Here I am using 4 paramaeters fromdate,todate, branchid & ttype
If I give from date 15-jan-2005 and to date 25-may-2005 then
it should show me only the months pertaining to fromdate and todate that is it should me jan, feb , mar ,apr , may only
It should not show all the 12 months
Please help me to solve the problem
The query is
SELECT *
FROM (SELECT (SUBSTR(c.dcn_ent_ctrl_id,1,4)||'00000000')branch,
sum(decode(to_char(c.dcn_date,'mon'),'jan',c.dcn_brokerage_amt,0)) JANUARY,
sum(decode(to_char(c.dcn_date,'mon'),'feb',c.dcn_brokerage_amt,0)) FEBRUARY,
sum(decode(to_char(c.dcn_date,'mon'),'mar',c.dcn_brokerage_amt,0)) MARCH,
sum(decode(to_char(c.dcn_date,'mon'),'apr',c.dcn_brokerage_amt,0)) APRIL,
sum(decode(to_char(c.dcn_date,'mon'),'may',c.dcn_brokerage_amt,0)) MAY,
sum(decode(to_char(c.dcn_date,'mon'),'jun',c.dcn_brokerage_amt,0)) JUNE,
sum(decode(to_char(c.dcn_date,'mon'),'jul',c.dcn_brokerage_amt,0)) JULY,
sum(decode(to_char(c.dcn_date,'mon'),'aug',c.dcn_brokerage_amt,0)) AUGUST,
sum(decode(to_char(c.dcn_date,'mon'),'sep',c.dcn_brokerage_amt,0)) SEPTEMBER,
sum(decode(to_char(c.dcn_date,'mon'),'oct',c.dcn_brokerage_amt,0)) OCTOBER,
sum(decode(to_char(c.dcn_date,'mon'),'nov',c.dcn_brokerage_amt,0)) NOVEMBER,
sum(decode(to_char(c.dcn_date,'mon'),'dec',c.dcn_brokerage_amt,0)) DECEMBER
FROM dtm_contract_note_master c, dtm_contract_note_details d
WHERE c.dcn_date BETWEEN :FROMDATE AND :TODATE
AND (SUBSTR(c.dcn_ent_ctrl_id,1,4)||'00000000') = :BRANCHID
and c.dcn_no=d.dcd_dcn_no
and SUBSTR(d.dcd_dcm_id,1,3)=NVL(:TTYPE,SUBSTR(d.dcd_dcm_id,1,3))
GROUP BY (SUBSTR(c.dcn_ent_ctrl_id,1,4)||'00000000') )
ORDER BY 1;
please help me to solve the problem
URGENT
|
|
|
|
|
|
|
|
| Re: HOW TO SELECT the columns DEPENDING UPON THE PARAMETRS IN THE SQL STATEMENT [message #122693 is a reply to message #122690] |
Wed, 08 June 2005 01:03 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
At this point I think you should tell the user that the report has been written to handle a full year. If the user wants a Jan-May report, then write them a Jan-May report wherein you have only 5 columns of output. If they want a financial year report write it as a July to June report.
Alternatively, use dynamic SQL to build the query based on the start month and end month. Do a search in this forum for 'dbms_sql'.
This dynamic SQL is really the only way to handle a truely dynamic range of dates, like nov-04 to may-05 here the first month displayed is November and the last is May and there may be a year change.
You will have to build a loop that builds onto the base component of your query with a "sum(decode(to_char(c.dcn_date,'mon'),'jan',c.dcn_brokerage_amt,0)) JANUARY" look-a-like string. You may even have to decode both year and month to get the cross year boundary handling to work.
Please just try this dynamic stuff. Start simply and work it up from there.
David
[Updated on: Wed, 08 June 2005 01:04] Report message to a moderator |
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 01 09:49:01 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
