Use Correlation Names??? 1

[tqeonline]

Hello all - just trying to do a simple left outer join and it is telling me to use correlation names... what am i missing?

declare @APP varchar(25)
set @APP = 'dax'

SELECT  BG_BUG_ID as 'Defect #', 
	BG_USER_11 as 'User Story', 
	BG_DETECTION_DATE as 'Detection Date',
	DateDiff(Day, SUBMITTED.MIN_AU_TIME,CLOSED.MIN_AU_TIME) as 'Days Open'
          
from BUG with(nolock), RELEASE_CYCLES with(nolock),

	BUG LEFT OUTER JOIN
		  (SELECT AU_ENTITY_ID, MIN(AU_TIME)AS MIN_AU_TIME
		  FROM AUDIT_LOG WITH (NOLOCK), AUDIT_PROPERTIES WITH (NOLOCK)
		  WHERE AU_ENTITY_TYPE = 'BUG'
		  AND AP_FIELD_NAME = 'BG_STATUS'
		  AND AU_ACTION_ID = AP_ACTION_ID
		  AND AP_NEW_VALUE = 'Submitted'
		  GROUP BY AU_ENTITY_ID) AS SUBMITTED
	ON BUG.BG_BUG_ID = SUBMITTED.AU_ENTITY_ID,

	BUG LEFT OUTER JOIN
		  (SELECT AU_ENTITY_ID, MIN(AU_TIME)AS MIN_AU_TIME
		  FROM AUDIT_LOG WITH (NOLOCK), AUDIT_PROPERTIES WITH (NOLOCK)
		  WHERE AU_ENTITY_TYPE = 'BUG'
		  AND AP_FIELD_NAME = 'BG_STATUS'
		  AND AU_ACTION_ID = AP_ACTION_ID
		  AND AP_NEW_VALUE = 'Closed'
		  GROUP BY AU_ENTITY_ID) AS CLOSED
	ON BUG.BG_BUG_ID = CLOSED.AU_ENTITY_ID

where BG_USER_11 is not NULL 
  AND BG_DETECTED_IN_RCYC = RCYC_ID
  And BUG.BG_BUG_ID = AU_ENTITY_ID
  AND AU_ACTION_ID = AP_ACTION_ID
  and BG_USER_11 <> ' '
  and BG_USER_11 <> 'Invalid'
  and BG_USER_02 like @APP
  and BG_DETECTION_DATE > '11-01-2010'
  And (RCYC_USER_01 LIKE 'Performance' OR RCYC_USER_01 LIKE 'Regression' OR RCYC_USER_01 LIKE 'Security' OR RCYC_USER_01 LIKE 'Smoke' OR RCYC_USER_01 LIKE 'System')
  And BG_USER_14 like 'Defect'
  AND BG_STATUS <> 'Invalid'
  AND AU_ENTITY_TYPE = 'BUG'
  AND AP_FIELD_NAME = 'BG_STATUS'
  AND AP_NEW_VALUE = 'Submitted'
  
  order by BG_USER_11

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[Qik3Coder]

You can't mix and match join styles.


Either they are all:
FROM tableA, tableB, tableC

Or all:

FROM tableA a
Join tableC c on
[tab]a.colA = c.ColA
LEFT JOIN tableB b on
[tab]a.colb = b.colb


Lodlaiden


I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[tqeonline]

You were right. I ended up finding another query that did what you were saying.

Thanks!

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008