WHERE EXISTS + UNION? 1

[tqeonline]

I'm trying to do a Where Exists that has a union at the bottom to include a special result.

I'm working with a column named "REL_ID" and i wanted to union a "REL_ID" = 999999

I tried doing a Union but it just returned all the results... as if it were ignoring the WHERE EXIST

So I change it to this:

WHERE (EXISTS (SELECT
            REL_ID
            FROM
            RELEASES INNER JOIN RELEASE_CYCLES WITH (NOLOCK)
            ON RELEASES.REL_ID = RELEASE_CYCLES.RCYC_PARENT_ID

            LEFT OUTER JOIN (SELECT TC_ASSIGN_RCYC, COUNT(*) AS CNT
                FROM TESTCYCL WITH (NOLOCK)
                WHERE TC_STATUS <> 'N/A'
                GROUP BY TC_ASSIGN_RCYC) AS CYCLE_SCRIPT_COUNT
            ON RELEASE_CYCLES.RCYC_ID = CYCLE_SCRIPT_COUNT.TC_ASSIGN_RCYC

            LEFT OUTER JOIN (SELECT TC_ASSIGN_RCYC, COUNT(Case When TC_STATUS = 'Passed' Then 1 End) AS CNT
                FROM TESTCYCL WITH (NOLOCK)          
                GROUP BY TC_ASSIGN_RCYC) AS CYCLE_PASSED_COUNT      
            ON RELEASE_CYCLES.RCYC_ID = CYCLE_PASSED_COUNT.TC_ASSIGN_RCYC

            LEFT OUTER JOIN (SELECT BG_DETECTED_IN_RCYC, COUNT(*) AS CNT FROM BUG WITH (NOLOCK)
                WHERE (BUG.BG_STATUS <> 'Invalid' 
                AND BUG.BG_STATUS <> 'Closed' 
                AND BUG.BG_STATUS <> 'New' 
                AND BUG.BG_STATUS <> 'Deferred')
                GROUP BY BG_DETECTED_IN_RCYC) AS BUG
            ON BUG.BG_DETECTED_IN_RCYC = RCYC_ID

            WHERE REL_ID = SQA_DASH_Report_Email.REL_ID
            AND REL_USER_02 > '11/21/2010'
            AND (REL_USER_02 >= DATEADD(DAY, -3, GetDate()) OR
            CYCLE_PASSED_COUNT.CNT < CYCLE_SCRIPT_COUNT.CNT)
            GROUP BY [REL_ID]
            )
        OR REL_ID = 999999)

What it was... was:

WHERE REL_USER_02 > '11/21/2010'
            AND (REL_USER_02 >= DATEADD(DAY, -3, GetDate()) OR
            CYCLE_PASSED_COUNT.CNT < CYCLE_SCRIPT_COUNT.CNT)
            GROUP BY [REL_ID]
            UNION
            Select 999999 as [REL_ID])

Is there an easier way to union the 999999 that doesn't make the query 5 seconds longer?

- 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

 

[MarkXaml]

I would try using a stored procedure and possibly this method:

DECLARE @tblValidReleases TABLE (REL_ID int)
INSERT INTO		@tblValidReleases
SELECT          REL_ID            
FROM            RELEASES 
INNER JOIN		RELEASE_CYCLES WITH (NOLOCK)            
ON				RELEASES.REL_ID = RELEASE_CYCLES.RCYC_PARENT_ID            
LEFT OUTER JOIN (SELECT TC_ASSIGN_RCYC, COUNT(*) AS CNT                
				FROM TESTCYCL WITH (NOLOCK)                
				WHERE TC_STATUS <> 'N/A'                
				GROUP BY TC_ASSIGN_RCYC) AS CYCLE_SCRIPT_COUNT            
ON				RELEASE_CYCLES.RCYC_ID = CYCLE_SCRIPT_COUNT.TC_ASSIGN_RCYC            
LEFT OUTER JOIN (SELECT TC_ASSIGN_RCYC, COUNT(Case When TC_STATUS = 'Passed' Then 1 End) AS CNT                
				FROM TESTCYCL WITH (NOLOCK)                          
				GROUP BY TC_ASSIGN_RCYC) AS CYCLE_PASSED_COUNT                  
ON RELEASE_CYCLES.RCYC_ID = CYCLE_PASSED_COUNT.TC_ASSIGN_RCYC            
LEFT OUTER JOIN (SELECT BG_DETECTED_IN_RCYC, COUNT(*) AS CNT 
				FROM BUG WITH (NOLOCK)                
				WHERE (BUG.BG_STATUS <> 'Invalid'                 
				AND BUG.BG_STATUS <> 'Closed'                 
				AND BUG.BG_STATUS <> 'New'                 
				AND BUG.BG_STATUS <> 'Deferred')                
				GROUP BY BG_DETECTED_IN_RCYC) AS BUG            
