Re: SQL in Pl/SQL

So, if City and State could be NULL, is it not possible that you will have multiple qualifying rows ? If so, you will get an error because SELECT INTO queries MUST return exactly one row.

However, you could do this:
SELECT ADID INTO V_ADID FROM OP_ADDRESS WHERE (CITY = V_CITY OR CITY IS NULL)
AND (STATE = V_STATE OR STATE IS NULL); To overcome the multiple row erro, do a cursor loop.

nwhitehead_at_gt.com

On Wed, 29 Apr 1998 16:01:51 -0400, Mark Aengenheyster <"'no spam loser' aengenhm"@rspa.dot.gov> wrote:

>hello everybody!
>
>I am having a serious lapse of intelligence here.
>
>here is my SQL which is inside my PL/SQL function:
> SELECT ADID INTO V_ADID FROM OP_ADDRESS
> WHERE CITY = V_CITY
> AND STATE = V_STATE;
>
>The problem is, city or state could be NULL but not necessarily.
>How do I right the SQL so that it will return a row if the value is NULL
>and if it is not NULL ( = 'something')?
>
>If it is null then I want to write:
> SELECT ADID INTO V_ADID FROM OP_ADDRESS
> WHERE CITY IS NULL
> AND STATE IS NULL;
>
>DECODE won't work and NVL doesn't help. I don't want to get into using
>dynamic SQL.
>What do I do?
>
>Thanks in advance
>
Received on Thu Apr 30 1998 - 02:48:45 CDT