decode (need a sort of dynamic stuff or so)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: decode (need a sort of dynamic stuff or so)

  1. #1
    Join Date
    Feb 2003
    Posts
    10

    decode (need a sort of dynamic stuff or so)

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Feb 2003
    Posts
    10
    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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 . . . .
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  5. #5
    Join Date
    Feb 2003
    Posts
    10
    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.

  6. #6
    celebguy_dv Guest

  7. #7
    Join Date
    Feb 2003
    Posts
    10
    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.

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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 . . .
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  9. #9
    Join Date
    Feb 2003
    Posts
    10
    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.
    Last edited by Shurik12; 03-05-2003 at 05:17 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width