Case statement and Inner Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Case statement and Inner Join

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-08-17 : 16:32:30

This is for a search and it is bringing back too many counts for numVerification0. The INNER JOIN must be in the Case for this to work, not like it is in the other sproc.

SELECT a.ImportAlertID, c.Name, a.ProcessDate, 


CASE 

WHEN (@type= 4 THEN		
	(SELECT COUNT(*) 
	FROM invoicenotadvanced ina
	INNER JOIN ImportAlertTrackingNumbers itn 
			ON a.ImportAlertID = itn.ImportAlertID
	WHERE VerificationStatus = 0 
	AND ina.ImportAlertID = itn.InvImportAlertID)
END AS numVerification0

FROM ImportAlert a
INNER JOIN ImportType b ON a.ImportTypeID = b.ImportTypeID
INNER JOIN Client c ON a.ClientID = c.pk_id

There are a few more selects and Case statement but this is the short version.

There's another page that uses a different sproc and it works:


IF @type = 4
BEGIN

SELECT DISTINCT a.ImportAlertID, c.Name, a.ProcessDate,


(SELECT COUNT(*) 
FROM invoicenotadvanced WHERE VerificationStatus = 0 
AND ImportAlertID = itn.InvImportAlertID) AS numVerification0,


FROM ImportAlert a
INNER JOIN ImportType b ON a.ImportTypeID = b.ImportTypeID
INNER JOIN Client c ON a.ClientID = c.pk_id

INNER JOIN ImportAlertTrackingNumbers itn 
ON a.ImportAlertID = itn.ImportAlertID

INNER JOIN invoicenotadvanced inv 
ON inv.ImportAlertID = itn.InvImportAlertID

Any suggestions?

Thanks,

Zath

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-17 : 23:44:16

[code]SELECT a.ImportAlertID,
c.Name,
a.ProcessDate,
CASE WHEN @type= 4 THEN (SELECT COUNT(*)
FROM invoicenotadvanced ina
INNER JOIN ImportAlertTrackingNumbers itn ON a.ImportAlertID = itn.ImportAlertID
WHERE VerificationStatus = 0 AND
ina.ImportAlertID = itn.InvImportAlertID)
END AS numVerification0
FROM ImportAlert a
INNER JOIN ImportType b ON a.ImportTypeID = b.ImportTypeID
INNER JOIN Client c ON a.ClientID = c.pk_id[/code]
note. you add another '(' after when.

Hope can help...but advise to wait pros with confirmation...

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-08-18 : 08:30:50

Actually, that was a typo.
To simplify the case statement, I edited it.
This is the actual one that is bringing back 8x the count...

CASE 
WHEN (SELECT ImportTypeID FROM ImportAlert 
		WHERE  a.ImportAlertID = ImportAlertID) = 4 THEN		
	(SELECT COUNT(*) 
	FROM invoicenotadvanced ina
	INNER JOIN ImportAlertTrackingNumbers itn 
			ON a.ImportAlertID = itn.ImportAlertID
	WHERE VerificationStatus = 0 
	AND ina.ImportAlertID = itn.InvImportAlertID)
END AS numVerification0,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources