| Subquery in a NVL Function in a Package procedure [message #32604] |
Fri, 27 August 2004 13:11  |
Ivanips
Messages: 1
Registered: August 2004
|
Junior Member |
|
|
I have nested subqueries inside an NVL function which works fine as a query in itself, but will not compile in a package with the <NOBR><A class=iAs oncontextmenu="return false;" onmousemove=kwM(383630); onmouseover=kwE(event,383630); style="COLOR: darkgreen; BORDER-BOTTOM: darkgreen 1px solid; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout=kwL(event); href="http://www.experts-exchange.com/Databases/Oracle/Q_20940213.html#" target=_blank>compiler[/url]</NOBR> telling me that a SELECT statement is not expected.
I can't find anything anywhere that suggests there are any restrictions stopping me from doing this inside packages and would like to know if there are restrictions for nested queries in procedures/packages and if so, what are they?
Here is a trimmed down version of the procedure's SELECT statement:
OPEN p_cursor FOR
SELECT
prd.prd_id AS "ProductID",
NVL((SELECT sp.slp_price --> problem SELECT is on this line
FROM prices sp, pricelist pl
WHERE pl.plt_id = sp.plt_id
AND sp.prd_id = prd.prd_id
AND pl.plt_id = v_PricelistId
AND sp.spl_level = (SELECT MAX(spl_level)
FROM prices sp2
WHERE sp2.prd_id = sp.prd_id)),
(SELECT sp.slp_price
FROM prices sp, pricelist pl
WHERE pl.plt_id = sp.plt_id
AND sp.prd_id = prd.prd_id
AND pl.defaultlist = 1
AND sp.spl_level = (SELECT MAX(spl_level)
FROM prices sp2
WHERE sp2.prd_id = sp.prd_id))
) AS "PRICE",
FROM products prd
WHERE ........
Thanks very much for any help on this one
|
|
|
|
| Re: Subquery in a NVL Function in a Package procedure [message #32605 is a reply to message #32604] |
Fri, 27 August 2004 13:27  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
You must on Oracle 8i.
In that version and before, there were constructs that you could use in SQL, but not in PL/SQL. These constructs include - Analytic functions
- ORDER BY in a subquery
- SELECT within a SELECT clause
- CURSOR() functions
- CASE/WHEN logic
- GROUP BY ROLLUP/CUBE
- BULK COLLECT from a dynamically opened REF CURSOR
See here, here. Your workarounds are to upgrade to 9i (8i is being desupported at the end of 2004), use dynamic SQL, wrap your SQL in a view and refer to this view in your PL/SQL, or refactor your SQL to not use one of the above constructs.
I haven't looked at your SQL closely, but it looks like you should be able to replace your SELECTs within your SELECT clause with an in-line view to which you outer-join. If you post some sample data and the output you expect from your SQL, someone here should be able to help you refactor this SQL to get what you need.
|
|
|
|
| Re: Subquery in a NVL Function in a Package procedure [message #32606 is a reply to message #32604] |
Fri, 27 August 2004 13:29 |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
You must on Oracle 8i.
In that version and before, there were constructs that you could use in SQL, but not in PL/SQL. These constructs include - Analytic functions
- ORDER BY in a subquery
- SELECT within a SELECT clause
- CURSOR() functions
- CASE/WHEN logic
- GROUP BY ROLLUP/CUBE
- BULK COLLECT from a dynamically opened REF CURSOR
See here, here. Your workarounds are to upgrade to 9i (8i is being desupported at the end of 2004), use dynamic SQL, wrap your SQL in a view and refer to this view in your PL/SQL, or refactor your SQL to not use one of the above constructs.
I haven't looked at your SQL closely, but it looks like you should be able to replace your SELECTs within your SELECT clause with an in-line view to which you outer-join. If you post some sample data and the output you expect from your SQL, someone here should be able to help you refactor this SQL to get what you need.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
