This content has been marked as final. Show 6 replies
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:
here is another example:
...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 ...
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:
select * from dual d where (case when dummy='X' then 'X' else (select 'y' from dual) end)='X'
(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
You don't need CASE for that. Try something like:
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.)
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 ...
See this thread:
Re: Error on procedure
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..
if p_route_opposite_ind = 'N'
then where clause is
ROUTE_ID = p_route_id
if p_route_opposite_ind ='Y'
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) ) );
then where clause is
*( ROUTE_ID = p_route_id OR ROUTE_ID =(SELECT RTE_OPPOSITE_DIRECTION_RTE_ID FROM RNS_CORE.TBL_RTE*
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) );
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.