SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Query help: Select/Case statements
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

becksinthecity
Starting Member

18 Posts

Posted - 06/13/2005 :  16:04:43  Show Profile  Reply with Quote
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

Edited by - becksinthecity on 06/13/2005 16:15:31

X002548
Not Just a Number

15586 Posts

Posted - 06/13/2005 :  16:18:05  Show Profile  Reply with Quote
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
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/13/2005 :  16:18:26  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/13/2005 :  16:19:51  Show Profile  Reply with Quote
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
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/13/2005 :  16:22:54  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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.
Go to Top of Page

becksinthecity
Starting Member

18 Posts

Posted - 06/13/2005 :  16:28:40  Show Profile  Reply with Quote
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




Edited by - becksinthecity on 06/13/2005 17:16:27
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/13/2005 :  16:34:19  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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.

Edited by - Seventhnight on 06/13/2005 16:36:34
Go to Top of Page

becksinthecity
Starting Member

18 Posts

Posted - 06/13/2005 :  16:38:52  Show Profile  Reply with Quote
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
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/13/2005 :  16:40:17  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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.
Go to Top of Page

becksinthecity
Starting Member

18 Posts

Posted - 06/13/2005 :  16:41:36  Show Profile  Reply with Quote
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

Edited by - becksinthecity on 06/13/2005 17:16:57
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/13/2005 :  16:42:46  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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.
Go to Top of Page

becksinthecity
Starting Member

18 Posts

Posted - 06/13/2005 :  16:43:57  Show Profile  Reply with Quote
excellent. i knew i was close. thanks so much.
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/13/2005 :  16:44:17  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
no prob.

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000