Menu
I'm brand-new to the Oracle world so this could be a softball. In working with an SSRS report, I'm passing in a string of states to a view. The twist is that the users could also pick a selection from the state list called "[ No Selection ]" ... (that part was not by doing and I'm stuck with implementing things this way)
If they choose the No Selection option, then I just want to return all states by default, otherwise return just the list of states that are in my comma-separated list.
This really seems like it should be easy but I'm stuck. Here is the code I have so far (just trying to get a sample to work) but my eyes have finally gone crossed trying to get it going.
Could anybody give me some direction on this one please?
begin
:stateCode:='MO,FL,TX';
--:stateCode:='[ No Selection ]';
end;
/
select count(*) as StateCount, :stateCode as SelectedVal
from hcp_state vw
where
(case
when (:stateCode = '') then (1)
when (:stateCode != '') then (vw.state_cd in (:stateCode))
(else 0)
end)
;
575
Introduction to Oracle CASE expressionYou can use a CASE expression in any statement or clause that accepts a valid expression. For example, you can use the CASE expression in statements such as SELECT , UPDATE , or DELETE , and in clauses like SELECT , WHERE , HAVING , and ORDDER BY .
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
Oracle NVL2 function is replaced by Case Statement in SQL Server. The NVL2 function evaluates the first expression (expr1, expr2, expr3). The NVL2 function returns the second expression if the first expression is not null. If the first expression is null, the third expression is returned.
Can CASE statements be nested? type_pre, As we see, Oracle allows nested CASE operators.
You can write the where clause as:
where (case when (:stateCode = '') then (1) when (:stateCode != '') and (vw.state_cd in (:stateCode)) then 1 else 0) end) = 1; Alternatively, remove the case entirely:
where (:stateCode = '') or ((:stateCode != '') and vw.state_cd in (:stateCode)); Or, even better:
where (:stateCode = '') or vw.state_cd in (:stateCode) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
