Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Posts
    3

    Question Unanswered: How to execute part of a WHERE clause conditionally ?

    Hi,

    I need to insert Data into an Error table based on particular condition. Since the same error will be triggered for several cases, I am trying trying to combine everything in one Single Sql statement as follows:

    Code:
    Code:
    INSERT INTO error_table
    (error_code,cust_id,customer_name,error_message )
    SELECT '34', cust_id,customer_name, 'Missing xxx'  
    FROM customers
    WHERE cust_id =  cur_cust.cust.id 
    AND ( NOT EXISTS ( Select .....)  -- control 1
    
    OR  (EXISTS ( Select .....)  --control 2
    
    OR  (NOT EXISTS ( Select .....) --  control 3
    I have around 10 controls. The Insert should be triggered for each control if the conditions are met.I was wondering if this can be done using a decode based on some variable outside the query.

    I want the query to run like this:
    if A then execute control 1: AND ( NOT EXISTS ( Select .....)
    If B then execute control 2: AND ( OR EXISTS ( Select .....)

    Thx

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    when processing some checks outside the query, you will end up in 10 different queries. However, it is possible inside the query, with something like this
    Code:
    WHERE cust_id =  cur_cust.cust.id 
    AND (
         (some_var = 'A' AND NOT EXISTS ( Select .....) )  -- control 1
      OR (some_var = 'B' AND EXISTS ( Select .....))  --control 2
      OR (some_var = 'C' AND NOT EXISTS ( Select .....) )--  control 3
        )
    (note I added some right brackets as they did not match in the sample)

  3. #3
    Join Date
    Oct 2010
    Posts
    3
    Thx, I will try this out

    I have another Issue:

    In the above code I am doing a select from customer table..So the error controls are for customers that exists in customer table. The Insert statement is inside a cursor for loop where I get the cur_cust.cust.id

    Now I have to do the same validation for customers that does not exists in customer table.

    I am currently doing a select count from customers for each cust_id. And if no row are returned then I am executing an Insert select from another table..

    Its something like this:

    Code:
    INSERT INTO error_table
    (error_code,cust_id,customer_name,error_message )
    SELECT '34', cust_id,customer_name, 'Missing xxx'  
    FROM customers
    WHERE cust_id =  cur_cust.cust.id 
    ......
    
    select count(1)
    INTO ls_count
    FROM customers
    WHERE cust_id =  cur_cust.cust.id 
    
    IF ls_count = 0 THEN
    
    INSERT INTO error_table
    (error_code,cust_id,customer_name,error_message )
    SELECT '34', cust_id,customer_name, 'Missing xxx'  
    FROM another_customer_table
    WHERE cust_id =  cur_cust.cust.id 
    ....;
    
    END IF;
    Can I combine these 2 Insert in one single query ?


    Thx

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    The main difficulty is querying for COUNT and optionally querying another table. You may incorporate it into the second query using NOT EXISTS (or NOT IN) clause. As you already noticed it, so I have nothing to add.

    Then, for returning all rows from two queries on different tables, you may use UNION ALL operator.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •