Monday, March 04, 2013 7:58 PM
I would like to use CASE and return a list. Here is my query:DECLARE @RX varchar(1)
SET @RX = '1'
U.time between '2/11/13' and '2/11/13' and
U.node in (
when '1' then ('ABC')
when '2' then ('ABC','DEF')
The CASE when @RX=2 doesn't work.
Monday, March 04, 2013 8:06 PM
You need to change your query for this:
DECLARE @RX varchar(1) SET @RX = '1' SELECT U.time, U.Node From U Where U.time between '2/11/13' and '2/11/13' and ( (@RX = '1' and u.node IN ('ABC')) OR (@RX = '2' and u.node IN ('ABC','DEF')) )
Sergio Sánchez Arias
AYÚDANOS A AYUDARTE
Monday, March 04, 2013 8:42 PMModeratorThere are several problems with the above. First of all, why do you declare 1 character variable as varchar(1) instead of char(1)? Secondly, I suggest to use two separate queries for your two cases and don't put @Rx variable into the query at all.
For every expert, there is an equal and opposite expert. - Becker's Law
- Marked As Answer by Iric WenModerator Tuesday, March 12, 2013 7:52 AM
Monday, March 04, 2013 9:39 PM
>> I would like to use CASE and return a list. <<
CASE is an expression; expression return scalar values. This is not just SQL, but any programming language. TIME is a reserved word in SQL and the rest of your code violates ISO-11179 and other industry standards. Please consider how silly VARCHAR(1) is. Why don't you know the ISO-8601 date format?
If you are really determined to use CASE, try this:
DECLARE @local_something_flg CHAR(1);
SELECT U.screwup_date, Something_node
FROM Unnamed_Stuff AS U
WHERE U.screwup_date BETWEEN '2013-02-11' AND '2013-02-11'
IN (CASE WHEN @local_something_flg IN ('1', '2')
THEN 'ABC' ELSE '' END,
CASE WHEN @local_something_flg ='2')
THEN 'DEF' ELSE '' END);
I am assuming that something_node cannot be an empty string.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL