Query help: Select/Case statements

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)

Query help: Select/Case statements

Author

Topic

becksinthecity
Starting Member

18 Posts

Posted - 2005-06-13 : 16:04:43

This is my current query. However it does not properly run. All i need it to do is to have the attribute value show up in a different column based on the value of Attributename. Howver, i do not have permission to create views, temporary tables or permissions, i can only do this as a select statement. This was my best guess, but i can only make this work for one case statement. any help would be appreciated. Thanks
_____________________________________________________

select a.SensordataID,
(case when a.AttributeName ='Target Account Name' then a.AttributeValue else null end) as TargetAcct
(case when a.AttributeName ='Caller Machine Name' then a.AttributeValue else null end) as CallerMachine
(case when a.AttributeName ='Caller User Name' then a.AttributeValue else null end) as CallerUserName
(case when a.AttributeName ='Caller Domain' then a.AttributeValue else null end) as CallerDomain

from SensorData s, SensorDataAVP a
where datediff(day,s.AlertDateTime,getdate()) < 1
and s.AlertName = 'User_Account_Locked_Out'
and s.SensorDataID = a.SensorDataID

__________________________________________________________

so the results should have columns:

SensorDataID, TargetAcct, CallerMachine, CallerUserName, CallerDomain

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-13 : 16:18:05

Hey becks....follow the link below for instructions...we need some more info from you

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-13 : 16:18:26

quote:
Originally posted by becksinthecity

This is my current query. However it does not properly run. All i need it to do is to have the attribute value show up in a different column based on the value of Attributename. Howver, i do not have permission to create views, temporary tables or permissions, i can only do this as a select statement. This was my best guess, but i can only make this work for one case statement. any help would be appreciated. Thanks
_____________________________________________________

select a.SensordataID,
(case when a.AttributeName ='Target Account Name' then a.AttributeValue else null end) as TargetAcct,
(case when a.AttributeName ='Caller Machine Name' then a.AttributeValue else null end) as CallerMachine,

(case when a.AttributeName ='Caller User Name' then a.AttributeValue else null end) as CallerUserName,

(case when a.AttributeName ='Caller Domain' then a.AttributeValue else null end) as CallerDomain,

from SensorData s, SensorDataAVP a
where datediff(day,s.AlertDateTime,getdate()) < 1
and s.AlertName = 'User_Account_Locked_Out'
and s.SensorDataID = a.SensorDataID

__________________________________________________________

so the results should have columns:

SensorDataID, TargetAcct, CallerMachine, CallerUserName, CallerDomain

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-13 : 16:19:51

You forget something Corey?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-13 : 16:22:54

no he did... my comments are in Red... maybe some commas?? yes??

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

becksinthecity
Starting Member

18 Posts

Posted - 2005-06-13 : 16:28:40

Tables
SensorData:
SensordataID(bigint, not null)

SensorDataAVP:
SensordataID(bigint, not null)
Attributename(nvarchar(50, not null)
attributeValue(nvarchar(2000), not null)

_______________

select a.SensordataID,a.AttributeValue,a.AttributeName
from SensorData s, SensorDataAVP a where s.AlertName = 'User_Account_Locked_Out'
and s.AlertDateTime > '2005-06-12 0:0' and s.SensorDataID = a.SensorDataID
and a.AttributeName in ('Target Account Name', 'Caller Machine Name', 'Caller User Name', 'Caller Domain' )

Produces:
SensordataID - AttributeValue - AttributeName

Each id number in this query has 4 values from attribute value.
i would like it to look like:

SensordataID-TargetAcct-CallerMachine-CallerUserName-CallerDomain

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-13 : 16:34:19

okay... then add the commas and a group by:


Select 
	a.SensordataID,
	max(case when a.AttributeName ='Target Account Name' then a.AttributeValue else null end) as TargetAcct, 
	max(case when a.AttributeName ='Caller Machine Name' then a.AttributeValue else null end) as CallerMachine, 
	max(case when a.AttributeName ='Caller User Name' then a.AttributeValue else null end) as CallerUserName, 
	max(case when a.AttributeName ='Caller Domain' then a.AttributeValue else null end) as CallerDomain
from SensorData s, SensorDataAVP a 
where datediff(day,s.AlertDateTime,getdate()) < 1
and s.AlertName = 'User_Account_Locked_Out' 
and s.SensorDataID = a.SensorDataID
Group By a.SensordataID

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

becksinthecity
Starting Member

18 Posts

Posted - 2005-06-13 : 16:38:52

select a.SensordataID,
(case when a.AttributeName ='Target Account Name' then a.AttributeValue else null end) as TargetAcct,
(case when a.AttributeName ='Caller Machine Name' then a.AttributeValue else null end) as CallerMachine,
(case when a.AttributeName ='Caller User Name' then a.AttributeValue else null end) as CallerUserName,
(case when a.AttributeName ='Caller Domain' then a.AttributeValue else null end) as CallerDomain

from SensorData s, SensorDataAVP a
where datediff(day,s.AlertDateTime,getdate()) < 1
and s.AlertName = 'User_Account_Locked_Out'
and s.SensorDataID = a.SensorDataID
Group By a.SensordataID, a.AttributeName, a.AttributeValue

that helps some, however, the data only populates one row now. i'd like all the data to appear. It did not like the group by at the end of each case statement.

SensordataID-TargetAcct-CallerMachine-CallerUserName-CallerDomain

3066955 NULL NULL NULL NULL
3066955 NULL LOCSDW66663 NULL NULL

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-13 : 16:40:17

try the updated query

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

becksinthecity
Starting Member

18 Posts

Posted - 2005-06-13 : 16:41:36

select a.SensordataID,
max(case when a.AttributeName ='Target Account Name' then a.AttributeValue else null end) as TargetAcct,
max(case when a.AttributeName ='Caller Machine Name' then a.AttributeValue else null end) as CallerMachine,
max(case when a.AttributeName ='Caller User Name' then a.AttributeValue else null end) as CallerUserName,
max(case when a.AttributeName ='Caller Domain' then a.AttributeValue else null end) as CallerDomain

from SensorData s, SensorDataAVP a
where datediff(day,s.AlertDateTime,getdate()) < 1
and s.AlertName = 'User_Account_Locked_Out'
and s.SensorDataID = a.SensorDataID
Group By a.SensordataID, a.AttributeName, a.AttributeValue

________________________
with the max's included, i get some rows will all nulls...

1198609 NULL NULL NULL ***
1198609 NULL NULL NULL NULL

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-13 : 16:42:46

don't group by everything... just group by what is in red.

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

becksinthecity
Starting Member

18 Posts

Posted - 2005-06-13 : 16:43:57

excellent. i knew i was close. thanks so much.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-13 : 16:44:17