Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic crosstab script
Dynamic crosstab script [message #408282] Mon, 15 June 2009 10:20 Go to next message
mando2009
Messages: 1
Registered: June 2009
Location: UK
Junior Member
Hi, Can anyone tell me why this procedure won't run? I'm trying to call it from a .net object datasource but getting nothing. The sql works but I think it's to do with the quotes.

create or replace
procedure PAGE_MARKING(p_reg_key IN NUMBER, p_recordset OUT SYS_REFCURSOR) is

cursor c_rows is
SELECT decode(wm.planned_start_date, null, sysdate, wm.planned_start_date) planned_start_date
FROM

(SELECT DISTINCT dev.fnc_week_num(planned_start_date,'09/10') num,
object_id,
planned_start_date,
usage_code
FROM table_1
WHERE register_event_id = 141
ORDER BY object_id,
num)
wm,

(SELECT week_num,
object_id,
register_event_id
FROM dev.ebs4_reg_dummy_weeks,
table_2
WHERE register_event_id = 141
ORDER BY week_num,
object_id)
aw

WHERE aw.week_num = wm.num(+)
AND aw.object_id = wm.object_id(+)
GROUP BY aw.object_id,
planned_start_date,
week_num
ORDER BY aw.object_id,week_num, planned_start_date;

l_query long := 'select null';
i number := 0;
v_reg_key number := p_reg_key;
begin

for i in c_rows loop
l_query := l_query || ', MAX(DECODE(decode(wm.planned_start_date, null, ''week_''||week_num, wm.planned_start_date),'''|| i.PLANNED_START_DATE ||''', USAGE_CODE)) "'|| i.PLANNED_START_DATE||'"';
end loop;
l_query := l_query || ' FROM (SELECT DISTINCT dev.fnc_week_num(planned_start_date,''09/10'') num, object_id, planned_start_date, usage_code FROM table_1 WHERE register_event_id = 141 ORDER BY object_id, num) wm, (SELECT week_num, object_id, register_event_id FROM dev.ebs4_reg_dummy_weeks, table_2 WHERE register_event_id = 141 ORDER BY week_num, object_id) aw WHERE aw.week_num = wm.num(+) AND aw.object_id = wm.object_id(+) GROUP BY aw.object_id, wm.planned_start_date, week_num ORDER BY aw.object_id, week_num, planned_start_date';
open p_recordset for l_query;
end;


Any help much appreciated

Thanks
Re: Dynamic crosstab script [message #408288 is a reply to message #408282] Mon, 15 June 2009 11:00 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

>Can anyone tell me why this procedure won't run
>I'm trying to call it from a .net object datasource but getting nothing.

My car won't run.
Tell me how to make my car go.

We only see what you post & what you post is unformatted code.
We have no idea what else is happening (or not).
Re: Dynamic crosstab script [message #408291 is a reply to message #408282] Mon, 15 June 2009 11:10 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
your l_query quotes look all messed up. A neat trick is to use double quotes everywhere you want a single quote, then just replace them the two single quotes before executing the dynamic sql. Makes it much easier to keep track of them.

-- final step
l_query := replace(l_query, '"', '''');
Previous Topic: Data Grouping (merged 3)
Next Topic: jobs question
Goto Forum:
  


Current Time: Mon Dec 05 06:42:08 CST 2016

Total time taken to generate the page: 0.04254 seconds