CODEWITHSUNDEEP
sqloracle-databaseplsql
Coding Duchess
Coding Duchess

Reputation: 6909

Contiditional WHERE clause with more than 3 conditions

I have a query with a conditional WHERE clause where I select users from Table1 based on a page item.

If a page item has a value of TYPE1 or TYPE2, I select the users from Table1 that are not in Table2, meeting first condition, and if a user is of TYPE3, then I select users from Table2 that are not in Table2 under a second condition.

Now I need to add two more types TYPE4 and TYPE5 but the difficulty is that if the users is in either of these types, they should not already exist in Table2 with a status='NEW'

      SELECT 1
      FROM   Table2 b
      WHERE  b.id = a.id            
         AND b.type_id = :P2_TEST_TYPE
         AND b.status = 'NEW'`

but should be in role 'PRIMARY' and 'SECONDARY' respectively:

mypackage.get_role(a.id) = 'PRIMARY' AND mypackage.get_type_id(TO_NUMBER(:P2_TEST_TYPE))='TYPE4'

AND

mypackage.get_role(a.id) = 'SECONDARY' AND mypackage.get_type_id(TO_NUMBER(:P2_TEST_TYPE))='TYPE5'

The query that takes care of TYPE1, TYPE2, and TYPE3 is below. How can I encorporate the conditions for TYPE4 and TYPE5 into this query:

SELECT a.ID, a.NAME
FROM Table1 a
WHERE NOT EXISTS
  (SELECT 1
      FROM   Table2 b
      WHERE  b.id = a.id            
         AND b.type_id = :P2_TEST_TYPE
         AND mypackage.get_category_id(b.parent_id) <> mypackage.get_category_id(:P2_PARENT_ID)
         AND b.status = 'NEW'
         AND mypackage.get_type_id(TO_NUMBER(:P2_TEST_TYPE)) IN ('TYPE1', 'TYPE2')
   UNION ALL
   SELECT 1
      FROM   Table2 b
      WHERE  b.id = a.id            
         AND b.type_id = :P2_TEST_TYPE
         AND b.status = 'NEW'
         AND mypackage.get_type_id(TO_NUMBER(:P2_TEST_TYPE)) = 'TYPE3'
  );

Upvotes: 0

Views: 512

Answers (2)

Coding Duchess
Coding Duchess

Reputation: 6909

Decided to split the logic between the two regions and then use show/hide to display an appropriate region

Upvotes: 0

Little Santi
Little Santi

Reputation: 8793

Due to the fact that both subqueries are quite resembling, I suggest you extract to a single subquery all the common AND conditions, and include an additional OR for each alternative condition. Then, group each set of conditions by their common sub-conditions (the one regarding b.status):

SELECT a.ID, a.NAME
FROM Table1 a
WHERE NOT EXISTS
(
    SELECT 1
    FROM   Table2 b
    WHERE  b.id = a.id            
        AND b.type_id = :P2_TEST_TYPE
        AND
        (
            (
                b.status = 'NEW' AND
                (
                    (
                        mypackage.get_type_id(TO_NUMBER(:P2_TEST_TYPE)) IN ('TYPE1', 'TYPE2')
                        AND
                        mypackage.get_category_id(b.parent_id) <> mypackage.get_category_id(:P2_PARENT_ID)
                    )
                    OR (mypackage.get_type_id(TO_NUMBER(:P2_TEST_TYPE)) = 'TYPE3')
                )
            )
            OR 
            (
                b.status <> 'NEW' AND
                (
                       (mypackage.get_role(a.id) = 'PRIMARY' AND mypackage.get_type_id(TO_NUMBER(:P2_TEST_TYPE))='TYPE4')
                    OR (mypackage.get_role(a.id) = 'SECONDARY' AND mypackage.get_type_id(TO_NUMBER(:P2_TEST_TYPE))='TYPE5')
                )
            )
        )
);

Certainly, it throws too much complexity. You should consider using a client language procedure instead.

Upvotes: 0

Related Questions