Home » SQL & PL/SQL » SQL & PL/SQL » Pivot query: grouping pivot field, using query for IN clause (Oracle 11.2.0.1.0)
| Pivot query: grouping pivot field, using query for IN clause [message #600030] |
Thu, 31 October 2013 00:45  |
 |
OraFerro
Messages: 433
Registered: July 2011
|
Senior Member |
|
|
Hi All,
I have several questions regarding pivot query and need your usual support.
below is my example:
CREATE TABLE test_sec(
sector_id number PRIMARY KEY,
sector_name varchar2(30))
INSERT ALL
INTO test_sec VALUES (1, 'Sec1')
INTO test_sec VALUES (2, 'Sec2')
INTO test_sec VALUES (3, 'Sec3')
INTO test_sec VALUES (4, 'Sec4')
SELECT * FROM DUAL;
CREATE TABLE test_sec_loan (
SECTOR_ID NUMBER(5),
YEAR VARCHAR2(15) NOT NULL,
netloan NUMBER(9) );
INSERT ALL
INTO test_sec_loan VALUES (1, '1997', 52)
INTO test_sec_loan VALUES (1, '1998', 92)
INTO test_sec_loan VALUES (1, '1999', 56)
INTO test_sec_loan VALUES (1, '2010', 20)
INTO test_sec_loan VALUES (1, '2012', 17)
INTO test_sec_loan VALUES (2, '1997', 22)
INTO test_sec_loan VALUES (2, '1998', 93)
INTO test_sec_loan VALUES (2, '1999', 46)
INTO test_sec_loan VALUES (2, '2010', 2)
INTO test_sec_loan VALUES (2, '2012', 19)
INTO test_sec_loan VALUES (3, '1997', 50)
INTO test_sec_loan VALUES (3, '1998', 98)
INTO test_sec_loan VALUES (3, '1999', 58)
INTO test_sec_loan VALUES (3, '2010', 9)
INTO test_sec_loan VALUES (3, '2012', 72)
INTO test_sec_loan VALUES (3, '2013', 57)
INTO test_sec_loan VALUES (4, '1997', 52)
INTO test_sec_loan VALUES (4, '1998', 92)
INTO test_sec_loan VALUES (4, '1999', 56)
INTO test_sec_loan VALUES (4, '2013', 54)
SELECT * FROM DUAL;
SELECT * FROM (
SELECT SECTOR_ID, YEAR, netloan
FROM test_sec_loan
)
PIVOT
(
sum(netloan)
FOR SECTOR_ID IN (1,2,3,4)-->(SELECT sector_id from test_sec)
)
ORDER BY YEAR
;
CREATE OR REPLACE PROCEDURE P_GET_test_LOANS
(
I_SECTOR_ID IN NUMBER,
S_YEAR IN VARCHAR2,
CUR_REF OUT SYS_REFCURSOR)
AS
BEGIN
OPEN CUR_REF
FOR
SELECT * FROM (
SELECT SECTOR_ID, YEAR, netloan
FROM test_sec_loan
WHERE
YEAR =
CASE WHEN YEAR IS null THEN YEAR
ELSE S_YEAR
END
AND
sector_id =
CASE WHEN I_SECTOR_ID <=1 THEN sector_id
ELSE I_SECTOR_ID
END
)
PIVOT
(
sum(netloan)
FOR SECTOR_ID IN (1,2,3,4) -->I_SECTOR_ID
)
ORDER BY YEAR
;
END;
I am using this query to build a stored procedure and be able to retrieve the data for a specific year or specific sector.
My questions are:
1- I need to make the sector selection dynamic (i.e. FOR SECTOR_ID IN (SELECT sector_id from test_sec) instead of (1,2,3,4).
2- I need to group years together so instead of showing each year in a row, I need years from 1997 to 2000 together, 2001 to 2010 together and the rest together.
3- In the SP I need to use the SP parameter I_SECTOR instead of (1,2,3,4) to make it dynamic and to get only the needed sector column
4- In the SP I need to know how to use S_YEAR as a parameter in case I learned how to group several years in one row (in this case the input will still be one year (i.e. '1999') but the data will be grouped with range of years (i.e. 2001-2010))
5- Finally I am looking forward to listening to other comments or suggestions as this might not be the right way to tackle the problem (maybe pivot is not the best choice or I need to add a middle layer between my table and SP)
Many thanks,
[Updated on: Thu, 31 October 2013 01:16] Report message to a moderator |
|
|
|
| Re: Pivot query: grouping pivot field, using query for IN clause [message #600046 is a reply to message #600030] |
Thu, 31 October 2013 02:45  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Hi,
1. Dynamic pivot IN clause (projection to columns) = dynamic SQL (at least in 11g).
However, it seems to be in contradiction with point 3 - filter to one section only.
2. Do not select YEAR directly, but construct an expression, which represents all members of the "group" with the same value, e.g. (suppose the first four characters are numbers and determine the year):
case when to_number( substr( YEAR, 1, 4 ) ) between 1997 and 2000 then '1997-2000'
when to_number( substr( YEAR, 1, 4 ) ) between 2001 and 2010 then '2001-2010'
else 'REST'
end
3. It is just a filter condition (WHERE clause), no need to PIVOT then to get a single aggregate value.
Which columns shall the cursor contain exactly? If they shall not be static, what is the rule for their construction (their count and names)?
4. Depends on content of S_YEAR parameter and all values in YEAR column (as its data type is VARCHAR2, there may be anything).
If the requirement is to group by decades (e.g. the next "range of years" would be 2021-2030) and YEAR would have NUMBER data type, it would be as easy as
trunc( YEAR_NUMERIC-1, -1 )
5. Why are you checking YEAR on NULL, as the column has NOT NULL constraint? Even the second condition could be written mor transparently (at least for me) as
( I_SECTOR_ID <=1 or sector_id = I_SECTOR_ID )
I already commented the use of PIVOT in point 3.
|
|
|
|
| Re: Pivot query: grouping pivot field, using query for IN clause [message #600050 is a reply to message #600046] |
Thu, 31 October 2013 03:44 |
|
OraFerro
Messages: 433
Registered: July 2011
|
Senior Member |
|
|
Hi Flyboy and thanks for the elaborated response.
1- I thought the same but I get (ORA-00936: missing expression) when I try
SELECT * FROM (
SELECT SECTOR_ID, YEAR, netloan
FROM test_sec_loan
)
PIVOT
(
sum(netloan)
FOR SECTOR_ID IN (SELECT sector_id from test_sec)--(1,2,3,4)-->(SELECT sector_id from test_sec)
)
ORDER BY YEAR
;
Point 3 is about the SP where I need to use the the dynamic statement
FOR SECTOR_ID IN
(
-- in case I_SECTOR_ID >=1 then SECTOR_ID
-- else (SELECT sector_id from test_sec) to get the full list of sectors
)
Hope this clarifies the unclarity in my original statement.
2- Thanks a lot
3- As described in point 1, I need to get all sectors in case I_SECTOR_ID is <=1 else I need to get only the sector column that corresponds to I_SECTOR_ID
4- the input parameter S_YEAR presents a single year (i.e. '1999', '2004'...etc.) so what should be the way to write my where condition?
where (SUBSTR(YEAR, 1, 4) >= S_YEAR ) or (SUBSTR(YEAR, 6, 4) <= S_YEAR )
5- Thanks a lot point taken
Many thanks,
|
|
|
|
|
|
| Re: Pivot query: grouping pivot field, using query for IN clause [message #600062 is a reply to message #600050] |
Thu, 31 October 2013 07:06 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
1. That is still static query, not the dynamic one.
Usage of dynamic queries is quite clearly described in PL/SQL User's Guide and Reference, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
If you insist on using it, follow the relevant chapters in that book. I will not make any example as it is often abused for no reason (and it is not clear to me whether you really need it).
XML is an option, but its output is one XMLTYPE column. Anyway, you would be able to at least parse it afterwards.
3. Not entirely. Which columns shall the cursor contain when I_SECTOR_ID >=1 and in the opposite case? Exact names of colums, please.
If their number/names differs, are you able to process them later?
4. Just curious, how do you derive from S_YEAR='1999' the range of years "2001-2010"?
Also, it depends on format of DATE column as well. Are the first four characters always numeric representation of the year (as I depended on)?
If so and both YEAR and S_YEAR shall be in the same decade, this should filter the YEARs from the decade represented by S_DATE:
to_number( substr( YEAR, 1, 4 ) ) >= trunc( to_number(S_YEAR)-1, -1 )+1
and to_number( substr( YEAR, 1, 4 ) ) <= trunc( to_number(S_YEAR)-1, -1 )+10
Just note the extra conversions because of VARCHAR2 data type.
|
|
|
|
Goto Forum:
Current Time: Sat May 11 00:58:50 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
