Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
Can a VARCHAR2 have the value 0 ? –  ypercube Sep 13 '12 at 10:51
    
Now i passed the 0 as a string.. whether it is possible now ? –  Vasu Sep 13 '12 at 10:54
add comment

2 Answers 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) 
              ) ;
share|improve this answer
    
obj_A is primary. so, surely we will have the value. If obj_B =0 or obj_b is null means we no need to consider it. we will ignore. If obj_B <>0 and obj_B is not null means we need to add the w.delegate_user_id=obj_B with w.customerID= obj_A –  Vasu Sep 13 '12 at 12:31
1  
Ok, but can you see that you don't need a special WHERE construct - just think about the logic –  Phil Sep 13 '12 at 12:37
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.