Using WHERE in a SELECT CASE statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using WHERE in a SELECT CASE statement

Author

Topic

BennyBCE
Starting Member

3 Posts

Posted - 2009-03-18 : 20:59:13

Hi guys,

I found this forum after some desperate Googling for an SQL query problem I have. I've spent almost two days trying to find what I need without success and hope one of you would be so kind as to point me in the right direction.

I have a database that logs machine values into multiple rows. EG:

DateAndTime ---------- TagIndex --- Val ---
18/03/2009 3:11:09 PM --- 0 ------ 405650
18/03/2009 3:11:09 PM --- 1 ------ 405652
18/03/2009 3:11:09 PM --- 2 ------ 32.29
18/03/2009 3:27:25 PM --- 0 ------ 405650
18/03/2009 3:27:25 PM --- 1 ------ 405652
18/03/2009 3:27:25 PM --- 2 ------ 32.5
18/03/2009 3:27:28 PM --- 0 ------ 405650
18/03/2009 3:27:28 PM --- 1 ------ 405652
18/03/2009 3:27:28 PM --- 2 ------ 32.5

I'm using a SELECT CASE statement to return the multiple rows as recordset rows with multiple columns. EG:

"SELECT DateAndTime
  Field0 = MAX(CASE WHEN TagIndex='0' THEN Val END)
  Field1 = MAX(CASE WHEN TagIndex='1' THEN Val END)
  Field2 = MAX(CASE WHEN TagIndex='2' THEN Val END)
FROM [Mixplant1Float] GROUP BY DateAndTime ORDER BY DateAndTime DESC", conn

This works well and returns:

18/03/2009 3:11:09 PM | 405650 | 405652 | 32.29
18/03/2009 3:27:25 PM | 405650 | 405652 | 32.5
18/03/2009 3:27:28 PM | 405650 | 405652 | 32.5

Where I am having trouble is in how to use a WHERE statement to filter the results. It is easy for me to filter by DateAndTime as this field is common to all of the records in the database. But what I need is to able to also filter by [Val]. So in the above example I want to only create a recordset row when the [Val] column is 32.5. So I would end up with:

18/03/2009 3:27:25 PM | 405650 | 405652 | 32.5
18/03/2009 3:27:28 PM | 405650 | 405652 | 32.5

I can't seem to get my head around it. If someone could point me in the right direction that would be greatly appreciated!

EDIT: Oh and hats off to this thread: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107658[/url] which helped me with the SELECT CASE statement.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-18 : 23:30:25

Use Having Clause

"SELECT DateAndTime
Field0 = MAX(CASE WHEN TagIndex='0' THEN Val END)
Field1 = MAX(CASE WHEN TagIndex='1' THEN Val END)
Field2 = MAX(CASE WHEN TagIndex='2' THEN Val END)
FROM [Mixplant1Float] GROUP BY DateAndTime
Having val=32.5
ORDER BY DateAndTime DESC", conn

Regards

Senthil.C
----------------------------------------------------------------------------
Server: Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

BennyBCE
Starting Member

3 Posts

Posted - 2009-03-19 : 17:38:34

Hi senthil,

thanks for your reply. How does the HAVING clause differentiate which VAL I am refering to? Would that return:

18/03/2009 3:27:25 PM | 32.5
18/03/2009 3:27:28 PM | 32.5

only?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 14:03:58

No. That solution provided is wrong.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 14:08:51

You just need this,

SELECT DateAndTime,
  Field0 = MAX(CASE WHEN TagIndex='0' THEN Val END),
  Field1 = MAX(CASE WHEN TagIndex='1' THEN Val END),
  Field2 = MAX(CASE WHEN TagIndex='2' THEN Val END)
FROM [Mixplant1Float] 
WHERE val=32.5
GROUP BY DateAndTime 
ORDER BY DateAndTime DESC

Having is filter on an aggregated column. It will only behave like 'where' when there is no 'group by' clause.

I couldn't make sense of this ..

quote:

It is easy for me to filter by DateAndTime as this field is common to all of the records in the

You can very well filter on Val field or any other field in the table.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-03-27 : 03:00:09

I dont think both the having clause & where clause will help.

set dateformat dmy
declare @tbl table(dt datetime,tagindex int,val float)
insert into @tbl
select
'18/03/2009 3:11:09 PM',0 , 405650 union all
select'18/03/2009 3:11:09 PM',1 , 405652 union all
select'18/03/2009 3:11:09 PM',2 , 32.29 union all
select'18/03/2009 3:27:25 PM',0 , 405650 union all
select'18/03/2009 3:27:25 PM',1 , 405652 union all
select'18/03/2009 3:27:25 PM',2 , 32.5 union all
select'18/03/2009 3:27:28 PM',0 , 405650 union all
select'18/03/2009 3:27:28 PM',1 , 405652 union all
select'18/03/2009 3:27:28 PM',2 , 32.5

select * from
(
SELECT dt,
Field0 = MAX(CASE WHEN TagIndex='0' THEN val END),
Field1 = MAX(CASE WHEN TagIndex='1' THEN Val END),
Field2 = MAX(CASE WHEN TagIndex='2' THEN Val END)
FROM @tbl GROUP BY dt
)T
where Field2=32.5 ORDER BY dt DESC

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-27 : 04:44:38

