CODEWITHSUNDEEP
sqloracle-databasestored-procedures
gbppa
gbppa

Reputation: 35

Sub query not allowed in Stored procedure error

I am trying to execute a procedure but getting an error in the below part of the procedure.

Getting the below errors:

PL/SQL: Statement ignored ERROR PLS-00405: subquery not allowed in PLS-00405: subquery not allowed in this context this context ERROR

The code is:

   --DECLARE
   L_ERROR_POINT   VARCHAR2 (100);
BEGIN
   P_STATUS_CODE := 0;
   P_ERROR_MESSAGE := '';

   --My error occurs in the below two lines--

   IF(P_DE || ' A' = (SELECT DAL_ET.PARAMETER_DESC FROM DAL_ET) 
   AND P_IDENTIFIER_TYPE_DESCRIPTION =(SELECT DAL_ET.PARAMETER_TEXT_VALUE FROM DAL_ET))

   THEN

   L_ERROR_POINT := 'INSERT INTO DAL_ID_TB';

How can i rewrite this query? Thanks in advance.

Upvotes: 0

Views: 2642

Answers (3)

Tony Andrews
Tony Andrews

Reputation: 132570

You cannot run a SQL statement within an IF condition. Instead you must run the SQL statement first, save the result into a variable, and use that in the IF:

SELECT DAL_ET.PARAMETER_DESC 
  INTO l_param_desc
  FROM DAL_ET;

IF l_param_desc = P_DE || ' A' 
AND ...

Upvotes: 0

Ted
Ted

Reputation: 4067

change the following into variables:

SELECT DAL_ET.PARAMETER_DESC
into   v_PARAMETER_DESC
FROM DAL_ET

SELECT DAL_ET.PARAMETER_TEXT_VALUE
into   v_PARAMETER_TEXT_VALUE

and use them in the if statement:

IF(P_DE || ' A' = v_PARAMETER_DESC
AND P_IDENTIFIER_TYPE_DESCRIPTION = v_PARAMETER_TEXT_VALUE)
.....

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

You can not use a query directly within an IF statement; to get what you need, you can use a variable:

DECLARE
  vCheckValue varchar2(100);
  ...
BEGIN
  select ...
  into vCheckValue 
  from ...
  where ...
  --
  if (vCheckValue  = ...) then
     ...
  end if;
END;

Upvotes: 2

Related Questions