Solved

Oracle In statement with NVL

Posted on 2016-09-14
27
25 Views
Last Modified: 2016-10-03
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?
0
Comment
Question by:Tanuja Kadam
  • 11
  • 10
  • 5
27 Comments
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
 

Author Comment

by:Tanuja Kadam
Comment Utility
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
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> OR Length('&&param2') = 0 );

is not a good way to check for a null value

length('')  is null,  not 0
0
 

Author Comment

by:Tanuja Kadam
Comment Utility
sdstuber....you go what I want but I get missing expression error.
Also, && Param2 may contains multiple values.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> Also, && Param2 may contains multiple values.

give me an example of your usage with real sample data
0
 

Author Comment

by:Tanuja Kadam
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
before guessing again.

what will your parameter look like if it is null?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Tanuja Kadam
Comment Utility
just null...won't return anything
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
No worries.  I've got a ton of different things I have tried.  I cannot figure out how to get both sides working.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points (awarded by participants)
Comment Utility
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
 

Author Comment

by:Tanuja Kadam
Comment Utility
Can you please explain what q'[&&param2]' does?
Also, Ihow the INSTR function would work for IN statement?
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points (awarded by participants)
Comment Utility
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
 

Author Comment

by:Tanuja Kadam
Comment Utility
Thank you. I will incorporate the logic in my code.
0
 

Author Comment

by:Tanuja Kadam
Comment Utility
Sorry for the delay. you can close this item.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
it's your question, you can close it yourself.
in fact, that's the preferred method.
0
 

Author Comment

by:Tanuja Kadam
Comment Utility
I don't see any close or "Accepted solution" button.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
you don't see "Assisted Solution" and "Best Solution"  buttons?
0
 

Author Comment

by:Tanuja Kadam
Comment Utility
Nope
0
 

Author Comment

by:Tanuja Kadam
Comment Utility
see the screenshot attached.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now