CODEWITHSUNDEEP
sqlsql-servert-sqljoincross-apply
Jimfletcha
Jimfletcha

Reputation: 81

Using both CROSS APPLY and INNER JOIN in the same query

I'm attempting to use both Left Join and Cross Apply in the same query and running into difficulties.

SELECT vAH.TagName, vAH.EventSTamp, -123 Value, vAH.Description, 
--  Ack.DateTime, Ack.UserFullName as AckUser, Ack.Description as AckComment, 
LEFT(vAH.TagName,9) + CONVERT(nvarchar(30),LLC.StartDateTime,113)  as ObjName
FROM WWALMDBArchived.dbo.v_AlarmHistory vAH
--CROSS APPLY (
--  SELECT TOP 1 EventStamp as DateTime, UserFullName, Description
--  FROM WWALMDBArchived.dbo.v_AlarmHistory vAH
--  WHERE TagName = vAH.TagName
--  AND EventStamp > vAH.EventStamp
--  AND AlarmState IN ('ACK_RTN','ACK_ALM')
--  ORDER BY DateTime, UserFullName, Description DESC
--  ) Ack
INNER JOIN CPMS.dbo.LotListConfig LLC
ON vAH.EventStamp >= LLC.StartDateTime
AND vAH.EventStamp <= LLC.EndDateTime
WHERE vAH.TagName LIKE @LineNumber + '%.Action_Alarm_ALM'
AND LLC.LineNumber = @LineNumber
AND LLC.LotNumber = @LotNumber
AND vAH.AlarmState = 'UNACK_ALM'

Essentially what I am doing is getting the boundary information from the LotListConfig table, getting the initial alarm information from v_AlarmHistory, and using the Cross Apply to get some subsequent alarm information from the v_AlarmHistory table.

The query above returns the records I would expect, but uncommenting the Cross Apply causes no records to return. There's an interaction of some kind happening between the Inner Join and the Cross Apply that I'm missing.

Anyone?

Upvotes: 0

Views: 3639

Answers (1)

Jimfletcha
Jimfletcha

Reputation: 81

Nevermind.

My query above is using the same table shortcut (vAH) in both the main query and the CROSS APPLY query. Deleting the vAH inside the Cross Apply resolves the issue.

Upvotes: 1

Related Questions