Web Development



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
7 FROM swbpkpt b
8 WHERE b.permit = a.permit + 1
9 )
10* )


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.

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.

Related Discussions

Related Forums