OraFAQ Forum: SQL & PL/SQL » How to use pivot with parameters

Home » SQL & PL/SQL » SQL & PL/SQL » How to use pivot with parameters

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to use pivot with parameters [message #582230]

Tue, 16 April 2013 04:06

cornwall
Messages: 36
Registered: June 2009

Member

How can you use pivot with a parameter?

If you attempt to use a parameter then an ORA-500: bind variable not supported is given

Below is a simplified version of the sql to illustrate teh problem.
Any help would be appreciated..

create table test_pivot
(
ord_no number,
qty number,
loc varchar2(10)
):

insert into test_pivot values (123, 3, 'LOC1');
insert into test_pivot values (123, 2, 'LOC1');
insert into test_pivot values (234, 4, 'LOC1');
insert into test_pivot values (234, 5, 'LOC1');

-- this one is fine
Select * From
(
Select Ord_No, Qty, Loc
From Test_Pivot
Where Ord_No In ('123', '234')
)
Pivot (Sum(Qty) For Ord_No In ('123', '234') )

-- how to use pivot with parameters
Select * From
(
Select Ord_No, Qty, Loc
From Test_Pivot
Where Ord_No In (:Ord1, :Ord2)
)
Pivot (Sum(Qty) For Ord_No In (:Ord1, :Ord2) )

Report message to a moderator

Re: How to use pivot with parameters [message #582231 is a reply to message #582230]

Tue, 16 April 2013 04:21

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You cannot use bind variables in this place.
You can only use constants.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

Re: How to use pivot with parameters [message #582232 is a reply to message #582231]

Tue, 16 April 2013 04:23

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Ampersand instead of colon?

SQL> Select * From
  2  (
  3  Select Ord_No, Qty, Loc
  4  From Test_Pivot
  5  Where Ord_No In (&&Ord1, &&Ord2)
  6  )
  7  Pivot (Sum(Qty) For Ord_No In (&&Ord1, &&Ord2) );
Enter value for ord1: 123
Enter value for ord2: 234

LOC               123        234
---------- ---------- ----------
LOC1                5          9

SQL>

Report message to a moderator

Re: How to use pivot with parameters [message #582298 is a reply to message #582230]

Tue, 16 April 2013 14:17

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

If you don't care about column names:

variable Ord1 number
variable Ord2 number
exec :Ord1 := 123;
exec :Ord2 := 234;
Select loc,"1","2" From 
(
Select Ord_No, Qty, Loc, dense_rank() over(order by Ord_No) rnk
From Test_Pivot
Where Ord_No In (:Ord1, :Ord2)
)
Pivot (Sum(Qty),max(Ord_No) x For rnk In (1, 2) )
/

LOC                 1          2
---------- ---------- ----------
LOC1                5          9

SQL>

SY.

Report message to a moderator

Re: How to use pivot with parameters [message #582299 is a reply to message #582232]

Tue, 16 April 2013 14:23

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Littlefoot wrote on Tue, 16 April 2013 05:23

Ampersand instead of colon?

Actually it can be both in SQL*Plus:

variable Ord1 number
variable Ord2 number
exec :Ord1 := 123;
exec :Ord2 := 234;
set verify off
column ord1 new_value ord1 noprint
column ord2 new_value ord2 noprint
select :Ord1 Ord1,:Ord2 Ord2 from dual;
Select * From 
(
Select Ord_No, Qty, Loc
From Test_Pivot
Where Ord_No In (:Ord1, :Ord2)
)
Pivot (Sum(Qty) For Ord_No In (&Ord1, &Ord2) )
/

LOC               123        234
---------- ---------- ----------
LOC1                5          9

SQL>

SY.

Report message to a moderator

Previous Topic:	Help with poor query
Next Topic:	table rows keep erasing themselves after I exit the session

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 15 20:47:57 CDT 2024