decode (need a sort of dynamic stuff or so)

[Shurik12]

Hello,

Could someone help to get me started here.
Need to make a 'crosstab' transformation (the rows become columns).
One can use Decode(...) in a 'static' case but what about when the number of coulmns can't be defined in advance?
If someone by chance has example, it'll be greatly appreciated.


Thanks in advance,
Shurik12.

[DaPi]

To my mind the solution is trivial - if you want a layout like Excel, extract your data as a "flat file" and load it into Excel. (Been doing this for years . . . a few lines of macro will automate it as well.)

OK, analytical functions will do some of this stuff - but why not do it the easy way?

[Shurik12]

Hi DaPi,

Thanks for your reply.
It could have been a solution but not in the situation I'm facing.
All the data remains on Oracle, no output to Excel is desirable.
I'm more than sure that it's doable by using dynamic sql and 'decode'
function.


Regards,
Shurik12.

[DaPi]

I'd use CURSOR with ORDER BY in PL/SQL, loop over the rows and build up the output string by concatination (with break-point processing just as we did in the 70's).

If you really want the ouput in an Oracle table with a variable number of columns . . . .

[Shurik12]

Here is what I came up with. But still receiving an error:

"ORA-00923: FROM keyword not found where expected, etc...


The code i the body is as follows

1: create or replace package body TestChart
2: as
3: procedure TestChart
4: (
5: ChartOutput IN OUT ReturnCursor
6: )
7:
8: is
9: l_query long:= 'SELECT DISTINCT storedate ';
10:
11: begin
12: for x in (SELECT distinct source FROM tblsources)
13: loop
14: l_query=l_query ||
15: 'max(decode(source, '|| x.source ||', midpx, null))" ' ||x.source || '", ';
16: end loop
17: l_query:= l_query || ' FROM testchart1
18: GROUP BY storedate';
19:
20:OPEN ChartOutput FOR l_query;
21:
22:END;
23:END;
24:/



(As the basis for this piece of code i took the stuff from the following link :http://asktom.oracle.com/pls/ask/f?p...4021879232164,)


It'd be really nice if someone could point out what I'm doing wrong?

By the way why in the mentioned above link (and thus in mine too) 'l_query' is declared as 'long'? Will not varchar2(...) be more logical?


Thanks in advance for your replies.


Regards,

Shurik12.

[celebguy_dv]

[Shurik12]

Hi again,

If it might be interesting for someone, the answer to my first question proved to be the following:

this part of code

...
l_query:= l_query||
','||'max (DECODE (source,’’’|| x.source ||’’’, MIDPX ,null)) "'|| x. source ||' " ’;
...


should have been ammended (',' added in the very beginning:
So it worked fine.

Here comes my second question.I tried to 'complicate' the matter:

1: CREATE OR REPLACE PACKAGE BODY ChartNameHistoryBySource
2: AS
3: PROCEDURE ChartNameHistoryBySource ,
4: (
5: p_StoreDateStart IN DATE
6: p_StoreDateEnd IN DATE
7: p_CompanyName IN VarChar2
8: p_Maturity IN Integer
9: ChartOutput IN OUT ReturnCursor
10: )
11:
12: IS
13: l_query long := 'SELECT storedate ';
14:
15: BEGIN
16: FOR x IN (SELECT DISTINCT source FROM tbltestsources)
17:
18:
19:
33:
34:
35:
36: LOOP
37: l_query:= l_query||
38: ','||'max (DECODE (source,’’’|| x.source ||’’’, MIDPX ,null)) "'|| x. source ||' " ’;
39: END LOOP;
40:
41:
42:
43:
44: l_query :=rtrim(l_query, ',') ||
45: ' FROM tblcdsbysource cds
46: , Itblcds cd
47: , tblnames n
48: , tblsources srs
49:
I50: WHERE
51: cds.PROTECTIONID=cd.PROTECTIONID
52: AND cds.SOURCEID=srs.SOURCEID
53: AND cd.NAMEID=n.NAMEID
54: AND STOREDATE >= : p_StoreDateStart AND STOREDATE <= : p_StoreDateEnd
55: AND CONAME= : p_CompanyName
56: AND MATURITY= : p_Maturity
57: GROUP BY storedate';
58: OPEN ChartOutput FOR l_query;
59:
60: END;
61: END;
62: /


and now I receive an error saying:
ORA-00600 internal error code, arguments [15212],[4],[],[],[],[],[]....


Guys, do I miss something major here?
Again would appreciate your input.

Regards,
Shurik12.

[DaPi]

ORA-00600 is a Oracle internal error (as it says) - so usually not your fault. Try raising a TAR on MetaLink - unless someone on this forum has seen it before . . .

[Shurik12]

Hi DaPi,

Thanks for your feedback.
Just sorted it out.
One has to use a late binding here.
This one I've encountered myself once. The problem is your way of using bind variables. Rather try the following (late binding):

......
53: AND cd.NAMEID=n.NAMEID
54: AND STOREDATE >= :1 AND STOREDATE <= :2
55: AND CONAME= :3
56: AND MATURITY= :4
57: GROUP BY storedate';
58: OPEN ChartOutput FOR l_query USING p_StoreDateStart, p_StoreDateEnd, p_CompanyName, p_Maturity;

Cheers,
Alex.