Oracle In statement with NVL

Hi ,

I am writing a query in a PL/sql block.

   SELECT parid,taxyr,user9,user10
   FROM CHARGE
   WHERE taxyr =  2014
   AND user9 in (select nvl(&&param2,user9) from charge);

How do I write the instatement with nvl?
Tanuja KadamProgrmmer AnalystAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor Commented:
try this...

I am making the assumption that the characters "[" and "]" won't ever be in your parameter
and that user9 won't have any quotes in it.


SELECT parid,
       taxyr,
       user9,
       user10
  FROM charge
 WHERE taxyr = 2014 AND (q'[&&param2]' IS NULL OR INSTR(q'[&&param2]', '''' || user9 || '''') > 0)
0
 
johnsoneSenior Oracle DBACommented:
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); 

Open in new window

0
 
Tanuja KadamProgrmmer AnalystAuthor Commented:
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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
johnsoneSenior Oracle DBACommented:
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 ); 

Open in new window

0
 
sdstuberCommented:
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
0
 
sdstuberCommented:
>>> OR Length('&&param2') = 0 );

is not a good way to check for a null value

length('')  is null,  not 0
0
 
Tanuja KadamProgrmmer AnalystAuthor Commented:
sdstuber....you go what I want but I get missing expression error.
Also, && Param2 may contains multiple values.
0
 
johnsoneSenior Oracle DBACommented:
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.
0
 
sdstuberCommented:
>>> Also, && Param2 may contains multiple values.

give me an example of your usage with real sample data
0
 
Tanuja KadamProgrmmer AnalystAuthor Commented:
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.
0
 
sdstuberCommented:
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))
0
 
johnsoneSenior Oracle DBACommented:
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
0
 
sdstuberCommented:
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!
0
 
sdstuberCommented:
before guessing again.

what will your parameter look like if it is null?
0
 
Tanuja KadamProgrmmer AnalystAuthor Commented:
just null...won't return anything
0
 
johnsoneSenior Oracle DBACommented:
No worries.  I've got a ton of different things I have tried.  I cannot figure out how to get both sides working.
0
 
Tanuja KadamProgrmmer AnalystAuthor Commented:
Can you please explain what q'[&&param2]' does?
Also, Ihow the INSTR function would work for IN statement?
0
 
sdstuberConnect With a Mentor Commented:
easiest method is to simply execute the query with null and sample data and see what the resulting sql is.

but, for explanation...


the q'[xyz]'    syntax is an alternate syntax for 'xyz'

but, it allows for embedded quotes.  so if your parameter is

'abc'

then the string becomes  q'['abc']'  which is legal; but if it was simply encased within quotes then it would be ''abc''  which is not a legal string.

if your parameter is null (blank) then your query, after substitution becomes


SELECT parid,
        taxyr,
        user9,
        user10
   FROM charge
  WHERE taxyr = 2014 AND (q'[]' IS NULL OR INSTR(q'[]', '''' || user9 || '''') > 0)

which works because q'[]'  is a null string and valid syntax


similarly if your parameter is     '9A','10','11b','C'

then your statement becomes

SELECT parid,
        taxyr,
        user9,
        user10
   FROM charge
  WHERE taxyr = 2014 AND (q'['9A','10','11b','C']' IS NULL OR INSTR(q'['9A','10','11b','C']', '''' || user9 || '''') > 0)


the instr works by checking wrapping your column in quotes
so, for example  is if your user9  is C, then that becomes 'C'

and then INSTR checks if

'C'  is a subset of   '9A','10','11b','C'  and determines yes, it can be found at character 17

we don't check for the string itself because that might give false positives.

for example if your parameter was '9A','10','11b','8C'  

then C would be found,  because it's a substring of 8C  (no quotes)

but 'C' would not match because it's not a subset of '8C' (with quotes)

adding the quotes makes it a little bit complicated to visualize, but since that's how your input parameter is structured, that's how the substrings have to be compared in order to be reliable.
1
 
Tanuja KadamProgrmmer AnalystAuthor Commented:
Thank you. I will incorporate the logic in my code.
0
 
Tanuja KadamProgrmmer AnalystAuthor Commented:
Sorry for the delay. you can close this item.
0
 
sdstuberCommented:
it's your question, you can close it yourself.
in fact, that's the preferred method.
0
 
Tanuja KadamProgrmmer AnalystAuthor Commented:
I don't see any close or "Accepted solution" button.
0
 
sdstuberCommented:
you don't see "Assisted Solution" and "Best Solution"  buttons?
0
 
Tanuja KadamProgrmmer AnalystAuthor Commented:
Nope
0
 
Tanuja KadamProgrmmer AnalystAuthor Commented:
see the screenshot attached.
0
 
sdstuberCommented:
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
0
All Courses

From novice to tech pro — start learning today.