Question

Locked

working SQL statement yielding null when embedded in PL/SQL?

By john.a.wills ·
I do this with the shown result in SQLPlus:
JWILLS@pprd>get newpermit
1 SELECT min (newpermit)
2 FROM (SELECT permit + 1 as newpermit
3 FROM swbpkpt a
4 WHERE permit between 10000 and 99999
5 AND a.expiry_year = 2011
6 AND NOT EXISTS (SELECT 'x'
7 FROM swbpkpt b
8 WHERE b.permit = a.permit + 1
9 )
10* )
JWILLS@pprd>/

MIN(NEWPERMIT)
--------------
10083

JWILLS@pprd>
When I put the statement into a PL/SQL program running on the same database, I use a program variable instead of 2011. By trace with htp.p I know that the variable has value 2011. The Select statement however yields null. Why does essentially the same SQL statement work differently in SQLPlus and PL/SQL, and what can I do to get it working in PL/SQL as it does in SQLPlus?

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

programmer error

by john.a.wills In reply to working SQL statement yie ...

The clause in the NOT EXISTS qualification lacks a reference to the expiry year, so that, depending on what has happened with permits for other expiry years, there may indeed be a permit at the crucial position in the sequence. My fault.

Back to Web Development Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums