Stored Procedure problem [message #36076] |
Fri, 02 November 2001 11:47 |
ash
Messages: 43 Registered: February 2001
|
Member |
|
|
Can anybody tell me what am I doing wrong in below ?
insert into tmpResults
(select distinct c.Id clientid,
c.lastname,
c.firstname,
(SELECT NVL(vpa2.CodeDesc, 'UnSpecified') FROM
vCodesAgency vpa2 WHERE vpa2.Code = vpa.Code
AND AgencyName NOT IN ('HUD', 'SECTION 8') AND ROWNUM = 1 AND NVL(vpa2.CodeDesc, '%') LIKE p_propagency) Agency
from client c, vCodesPropertyAgency vpa
where ..............
)
This query when I run from sql plus it works
but gives me following error when I run in the stored procedure on the second subquery
error : Encountered the symbol "SELECT" when expected one of the following:
If I convert into dynamic sql then how do I execute it to insert records into table ?
Or Is there any other way to do it ?
Any help is highly appreciated.
----------------------------------------------------------------------
|
|
|
Re: Stored Procedure problem [message #36077 is a reply to message #36076] |
Fri, 02 November 2001 12:34 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
This is one of those cases in 8i (but not in 9i) where the PL/SQL engine doesn't support all SQL syntax. So, you will have to use dynamic SQL to execute this query:
v_query := 'insert into tmpResults
(select distinct c.Id clientid,
c.lastname,
c.firstname,
(SELECT NVL(vpa2.CodeDesc, ''UnSpecified'') FROM
vCodesAgency vpa2 WHERE vpa2.Code = vpa.Code
AND AgencyName NOT IN (''HUD'', ''SECTION 8'') AND ROWNUM = 1 AND NVL(vpa2.CodeDesc, ''%'') LIKE p_propagency) Agency
from client c, vCodesPropertyAgency vpa
where ...)';
Make sure all the parentheses are correct and notice that single quotes have been replaced by two single quotes.
Then, just:
execute immediate v_query;
----------------------------------------------------------------------
|
|
|
|