6 Replies Latest reply: Aug 17, 2010 12:16 PM by user32322435 RSS

    CASE in WHERE Clause?

    user32322435
      Hi All,


      I'm on oracle 10gr2.

      I have the following where clause,,whenre I need to use case for one of the filtering condition in the where clause..depending on one of the input parameter..
      I have the segment of code below...Is the case statement correct, can we use it here?

      ||
                              LEFT OUTER JOIN RNS_CORE.V_GET_DVMT VAH
                              ON DATE_RANGE.DATE_YEAR = TO_CHAR(VAH.DATA_DATE,'YYYY')
                              CROSS JOIN YEAR_SUB 
                              WHERE
                             ( CASE WHEN p_route_opposite_ind='N' THEN
                                                      ROUTE_ID = p_route_id
                                           ELSE   ( ROUTE_ID = p_route_id
                                                       OR  ROUTE_ID =(SELECT RTE_OPPOSITE_DIRECTION_RTE_ID FROM RNS_CORE.TBL_RTE WHERE RTE_ID=p_route_id))
                                            END CASE );          
                              AND  (( ROUTE_FROM_MEASURE >= P_ROUTE_FROM
                              AND   ROUTE_TO_MEASURE   <= p_route_to_measure)
                              OR (P_ROUTE_FROM BETWEEN ROUTE_FROM_MEASURE AND ROUTE_TO_MEASURE)
                              OR (p_route_to_measure BETWEEN ROUTE_FROM_MEASURE AND ROUTE_TO_MEASURE)
                              ) 
                              AND   DATA_DATE BETWEEN TO_DATE('01/01/'||TO_CHAR(TO_DATE(p_start_date,'MM/DD/YYYY'),'yyyy'),'mm/dd/yyyy')
                              AND TO_DATE('12/31/'||TO_CHAR(TO_DATE(p_end_date,'MM/DD/YYYY'),'yyyy'),'mm/dd/yyyy')
                              --AND DATE_RANGE.DATE_YEAR IS NULL
      
                          );
      Thanks in advance

      Appreciate your sugessions

      HU
        • 1. Re: CASE in WHERE Clause?
          706888
          Hi,

          you said:
          >
          Is the case statement correct, can we use it here?
          >

          the statement is incorrect ... , you can use it there.

          try to replace your case with:
          ...WHERE
          ( CASE WHEN p_route_opposite_ind='N' THEN p_route_id
              ELSE  (SELECT RTE_OPPOSITE_DIRECTION_RTE_ID FROM RNS_CORE.TBL_RTE WHERE RTE_ID=p_route_id) END) = route_id
          AND ...
          here is another example:
          select * from dual d
          where (case when dummy='X' then 'X' else (select 'y' from dual) end)='X'
          REM: don't forget a case statement is used to send back a single value, so you need to compare the result of the case with something:

          (case cond ..... else .... end) = 'yxz'

          Edited by: user11268895 on Aug 17, 2010 4:04 PM

          Edited by: user11268895 on Aug 17, 2010 4:05 PM
          • 2. Re: CASE in WHERE Clause?
            Frank Kulash
            Hi,

            You don't need CASE for that. Try something like:
            WHERE   (    (       p_route_opposite_ind     = 'N'     AND route_id = p_route_id )
                 OR   ( NVL ( p_route_opposite_ind
                                , 'Y'
                         )                      != 'N'     AND route_id IN (
                                                                                 SELECT  rte_opposite_direction_rte_id 
                                                            FROM    rns_core.tbl_rte 
                                                            WHERE   rte_id  = p_route_id
                                                                              )
                         )
                    )
            AND     route_from_measure     >= p_route_from
            AND     ...
            You can use CASE in a WHERE clause. CASE does the same thing in a WHERE clause that it does anywhere else: it returns a single value in one of the SQL data types, such as DATE, NUMBER or VARCHAR2. (There is no boolean data type in SQL.)

            See this thread:
            Re: Error on procedure
            • 3. Re: CASE in WHERE Clause?
              user32322435
              Thanks all for your help!

              I should have been more clear about what I need...sorry about that..

              I have to change he where clause based on the value of an input parameter..
              like...

              if p_route_opposite_ind = 'N'

              then where clause is
              WHERE
              ROUTE_ID = p_route_id
              WHERE
                                      ROUTE_ID = p_route_id
                                      AND  (( ROUTE_FROM_MEASURE >= P_ROUTE_FROM
                                      AND   ROUTE_TO_MEASURE   <= p_route_to_measure)
                                      OR (P_ROUTE_FROM BETWEEN ROUTE_FROM_MEASURE AND ROUTE_TO_MEASURE)
                                      OR (p_route_to_measure BETWEEN ROUTE_FROM_MEASURE AND ROUTE_TO_MEASURE)
                                      ) 
                     
                                  );
              if p_route_opposite_ind ='Y'

              then where clause is
              WHERE
              *( ROUTE_ID = p_route_id OR ROUTE_ID =(SELECT RTE_OPPOSITE_DIRECTION_RTE_ID FROM RNS_CORE.TBL_RTE*
              WHERE    RTE_ID=p_route_id))
                 WHERE
                                      ( ROUTE_ID = p_route_id  OR  ROUTE_ID =(SELECT RTE_OPPOSITE_DIRECTION_RTE_ID FROM RNS_CORE.TBL_RTE
                 
                                                                                                        WHERE    RTE_ID=p_route_id))                           
                                      AND  (( ROUTE_FROM_MEASURE >= P_ROUTE_FROM
                                      AND   ROUTE_TO_MEASURE   <= p_route_to_measure)
                                      OR (P_ROUTE_FROM BETWEEN ROUTE_FROM_MEASURE AND ROUTE_TO_MEASURE)
                                      OR (p_route_to_measure BETWEEN ROUTE_FROM_MEASURE AND ROUTE_TO_MEASURE)
                                  );
              • 4. Re: CASE in WHERE Clause?
                Frank Kulash
                Hi,

                What is wrong with the code I posted? (I'm not claiming that it's right; I'm just saying that if I mis-understood the problem, then I have to know where it's wrong before I can fix it.)

                Post some sample data (CREATE TABLE and INSERT statements), the results you want to get from that data when p_route_opposite_ind = 'N', and the results you want from the same data when p_route_opposite_ind = 'Y'.
                Post your query (using a WHERE clause such as the one I suggested), and point out where that query is getting the wrong results.
                I assumed that p_route_opposite_ind was a VARCHAR2, and that it could be NULL. Correct me if I was wrong.

                Simplify your problem if possible. It looks like you have several other conditions besides the ones that depend on p_route_opposite_ind. If you understand how to do all of those other conditions, then don't include them in the problem, or maybe include just one other condition so that you can see how it interacts with the conditions that are depenedent on p_route_oppostie_ind. That way, it will be easier to understand the part you don't already understand, and you might not need to post as much sample data.
                • 5. Re: CASE in WHERE Clause?
                  user32322435
                  Sorry Frank..I didn't mean to say that your query is wrong....I was trying to give more details about what i was looking for..

                  I'm going to test your query...and hopefully it will address my concern..

                  Thanks
                  • 6. Re: CASE in WHERE Clause?
                    user32322435
                    Perfect....I replaced the CASE and got things straight.


                    Thanks!