SQL with 3 conditions with join: "where X in(foo)", "where X not in(foo)", or no "where" rule

I am trying to create a query where, depending on a variable, results included are a result of one of three conditions:

1) WHERE am.process     IN (SELECT process_name FROM it.special_processes)
2) WHERE am.process NOT IN (SELECT process_name FROM it.special_processes)
3) (equivalent of no WHERE clause for the process)

This query works for condition 1:

SELECT info.sortingorder sort, info.stage, SUM(am.qty) boh
FROM it.snpshot am, it.info info, it.naming nm
WHERE info.stage = nm.stage(+)
AND am.process  IN (SELECT process_name FROM it.special_processes)
GROUP BY info.sortingorder, info.stage

This works for the condition 1 or, depending on :flag, condition 3:

SELECT info.sortingorder sort, info.stage, SUM(am.qty) boh
FROM it.snpshot am, it.info info, it.naming nm
W开发者_Go百科HERE info.stage = nm.stage(+)
AND :FLAG = 1 OR am.process IN (SELECT process_name FROM it.special_processes)
GROUP BY info.sortingorder, info.stage

However, when I want to handle condition 3, things get complicated. I tried something like:

SELECT info.sortingorder sort, info.stage, SUM(am.qty) boh
FROM it.snpshot am, it.info info, it.naming nm
WHERE info.stage = nm.stage(+)
AND :flag = 0    -- (this is for condition 3)
OR (:flag = 1 AND am.process     IN (SELECT process_name FROM it.special_processes))
OR (:flag = 2 AND am.process NOT IN (SELECT process_name FROM it.special_processes))
GROUP BY info.sortingorder, info.stage

...But I get:

ORA-01719: outer join operator (+) not allowed in operand of OR or IN
*Cause:    An outer join appears in an or clause.
*Action:   If A and B are predicates, to get the effect of (A(+) or B),
           try (SELECT WHERE (A(+) AND NOT B)) UNION ALL (SELECT WHERE (B)).
Error at Line: 5 Column: 19

The error message attempts to give some advice, but I don't see how it applies to a set of conditions like shown above (conditional UNION ALL). Removing the outer join (+) eliminates the error, but it's there for a reason. The complexity is starting to explode (especially considering the above is a distillation of a much larger query) and I already have an SQL "NOT IN()" which I usually see as a red flag. My previous approach was to dynamically generate the "NOT IN" or "IN" part of the query string, but that always seemed like an awful hack. Note: PL/SQL is not an option for various reasons.

My question is: How would you handle this situation?

I'm guessing you're just missing up your logical ands and ors here.

SELECT info.sortingorder sort, info.stage, SUM(am.qty) boh
FROM it.snpshot am, it.info info, it.naming nm
WHERE info.stage = nm.stage(+)
AND (:flag = 0    -- (this is for condition 3)
OR (:flag = 1 AND am.process     IN (SELECT process_name FROM it.special_processes))
OR (:flag = 2 AND am.process NOT IN (SELECT process_name FROM it.special_processes))
)
GROUP BY info.sortingorder, info.stage

Now all the terms are and-ed together. Previously, you were missing one set of (); I have added it after the word "and" before the :flag = 0 and before the group by. Believe that'll get you by. Do you see why? true and false and false or true = true.

Personally, I'd try very hard to get this into an IF block, and write the three versions of the query; the EXPLAIN PLAN for 'conditional' queries such as these tends to be pretty poor...

What happens if you change your notation to explicit JOINs?

SELECT
  info.sortingorder sort,
  info.stage,
  SUM(am.qty) boh
FROM
  it.snpshot am
CROSS JOIN
  it.info    info
LEFT JOIN
  it.naming  nm
    ON info.stage = nm.stage
WHERE
       (:flag = 0)   -- (this is for condition 3)
    OR (:flag = 1 AND am.process     IN (SELECT process_name FROM it.special_processes))
    OR (:flag = 2 AND am.process NOT IN (SELECT process_name FROM it.special_processes))
GROUP BY
  info.sortingorder,
  info.stage

EDIT

This may or may not help your error, but may yield a better EXPLAIN PLAN...

WHERE
  CASE WHEN EXISTS (SELECT * FROM it.special_processes WHERE process_name = am.process)
       THEN 2 ELSE 1 END
  <>
  :flag

• flag = 0 => unequal (true) always (CASE never returns a 0)
• flag = 1 => unequal (true) if am.process exists in it.special_processes
• flag = 2 => unequal (true) if am.process doesn't exist in it.special_processes

First, I'd lose the old outer join syntax and rewrite using ANSI (that might get rid of your error message right off the bat - it's a shortcoming of the old (+) syntax). I'd then try to incorporate CASE logic in the where clause (untested):

SELECT info.sortingorder sort, info.stage, SUM(am.qty) boh
FROM it.snapshot am
   , it.info info LEFT JOIN it.naming nm ON info.stage = nm.stage
WHERE CASE WHEN 
           (:flag = 1 AND am.process IN 
              (SELECT process_name FROM it.special_processes)) THEN 'Y'
           WHEN 
           (:flag = 2 AND am.process NOT IN 
              (SELECT process_name FROM it.special_processes)) THEN 'Y'
           WHEN :flag = 0 THEN 'Y'
           ELSE 'N'
      end = 'Y'
GROUP BY info.sortingorder, info.stage;

My solution for "no where clause" typically is to supply a predicate as such:

WHERE 1 = 1

Since it always evaluates to true, it should have no impact when included, even alongside other predicate conditions. I have, however, seen it change the query execution plan in Microsoft SQL Server.