Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filtering multiple alias for single column in sql Expand / Collapse
Author
Message
Posted Monday, February 17, 2014 8:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 17, 2014 8:37 PM
Points: 2, Visits: 2
My query is as below which works perfectly fine:

SELECT
[le].[id] AS [eventId]
,MAX(Convert (varchar(10), [le].[eventCreationTime], 104)) AS [Swipe Date]
,MAX(Convert (varchar(10), [le].[eventCreationTime], 108)) AS [Swipe Time]
,MAX(Replace(Replace([la].[addressTag], 'AccessEngine.Devices.',''),'.Event','')) AS [Reader Name]
,MAX([ls].[stateNumber]) AS [stateNumber]
,MAX([ls].[stateName]) AS [state Name]
,[First Name] = MAX(CASE WHEN [levt].[eventValueName] = 'FIRSTNAME' THEN [lev].[stringValue] END)
,Name = MAX(CASE WHEN [levt].[eventValueName] = 'NAME' THEN [lev].[stringValue] END)
,[Card NO] = MAX(CASE WHEN [levt].[eventValueName] = 'CARDNO' THEN [lev].[stringValue] END)
,[Person NO] = MAX(CASE WHEN [levt].[eventValueName] = 'PERSNO' THEN [lev].[stringValue] END)
,Department = MAX(CASE WHEN [levt].[eventValueName] = 'DEPARTMENT' THEN [lev].[stringValue] END)
FROM LogDetectorType AS ldt INNER JOIN
LogEvent AS le LEFT OUTER JOIN
LogState AS ls ON le.stateId = ls.Id INNER JOIN
LogAddress AS la ON le.addressId = la.ID ON ldt.ID = la.detectorTypeId LEFT OUTER JOIN
LogEvent2Value AS le2v ON le.ID = le2v.eventId LEFT OUTER JOIN
LogEventValueType AS levt INNER JOIN
LogEventValue AS lev ON levt.ID = lev.eventTypeId ON le2v.valueId = lev.Id
WHERE (stateName IN(@StateName))
AND ([le].[eventCreationTime] BETWEEN (@From) AND (@To))
AND (Replace(Replace([la].[addressTag], 'AccessEngine.Devices.',''),'.Event','') IN (@Reader))
GROUP BY le.ID, [le].[eventCreationTime]
ORDER BY [le].[eventCreationTime] DESC, MAX([le].[id])

I need to add a new AND condition for filter anAlias column [First Name]. When i do this, other alias columns like [Card NO]; [Name]; [Pers NO] are shown with blank results.

Kindly help me in filtering this.

Thanks in advance.
Post #1542336
Posted Monday, February 17, 2014 8:26 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 18,064, Visits: 16,099
If you want to filter for First Name, then you need to use [lev].[stringValue] in your where clause.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1542338
Posted Monday, February 17, 2014 8:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 17, 2014 8:37 PM
Points: 2, Visits: 2
If i use stringvalue, other alias columns will be NULL or will not show any results.
Post #1542345
Posted Monday, February 17, 2014 8:43 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 18,064, Visits: 16,099
What is your goal in trying to filter by somebody's First name?

Do you have sample data and table definitions to help solve this problem you are running into?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1542347
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse