ORACLE SQL AND/OR combination

Question

i need some help with an SELECT statement that i wrote.

The statement looks like:

SELECT  DISTINCT MANDT, PATH301 
FROM    NC301B
WHERE   EDIPROC like 'P30_'
    AND (LF301M > 0) 
    AND (MANDT LIKE '011')
    AND (PATH301 NOT LIKE '%:\saptemp%')
    AND (PATH301 NOT LIKE '%:\SAPTEMP%')  
    AND (PATH301 NOT LIKE '%usr%')  
    AND (PATH301 NOT LIKE '%:\Windows%')
    AND (PATH301 NOT LIKE '%PKV_DAV%')
    AND (PATH301 NOT LIKE '%pkv_dav%');

Now i need to check for an additional MANDT -> MANDT LIKE '012'

My problem is that the output is not correct when i add the line OR (MANDT LIKE '012') to the statement.

My idea was:

SELECT  DISTINCT MANDT, PATH301 
FROM    NC301B
WHERE   EDIPROC like 'P30_'
    AND (LF301M > 0) 
    AND (MANDT LIKE '011')
    OR (MANDT LIKE '012')
    AND (PATH301 NOT LIKE '%:\saptemp%')
    AND (PATH301 NOT LIKE '%:\SAPTEMP%')  
    AND (PATH301 NOT LIKE '%usr%')  
    AND (PATH301 NOT LIKE '%:\Windows%')
    AND (PATH301 NOT LIKE '%PKV_DAV%')
    AND (PATH301 NOT LIKE '%pkv_dav%');

Is it possible to check for two or more MANDT Values in one statement like i did in my example?

Thank you!

why you use like in there? MANDT= '011' cant you use this? — Sandun Chathuranga
– Sandun Chathuranga, Commented Jul 5, 2016 at 10:24
we use like when we use wildcard operation like % , _ , [ , ] — Sandun Chathuranga
– Sandun Chathuranga, Commented Jul 5, 2016 at 10:28

mcha · Accepted Answer · 2016-07-05 10:18:51Z

3

You need to pay attention to the parenthesis, try this one

SELECT  DISTINCT MANDT, PATH301  FROM    NC301B WHERE   EDIPROC like
'P30_'
    AND (LF301M > 0) 
     AND (MANDT LIKE '011' OR MANDT LIKE '012')
     AND (PATH301 NOT LIKE '%:\saptemp%')
     AND (PATH301 NOT LIKE '%:\SAPTEMP%')  
     AND (PATH301 NOT LIKE '%usr%')  
     AND (PATH301 NOT LIKE '%:\Windows%')
     AND (PATH301 NOT LIKE '%PKV_DAV%')
     AND (PATH301 NOT LIKE '%pkv_dav%');

answered Jul 5, 2016 at 10:18

mcha

3,0085 gold badges27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2016-07-05 10:18:19Z

2

Put parentheses around the two checks on MANDT:

SELECT  DISTINCT MANDT, PATH301 
FROM    NC301B
WHERE   EDIPROC LIKE 'P30_' AND
    (LF301M > 0) AND
    ((MANDT LIKE '011') OR (MANDT LIKE '012')) AND
    (PATH301 NOT LIKE '%:\saptemp%') AND
    (PATH301 NOT LIKE '%:\SAPTEMP%') AND
    (PATH301 NOT LIKE '%usr%') AND
    (PATH301 NOT LIKE '%:\Windows%') AND
    (PATH301 NOT LIKE '%PKV_DAV%') AND
    (PATH301 NOT LIKE '%pkv_dav%');

answered Jul 5, 2016 at 10:18

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Comments

jpw · Accepted Answer · 2016-07-05 10:18:24Z

2

You need to use parentheses to group the two predicates as and has higher precedence than or:

...
AND ( (MANDT LIKE '011') OR (MANDT LIKE '012') ) 
...

answered Jul 5, 2016 at 10:18

jpw

45k6 gold badges71 silver badges90 bronze badges

Comments

Mottor · Accepted Answer · 2016-07-05 11:25:42Z

You can use REGEXP_LIKE and because you do not have % in '011' and '012', you can use IN Condition :

SELECT DISTINCT MANDT, PATH301
  FROM NC301B
 WHERE EDIPROC LIKE 'P30_' 
   AND LF301M > 0
   AND MANDT IN ('011','012')
   AND NOT regexp_like(path301, '(:\\saptemp|:\\SAPTEMP|usr|:\\Windows|PKV_DAV|pkv_dav)')

Because:

The default case sensitivity is determined by the value of the NLS_SORT parameter.

you can use the third parameter in regexp_like:

'i' specifies case-insensitive matching.

'c' specifies case-sensitive matching.

Collectives™ on Stack Overflow

ORACLE SQL AND/OR combination

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related