Solved

Oracle In statement with NVL

Posted on 2016-09-14
27
43 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 10
  • 5
27 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 41798468
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
ID: 41798512
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 35

Expert Comment

by:johnsone
ID: 41798579
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 41798580
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 74

Expert Comment

by:sdstuber
ID: 41798583
>>> 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
ID: 41798615
sdstuber....you go what I want but I get missing expression error.
Also, && Param2 may contains multiple values.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41798619
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 74

Expert Comment

by:sdstuber
ID: 41798628
>>> Also, && Param2 may contains multiple values.

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

Author Comment

by:Tanuja Kadam
ID: 41798634
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 74

Expert Comment

by:sdstuber
ID: 41798640
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 35

Expert Comment

by:johnsone
ID: 41798648
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 74

Expert Comment

by:sdstuber
ID: 41798660
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 74

Expert Comment

by:sdstuber
ID: 41798662
before guessing again.

what will your parameter look like if it is null?
0
 

Author Comment

by:Tanuja Kadam
ID: 41798663
just null...won't return anything
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41798671
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 74

Accepted Solution

by:
sdstuber earned 500 total points (awarded by participants)
ID: 41798720
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
ID: 41798731
Can you please explain what q'[&&param2]' does?
Also, Ihow the INSTR function would work for IN statement?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points (awarded by participants)
ID: 41798764
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
ID: 41798831
Thank you. I will incorporate the logic in my code.
0
 

Author Comment

by:Tanuja Kadam
ID: 41821545
Sorry for the delay. you can close this item.
0
 
LVL 74

Expert Comment

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

Author Comment

by:Tanuja Kadam
ID: 41821610
I don't see any close or "Accepted solution" button.
0
 
LVL 74

Expert Comment

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

Author Comment

by:Tanuja Kadam
ID: 41821630
Nope
0
 

Author Comment

by:Tanuja Kadam
ID: 41821633
see the screenshot attached.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41821659
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

695 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