How do I use the IF…ELSE condition in a WHERE clause?

Question

I'm attempting to use the IF...ELSE construct in my WHERE clause to selectively apply conditions to my SELECT.

Should this work?

CREATE OR REPLACE package body If_Else_Pack IS

    PROCEDURE Moving
    (
        obj_A       IN varchar2,
        obj_B       IN varchar2,
        obj_C       IN varchar2,
        obj_D       IN varchar2,
        cur_Result  OUT T_CURSOR
    ) IS
    BEGIN
        OPEN cur_Result FOR
        SELECT 
            w.assetid,
            w.vehiclenumber,
            w.LatLong,
            w.CurrentSpeed,
            w.timeOfMovement,
            w.CurrentPlace,
            w.curTime,
            w.motion,
            w.fuelR,
            w.VehicleStart
        FROM waypoints1 w
        WHERE 
            IF ((obj_D= '0' OR obj_D IS NULL) AND (obj_C= '0' OR  obj_C IS NULL)) THEN
                WHERE w.customer_id =obj_A
                AND w.delegate_user_id = obj_B;
            ELSE IF ((obj_D= '0 'OR obj_D IS NULL) AND (obj_C<> '0' OR obj_C IS NOT NULL)) THEN
                WHERE w.customer_id = obj_A
                AND w.category_id = obj_C
                AND w.delegate_user_id = obj_B;
            ELSE IF ((obj_D<> '0' OR obj_Dis NOT NULL) AND(obj_C= '0' OR obj_C IS NULL)) THEN
                WHERE w.customer_id = obj_A
                AND w.fleet_id = obj_D
                AND w.delegate_user_id = obj_B;
    END MOVING;
END IF_ELSE_PACK;

Answer 1

You should look up the CASE construct. Adrian's article is quite helpful. The Oracle documentation is here.

With the kind of query you want to do I can't help but think there are some omissions in the database schema.. Surely you can add some constraints on obj_C and obj_D so that null is not allowed? This would simplify things.

Answer 2

I think the question is a little misleading & causes people to not think properly. Looks like the 3 conditions won't overlap, so all you need to do is OR the 3 statements together:

create or replace package body If_Else_Pack is

  Procedure Moving(obj_A IN varchar2,
                   obj_B  IN varchar2,
                   obj_C IN varchar2,
                   obj_D    IN varchar2,
                   cur_Result     OUT T_CURSOR) is

   begin

        open cur_Result for
        select w.assetid,
                 w.vehiclenumber,
                 w.LatLong,
                 w.CurrentSpeed,
                 w.timeOfMovement,
                 w.CurrentPlace,
                 w.curTime,
                 w.motion,
                 w.fuelR,
                 w.VehicleStart
        from waypoints1 w
        where 
        (((obj_D= '0' or obj_D is null) and (obj_C= '0' oR  obj_C is null)) 
                 and w.customer_id =obj_A
                 and w.delegate_user_id = obj_B)
        or
        (((obj_D= '0 'or obj_D is null) and (obj_C<> '0' or obj_C is not null)) 
           and w.customer_id = obj_A 
           and w.category_id = obj_C 
           and w.delegate_user_id = obj_B)
        or
        (((obj_D<> '0' or obj_D is not null) and (obj_C= '0' or obj_C is null))
           and w.customer_id = obj_A
           and w.fleet_id = obj_D
           and w.delegate_user_id = obj_B);
END MOVING;
END IF_ELSE_PACK;

---

The WHERE condition can be further simplified to:

        WHERE 
              w.customer_id = obj_A
          AND 
              w.delegate_user_id = obj_B
          AND 
              ( (obj_D = '0' or obj_D IS NULL) AND (obj_C= '0' OR obj_C IS NULL) 
             OR 
                (obj_D = '0' or obj_D IS NULL) AND (w.category_id = obj_C) 
             OR 
                (w.fleet_id = obj_D) AND (obj_C= '0' OR obj_C IS NULL) 
              ) ;