ON				BUG.BG_DETECTED_IN_RCYC = RCYC_ID            
WHERE			REL_ID = SQA_DASH_Report_Email.REL_ID            
AND				REL_USER_02 > '11/21/2010'            
AND				(REL_USER_02 >= DATEADD(DAY, -3, GetDate()) OR            
				CYCLE_PASSED_COUNT.CNT < CYCLE_SCRIPT_COUNT.CNT)            
GROUP BY [REL_ID]

-- add the special rel_id
INSERT INTO		@tblValidReleases VALUES (999999)

--THEN use the table variable in your query
SELECT		[col1],[col2], ...
FROM		[table] t (NOLOCK)
INNER JOIN	@tblValidReleases vr
ON			t.REL_ID = vr.REL_ID

the inner join will act as your EXISTS statement. The table variable just holds a list of valid rel_ids plus the special 999999.

disclaimer: the select statement into the table variable is not changed from the original post... I cannot verify that it is appropriate or works.

good luck!

Mark

 

[tqeonline]

after a little tweaking I got it to work perfectly!

I had thought about a temp table before and just couldn't wrap my mind around how to implement it.

Here is the finished product:

DECLARE @tblValidReleases TABLE (REL_ID int)
            INSERT INTO @tblValidReleases
            SELECT REL_ID            
            FROM RELEASES 

            INNER JOIN RELEASE_CYCLES WITH (NOLOCK)            
            ON RELEASES.REL_ID = RELEASE_CYCLES.RCYC_PARENT_ID      
      
            LEFT OUTER JOIN (SELECT TC_ASSIGN_RCYC, COUNT(*) AS CNT                                
	            FROM TESTCYCL WITH (NOLOCK)                                
	            WHERE TC_STATUS <> 'N/A'                                
	            GROUP BY TC_ASSIGN_RCYC) AS CYCLE_SCRIPT_COUNT            
            ON  RELEASE_CYCLES.RCYC_ID = CYCLE_SCRIPT_COUNT.TC_ASSIGN_RCYC       
     
            LEFT OUTER JOIN (SELECT TC_ASSIGN_RCYC, COUNT(Case When TC_STATUS = 'Passed' Then 1 End) AS CNT                                
	            FROM TESTCYCL WITH (NOLOCK)                                          
	            GROUP BY TC_ASSIGN_RCYC) AS CYCLE_PASSED_COUNT                  
            ON RELEASE_CYCLES.RCYC_ID = CYCLE_PASSED_COUNT.TC_ASSIGN_RCYC            

            LEFT OUTER JOIN (SELECT BG_DETECTED_IN_RCYC, COUNT(*) AS CNT                 
	            FROM BUG WITH (NOLOCK)                                
	            WHERE (BUG.BG_STATUS <> 'Invalid'                                 
		            AND BUG.BG_STATUS <> 'Closed'                                 
		            AND BUG.BG_STATUS <> 'New'                                 
		            AND BUG.BG_STATUS <> 'Deferred')                                
	            GROUP BY BG_DETECTED_IN_RCYC) AS BUG            
            ON BUG.BG_DETECTED_IN_RCYC = RCYC_ID        
    
            WHERE REL_USER_02 > '11/21/2010'            
	            AND (REL_USER_02 >= DATEADD(DAY, -3, GetDate()) 
	            OR CYCLE_PASSED_COUNT.CNT < CYCLE_SCRIPT_COUNT.CNT)            
            GROUP BY [REL_ID]

            -- add the special rel_id
            INSERT INTO @tblValidReleases VALUES (999999)

            --THEN use the table variable in your query
            SELECT [ID]
                    ,[EMAIL] as 'E-mail'      
                    ,[REL_NAME] as 'Release Name'
                    ,t.[REL_ID]
                    ,isnull([Env],'') as 'Env'
                    FROM SQA_DASH_Report_Email t

                    Left Outer Join (Select [REL_NAME], [REL_ID],[REL_USER_09] as 'Env' FROM RELEASES
		            UNION
	                Select 'SQA Daily Project Summary' as [REL_NAME], 999999 as [REL_ID], '' as [Env]) as RELEASES
                    ON RELEASES.REL_ID = t.REL_ID
        
            INNER JOIN @tblValidReleases vr
            ON t.REL_ID = vr.REL_ID
            
            WHERE [EMAIL] = @Email
            ORDER BY [REL_NAME]

- 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