I have a table pm_project which contains following columns... project_id,project_name,project_start_date,project_end_date... I want to generaate a report where user will select the range of months, I have to display a graph with x axis as the range of months he has selected.. and project_name as Y axis. My query should help plotting this graph with the projects and its range(duration between project_start_date and project_end_date if the project has started before query end date (which user selects) and project should not have ended before query start date ) in the selected period. I need a query to dynamically select project names as alias for result calculated using start date and end date.
I've been trying this query but after the keyword as, if I write a sub query, it is throwing an error.
declare
TYPE number_table_type IS TABLE OF Varchar2(3260) ;
v number_table_type :=number_table_type(100);
lm varchar2(4400);
ln varchar2(4000);
v_l Number(20) ;
vl varchar2(30);
begin
v.extend(100);
vl:='dhl';
lm :='select (to_char(add_months((to_date(:P97_QSDATE,''dd-mm-yyyy'')),r),''MON-YYYY'')) label';
v(1):=',case when project_id=10053 and (to_char(project_start_date,''yyyymm'') <= to_char(add_months((to_date(:P97_QSDATE,''dd-mm-yyyy'')),r),''yyyymm'') and
to_char(project_end_date,''yyyymm'')>= to_char(add_months((to_date(:P97_QSDATE,''dd-mm-yyyy'')),r),''yyyymm'')) then 1
else null
end as proj';
ln :=' from pm_project,(select rownum-1 r from all_objects where rownum <= ( select
max(months_between(:P97_QEDATE,:P97_QSDATE))+1 from pm_project) )
where months_between(:P97_QEDATE,:P97_QSDATE)+1 >= r and project_id=10053' ;
return lm||v(1)||ln;
end;
Here i have tried for one project with column alias hardcoded as "proj".. 'I want alias to be the project name and also i want it for all the project as i have written query for 1 project.. how can i implement the same query for all project with project name as alias'.. Thank you for your response..
------------------------------------------------------------------------------------------------------------
Hi Tom, This is my requiremnet.. I have a table pm_project as below:
create table pm_project(project_id NUMBER not null,
project_name VARCHAR2(40),
project_start_date DATE,
project_end_date DATE);
insert into pm_project values(1001,'DHL','12-mar-2011','14-sep-2011');
insert into pm_project values(1000,'LOWES','10-oct-2010','14-apr-2011');
insert into pm_project values(1002,'XPEDEX','12-mar-2010','14-nov-2010');
insert into pm_project values(1003,'BELRON','12-oct-2011','');
insert into pm_project values(1004,'ACADEMICS','12-june-2010','');
If project_end_date is null , it means project is still in progress.. Now user will select the range of month to see which project was active in that duration(project should have started before query end date and project should not have ended before query start date .. )
lets say user select q_start_date(Query start date) as 12-jan-2011 and q_end_date(query end date) as 18-oct-2011..
I need following output
label DHL LOWES BELRON ACADEMICS
----- --- ----- ------ --------
jan-2011 2 4
feb-2011 2 4
mar-2011 1 2 4
apr-2011 1 2 4
may-2011 1 4
jun-2011 1 4
jul-2011 1 4
aug-2011 1 4
sep-2011 1 4
oct-2011 3 4
If the project is active for that month, its given a value as shown above( Different values are given for different project so we can plot it in a graph)... I tried the following plsql code which returns a sql query and am able to get data for 1 project ..
declare
TYPE number_table_type IS TABLE OF Varchar2(3260) ;
v number_table_type :=number_table_type(100);
lm varchar2(4400);
ln varchar2(4000);
begin
v.extend(100);
lm :='select (to_char(add_months((to_date(:P97_QSDATE,''dd-mm-yyyy'')),r),''MON-YYYY'')) label';
v(1):=',case when project_id=1000 and (to_char(project_start_date,''yyyymm'') <= to_char(add_months((to_date(:P97_QSDATE,''dd-mm-yyyy'')),r),''yyyymm'') and
to_char(project_end_date,''yyyymm'')>= to_char(add_months((to_date(:P97_QSDATE,''dd-mm-yyyy'')),r),''yyyymm'')) then 1
else null
end as proj';
ln :=' from pm_project,(select rownum-1 r from all_objects where rownum <= ( select
max(months_between(:P97_QEDATE,:P97_QSDATE))+1 from pm_project) )
where months_between(:P97_QEDATE,:P97_QSDATE)+1 >= r and project_id=1000' ;
return lm||v(1)||ln;
end;
and here is my output
label proj
----- ----
jan-2011
feb-2011
mar-2011 1
apr-2011 1
may-2011 1
jun-2011 1
jul-2011 1
aug-2011 1
sep-2011 1
oct-2011
But column alias in this output is proj, how can i get the column alias to be the project_name(DHL in this case) dynamically..
Also i need this query to be done for all the projects .. How can i implement it??
so, first we'll start by figuring out the template query we need:
ops$tkyte%ORA11GR2> variable sdate varchar2(20)
ops$tkyte%ORA11GR2> variable edate varchar2(20)
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec :sdate := '12-jan-2011'; :edate := '18-oct-2011';
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> with dates
2 as
3 (select add_months( trunc( to_date(:sdate,'dd-mon-yyyy'),'mm' ), level-1 ) dt
4 from dual
5 connect by level <= months_between( trunc(to_date(:edate,'dd-mon-yyyy'),'mm'), trunc( to_date(:sdate,'dd-mon-yyyy'),'mm') )+1
6 ),
7 p
8 as
9 (select *
10 from pm_project
11 where ( to_date(:sdate,'dd-mon-yyyy') <= nvl(project_end_date,to_date(:sdate,'dd-mon-yyyy')) )
12 and ( last_day(to_date(:edate,'dd-mon-yyyy')) >= project_start_date )
13 )
14 select dates.dt,
15 max( decode( project_name, 'DHL', 1 ) ) "DHL",
16 max( decode( project_name, 'LOWES', 2 ) ) "LOWES",
17 max( decode( project_name, 'BELRON', 3 ) ) "BELRON",
18 max( decode( project_name, 'ACADEMICS', 4 ) ) "ACADEMICS"
19 from dates, p
20 where dates.dt between trunc(p.project_start_date,'mm') and last_day( nvl(p.project_end_date,dates.dt) )
21 group by dates.dt
22 order by dates.dt
23 /
DT DHL LOWES BELRON ACADEMICS
--------- ---------- ---------- ---------- ----------
01-JAN-11 2 4
01-FEB-11 2 4
01-MAR-11 1 2 4
01-APR-11 1 2 4
01-MAY-11 1 4
01-JUN-11 1 4
01-JUL-11 1 4
01-AUG-11 1 4
01-SEP-11 1 4
01-OCT-11 3 4
10 rows selected.
so, that gets our result set for us - but since the number and names of the columns we retrieve will change from run to run - we KNOW we cannot do this in a single query - we'll need to use a bit of dynamic sql to build some set of queries for us.
Now that we have the template query - making this into a procedure that returns a result set for us is very very easy:
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure my_pivot( p_sdate in date, p_edate in date, p_query in out sys_refcursor )
2 as
3 l_sql long := q'|
4 with dates
5 as
6 (select add_months( trunc( :p_sdate,'mm' ), level-1 ) dt
7 from dual
8 connect by level <= months_between( trunc(:p_edate,'mm'), trunc( :p_sdate,'mm') )+1
9 ),
10 p
11 as
12 (select *
13 from pm_project
14 where ( :p_sdate <= nvl(project_end_date,:p_sdate) )
15 and ( last_day(:p_edate) >= project_start_date )
16 )
17 select dates.dt|';
18 begin
19 for x in ( select project_name, rownum r
20 from (select distinct project_name
21 from pm_project
22 where ( p_sdate <= nvl(project_end_date,p_sdate) )
23 and ( last_day(p_edate) >= project_start_date )
24 order by project_name) )
25 loop
26 l_sql := l_sql ||
27 ', max( decode( project_name, ' ||
28 dbms_assert.enquote_literal(x.project_name) ||
29 ', ' || x.r || ') )' ||
30 dbms_assert.simple_sql_name( '"'|| x.project_name || '"' );
31 end loop;
32 l_sql := l_sql || q'|
33 from dates, p
34 where dates.dt between trunc(p.project_start_date,'mm') and last_day( nvl(p.project_end_date,dates.dt) )
35 group by dates.dt
36 order by dates.dt|';
37
38 open p_query for l_sql using p_sdate, p_edate, p_sdate, p_sdate , p_sdate, p_edate;
39
40 end;
41 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> variable q refcursor
ops$tkyte%ORA11GR2> exec my_pivot( to_date(:sdate,'dd-mon-yyyy'), to_date(:edate,'dd-mon-yyyy'), :q )
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> print q
DT ACADEMICS BELRON DHL LOWES
--------- ---------- ---------- ---------- ----------
01-JAN-11 1 4
01-FEB-11 1 4
01-MAR-11 1 3 4
01-APR-11 1 3 4
01-MAY-11 1 3
01-JUN-11 1 3
01-JUL-11 1 3
01-AUG-11 1 3
01-SEP-11 1 3
01-OCT-11 1 2
10 rows selected.
And there you go - instant "dynamic pivot"