quote:
Originally posted by ayamas

I dont think both the having clause & where clause will help.

set dateformat dmy
declare @tbl table(dt datetime,tagindex int,val float)
insert into @tbl
select
'18/03/2009 3:11:09 PM',0 , 405650 union all
select'18/03/2009 3:11:09 PM',1 , 405652 union all
select'18/03/2009 3:11:09 PM',2 , 32.29 union all
select'18/03/2009 3:27:25 PM',0 , 405650 union all
select'18/03/2009 3:27:25 PM',1 , 405652 union all
select'18/03/2009 3:27:25 PM',2 , 32.5 union all
select'18/03/2009 3:27:28 PM',0 , 405650 union all
select'18/03/2009 3:27:28 PM',1 , 405652 union all
select'18/03/2009 3:27:28 PM',2 , 32.5

select * from
(
SELECT dt,
Field0 = MAX(CASE WHEN TagIndex='0' THEN val END),
Field1 = MAX(CASE WHEN TagIndex='1' THEN Val END),
Field2 = MAX(CASE WHEN TagIndex='2' THEN Val END)
FROM @tbl GROUP BY dt
)T
where Field2=32.5 ORDER BY dt DESC

What you are doing is the same as what one could have using 'having' . OP doesn't need filter on aggregated column. Have a look at the sample he has posted.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-27 : 04:50:53

[code]SELECT DateAndTime
MAX(CASE WHEN TagIndex = 0 THEN Val END) AS Field0,
MAX(CASE WHEN TagIndex = 1 THEN Val END) AS Field1,
MAX(CASE WHEN TagIndex = 2 THEN Val END) AS Field2
FROM Mixplant1Float
GROUP BY DateAndTime
HAVING MAX(CASE WHEN TagIndex = 2 THEN Val END) = 32.5
ORDER BY DateAndTime DESC[/code]

E 12°55'05.63"
N 56°04'39.26"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-03-27 : 05:14:05

quote:
Originally posted by sakets_2000

quote:
Originally posted by ayamas

I dont think both the having clause & where clause will help.

set dateformat dmy
declare @tbl table(dt datetime,tagindex int,val float)
insert into @tbl
select
'18/03/2009 3:11:09 PM',0 , 405650 union all
select'18/03/2009 3:11:09 PM',1 , 405652 union all
select'18/03/2009 3:11:09 PM',2 , 32.29 union all
select'18/03/2009 3:27:25 PM',0 , 405650 union all
select'18/03/2009 3:27:25 PM',1 , 405652 union all
select'18/03/2009 3:27:25 PM',2 , 32.5 union all
select'18/03/2009 3:27:28 PM',0 , 405650 union all
select'18/03/2009 3:27:28 PM',1 , 405652 union all
select'18/03/2009 3:27:28 PM',2 , 32.5

select * from
(
SELECT dt,
Field0 = MAX(CASE WHEN TagIndex='0' THEN val END),
Field1 = MAX(CASE WHEN TagIndex='1' THEN Val END),
Field2 = MAX(CASE WHEN TagIndex='2' THEN Val END)
FROM @tbl GROUP BY dt
)T
where Field2=32.5 ORDER BY dt DESC

What you are doing is the same as what one could have using 'having' . OP doesn't need filter on aggregated column. Have a look at the sample he has posted.

Please check the resultset you get after using the where clause the way u suggested.

set dateformat dmy
declare @tbl table(dt datetime,tagindex int,val float)
insert into @tbl
select
'18/03/2009 3:11:09 PM',0 , 405650 union all
select'18/03/2009 3:11:09 PM',1 , 405652 union all
select'18/03/2009 3:11:09 PM',2 , 32.29 union all
select'18/03/2009 3:27:25 PM',0 , 405650 union all
select'18/03/2009 3:27:25 PM',1 , 405652 union all
select'18/03/2009 3:27:25 PM',2 , 32.5 union all
select'18/03/2009 3:27:28 PM',0 , 405650 union all
select'18/03/2009 3:27:28 PM',1 , 405652 union all
select'18/03/2009 3:27:28 PM',2 , 32.5

SELECT dt,
Field0 = MAX(CASE WHEN TagIndex='0' THEN val END),
Field1 = MAX(CASE WHEN TagIndex='1' THEN Val END),
Field2 = MAX(CASE WHEN TagIndex='2' THEN Val END)
FROM @tbl
WHERE val=32.5
GROUP BY dt

BennyBCE
Starting Member

3 Posts

Posted - 2009-03-29 : 17:49:02

Hi all and thanks for the replies and suggestions. Peso's solution worked a treat for me. Thanks mate

quote:

Originally posted by Peso

SELECT		DateAndTime
		MAX(CASE WHEN TagIndex = 0 THEN Val END) AS Field0,
		MAX(CASE WHEN TagIndex = 1 THEN Val END) AS Field1,
		MAX(CASE WHEN TagIndex = 2 THEN Val END) AS Field2
FROM		Mixplant1Float
GROUP BY	DateAndTime
HAVING		MAX(CASE WHEN TagIndex = 2 THEN Val END) = 32.5
ORDER BY	DateAndTime DESC

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources