PLS-00103 error

[ofsouto]

Hi

The query bellow runs ok alone, but when I try to compile a stored procedure with it inside an if statement an error message shows up.

query:
select nvl((select min(code) from table_1 where value_1 >=
100),
(select max(code) from table_1 where value_1 < 100))
from dual;

error message:
PLS-00103: Encountered the symbol &quot;SELECT&quot; when expecting one of the following:

( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count c

What's wrong? How can I solve this problem?

Best Regards

Obede

 

[Sudhi78]

check the previous lines of the query; looks like previous line is not completed. The INTO clause is missing in the SELECT statement.

 

[ofsouto]

Sorry!

As I write, query runs ok isolated.
When I compile procedure a message error shows up on the 2nd &quot;select&quot; (select min(code)).

Best regards
Obede

 

[carp]

In PL/SQL, you have to SELECT ... INTO.

Try this:

PROCEDURE my_proc IS
l_code table_1.code%TYPE;
.
.
BEGIN
select nvl((select min(code) from table_1 where value_1 >= 100),
(select max(code) from table_1 where value_1 < 100))
INTO l_code
from dual;
.
.
.
END;

 

[sem]

Try this:

select nvl(a.val, b.val)
from
(select min(code) from table_1 where value_1 >= 100) a,
(select max(code) from table_1 where value_1 < 100) b

SQL engine normally takes the lead over PL/SQL one.

Regards, Dima