Oracle In statement with NVL

If that is really in a PL/SQL block, you are missing the INTO portion of your query.  You shouldn't be using SQL*Plus substitution variables in the PL/SQL block either.

Otherwise, I believe this will do what you want, but it isn't really clear to me what you are trying to do.

SELECT parid, 
       taxyr, 
       user9, 
       user10 
FROM   charge 
WHERE  taxyr = 2014 
       AND user9 = Nvl(&&param2, user9); 

Select allOpen in new window

This select statement is a cursor. &&param2 consists of multiple values.
and if that filed is null then the field user9 = user9.
I have to use in statement for this.

If your in list contains numbers, then this should work.

SELECT parid, 
       taxyr, 
       user9, 
       user10 
FROM   charge 
WHERE  taxyr = 2014 
       AND ( user9 IN ( &&param2 ) 
              OR Length('&&param2') = 0 ); 

Select allOpen in new window

I'm assuming you want something that checks if your paramter is null, then get all results for 2014
otherwise, get only those results where user9 matchs your parameter.

If so, try this:

SELECT parid,taxyr,user9,user10
    FROM CHARGE
    WHERE taxyr =  2014
    AND (&&param2 is null or  user9 = &&param2)

there is a slight functional difference between this and the queries in the previous posts in that it will return values even when user9 is NULL, where as the original and previous query will only return values where user9 is not null (regardless if the param2 field is null or not)

That may or may not be what you want

>>> OR Length('&&param2') = 0 );

is not a good way to check for a null value

length('')  is null,  not 0

sdstuber....you go what I want but I get missing expression error.
Also, && Param2 may contains multiple values.

You know, I knew that length of null is null.  Not sure what I was thinking today.

You cannot use the = it must be in because it is a list of values as stated.  A straight = is easy.  I'm still trying to figure out a unknown list of values.

>>> Also, && Param2 may contains multiple values.

give me an example of your usage with real sample data

Param2 values could be as below,
  '9A','10','11b','C'

So the user9 field from CHARGE can be the above values and if the Param2 is null then the And clause should be ignored.

I'm making the assumption that if param2 is null it will be set to '', not simply left blank.

SELECT parid,taxyr,user9,user10
     FROM CHARGE
     WHERE taxyr =  2014
     AND (&&param2 is null or user9 in (&&param2))

That won't work.  The result of the substitution is:

SELECT parid,taxyr,user9,user10
     FROM CHARGE
     WHERE taxyr =  2014
     AND ( '9A','10','11b','C' is null or user9 in ( '9A','10','11b','C'))

And that results in a ORA-00920:  Invalid releational opertator

hah, yeah, you're right.

I had one side figured out for if it was null and the other side for it was not.  but didn't take into account the reverse!

before guessing again.

what will your parameter look like if it is null?

just null...won't return anything

No worries.  I've got a ton of different things I have tried.  I cannot figure out how to get both sides working.

Can you please explain what q'[&&param2]' does?
Also, Ihow the INSTR function would work for IN statement?

Thank you. I will incorporate the logic in my code.

Sorry for the delay. you can close this item.

it's your question, you can close it yourself.
in fact, that's the preferred method.

I don't see any close or "Accepted solution" button.

you don't see "Assisted Solution" and "Best Solution"  buttons?

Nope

see the screenshot attached.

no screen shot attached.

but you have 2 options now.
you can just let it go with the auto close already in progress if you agree
if you do not agree with the close in progress, then click the "request attention" link at bottom of your original question and a moderator will assist you