Re: SQL in Pl/SQL

SELECT ADID INTO V_ADID FROM OP_ADDRESS WHERE
   nvl(CITY,' ') = nvl(V_CITY,' ')
AND nvl(STATE,' ') = nvl(V_STATE,' ');

' ' is '<space>'

Mark Aengenheyster <"'no spam loser' aengenhm"@rspa.dot.gov> wrote in article <3547872F.3C3E590F_at_rspa.dot.gov>...
> 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 Wed Apr 29 1998 - 17:37:06 CDT