Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Crosstab
PL/SQL Crosstab [message #262755] Tue, 28 August 2007 04:17 Go to next message
chris_t
Messages: 4
Registered: August 2007
Junior Member
Hope someone can assist. I am trying to create a dynamic crosstab with PL/SQL (oracle 9.2.0.1.0). My non-dynamic (non-PL/SQL)one looks like so:

SELECT ts_Name,
MAX(DECODE(ds_Step_Name,'Step 1',st_Step_Name
||' - '
||st_Status,
NULL)) Step_1,
MAX(DECODE(ds_Step_Name,'Step 2',st_Step_Name
||' - '
||st_Status,
NULL)) Step_2,
MAX(DECODE(ds_Step_Name,'Step 3',st_Step_Name
||' - '
||st_Status,
NULL)) Step_3,
MAX(DECODE(ds_Step_Name,'Step 4',st_Step_Name
||' - '
||st_Status,
NULL)) Step_4
FROM Default_db.Test,
Default_db.desSteps,
Default_db.Step
WHERE ts_Test_Id = ds_Test_Id (+)
AND ds_Test_Id = st_Test_Id (+)
AND ds_Step_Id = st_Step_Id (+)
AND ts_Test_Id = 501
AND ts_Template IS NULL
GROUP BY ts_Name

This produces the following (removed headers):

test_1, Step 1 - Passed Step, 2 - Passed, Step 3 - Failed, Step 4 - No Run

I am geting somewhat lost with a dynamic version - the number of 'Steps' is unknown. I was attempting the following having viewed previous posts:


SQL>VARIABLE g_ref REFCURSOR
SQL>DECLARE
v_sql VARCHAR2(1000);
BEGIN
v_sql := 'select TS_NAME';

FOR rec IN (SELECT DISTINCT ds_Step_Name
FROM Default_db.desSteps)
LOOP
v_sql := v_sql
||',MAX(DECODE(ds_step_name,'''
||rec.ds_Step_Name
||''',rec.ds_step_name))';
END LOOP;

v_sql := v_sql
||'from (select TS_NAME, DS_STEP_NAME, ST_STATUS '
||'from DEFAULT_DB.TEST, DEFAULT_DB.DESSTEPS,DEFAULT_DB.STEP '
||'where T.TS_TEST_ID = DS.DS_TEST_ID'
||'and DS_TEST_ID = ST_TEST_ID'
||'and DS_TEST_ID = ST_TEST_ID'
||'and DS_STEP_ID = ST_STEP_ID)'
||'and TS_TEMPLATE is null'
||'group by TS_NAME,DS_STEP_ORDER';

OPEN :g_ref FOR v_sql;
END;
/



The above is returning an ORA 6502 (PL/SQL numeric or value error line 9).

Any ideas please ?

Thanks in advance.

[Updated on: Tue, 28 August 2007 04:41]

Report message to a moderator

Re: PL/SQL Crosstab [message #262756 is a reply to message #262755] Tue, 28 August 2007 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: PL/SQL Crosstab [message #262768 is a reply to message #262756] Tue, 28 August 2007 04:42 Go to previous messageGo to next message
chris_t
Messages: 4
Registered: August 2007
Junior Member
Thanks for the pointers Michel. I have reformatted and hope this is more acceptable.

Regards

Chris.

[Updated on: Tue, 28 August 2007 04:42]

Report message to a moderator

Re: PL/SQL Crosstab [message #262771 is a reply to message #262756] Tue, 28 August 2007 04:49 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Quote:
DECLARE
v_sql VARCHAR2(1000);


Set your variable length higher. It can not store the total concatenated string so this error comes.

Please declare it to it's maximum as you don't know the exact length so declare it as
DECLARE
v_sql VARCHAR2(4000);

Cheers
Soumen
Re: PL/SQL Crosstab [message #262780 is a reply to message #262771] Tue, 28 August 2007 05:01 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Soumen,
Quote:
Please declare it to it's maximum as you don't know the exact length so declare it as ... [snip]


Actually, the max size is 32767 not 4000
Re: PL/SQL Crosstab [message #262815 is a reply to message #262755] Tue, 28 August 2007 05:40 Go to previous messageGo to next message
chris_t
Messages: 4
Registered: August 2007
Junior Member
Thanks for replies however set variable to 32767 but still get the same error....
Re: PL/SQL Crosstab [message #262817 is a reply to message #262815] Tue, 28 August 2007 05:41 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Please run your code in a sql*plus session and post the entire result (code and full error message)
Ta

ps Please remember to post your version and to format and code tag your code.

[Updated on: Tue, 28 August 2007 05:42]

Report message to a moderator

Re: PL/SQL Crosstab [message #262853 is a reply to message #262817] Tue, 28 August 2007 06:46 Go to previous messageGo to next message
chris_t
Messages: 4
Registered: August 2007
Junior Member
No Message Body

[Updated on: Wed, 29 August 2007 04:33]

Report message to a moderator

Re: PL/SQL Crosstab [message #262858 is a reply to message #262853] Tue, 28 August 2007 06:52 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is likely that you exceeded your variable size.
Enclose your code between [code]<your code>[/code] tags.

Regards
Michel
Previous Topic: Problem with OR
Next Topic: Nested Table,Varray
Goto Forum:
  


Current Time: Fri Dec 09 09:27:49 CST 2016

Total time taken to generate the page: 0.22717 seconds