Re: Rows into Columns, Datagrid preparation without Dynamic SQL
From: William Robertson <williamr2019_at_googlemail.com>
Date: Thu, 19 Mar 2009 00:41:58 -0700 (PDT)
Message-ID: <871121ab-6b19-43a3-a04b-708fc5680a2d_at_o11g2000yql.googlegroups.com>
On Mar 18, 8:27 am, sumit <sumithcn..._at_gmail.com> wrote:
> Hi All,
>
> Can someone help me with this issue?
>
> We have three tables with the following structure.
>
> SELECT * FROM market;
>
> comp_no comp_name
> 100 ABC
> 101 DEF
> 102 AcME
> 103 XYZ
> . .
> . .
> . .
> . .
> . .
>
> SELECT * FROM products;
>
> pdt_no pdt_name
> 200 Paper
> 201 Staples
> 202 Pencil
> 203 Eraser
> . .
> . .
> . .
> . .
> . .
> . .
>
> SELECT * FROM sale_data;
>
> comp_no pdt_no sell_amt
> 100 200 10000
> 100 201 20000
> 100 202 30000
> 101 203 15000
> 103 200 99999
> 103 202 50000
>
> I need the sale_data as a grid, with the product making the columns
> and markets making the rows. :)
>
> comp_no 200 201 202 203 . . . .
> 100 10000 20000 30000 <NULL>
> 101 <NULL> <NULL> <NULL> 15000
> 102 <NULL> <NULL> <NULL> <NULL>
> 103 99999 <NULL> 50000 <NULL>
> . . . . .
> . . . . .
> . . . . .
> . . . . .
> . . . . .
>
> I know that we can do this using dynamic sql, but we have a number of
> prducts in the product and it causes buffer overflow error with the
> query. Highly appreciated if someone can provide any workaround or
> approach to attack this problem..
>
> Thanks
>
> Sumith
Date: Thu, 19 Mar 2009 00:41:58 -0700 (PDT)
Message-ID: <871121ab-6b19-43a3-a04b-708fc5680a2d_at_o11g2000yql.googlegroups.com>
On Mar 18, 8:27 am, sumit <sumithcn..._at_gmail.com> wrote:
> Hi All,
>
> Can someone help me with this issue?
>
> We have three tables with the following structure.
>
> SELECT * FROM market;
>
> comp_no comp_name
> 100 ABC
> 101 DEF
> 102 AcME
> 103 XYZ
> . .
> . .
> . .
> . .
> . .
>
> SELECT * FROM products;
>
> pdt_no pdt_name
> 200 Paper
> 201 Staples
> 202 Pencil
> 203 Eraser
> . .
> . .
> . .
> . .
> . .
> . .
>
> SELECT * FROM sale_data;
>
> comp_no pdt_no sell_amt
> 100 200 10000
> 100 201 20000
> 100 202 30000
> 101 203 15000
> 103 200 99999
> 103 202 50000
>
> I need the sale_data as a grid, with the product making the columns
> and markets making the rows. :)
>
> comp_no 200 201 202 203 . . . .
> 100 10000 20000 30000 <NULL>
> 101 <NULL> <NULL> <NULL> 15000
> 102 <NULL> <NULL> <NULL> <NULL>
> 103 99999 <NULL> 50000 <NULL>
> . . . . .
> . . . . .
> . . . . .
> . . . . .
> . . . . .
>
> I know that we can do this using dynamic sql, but we have a number of
> prducts in the product and it causes buffer overflow error with the
> query. Highly appreciated if someone can provide any workaround or
> approach to attack this problem..
>
> Thanks
>
> Sumith
If the columns are not known until you've read the rows,a dynamic solution is going to be necessary.
What buffer overflow error do you get? Are you using DBMS_OUTPUT for reporting? A dynamic ref cursor would be a better approach. Received on Thu Mar 19 2009 - 02:41:58 CDT