Select when parm = 1 - 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)

Select when parm = 1

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-16 : 13:53:35

I know there must be a way to achieve this, so, here we are again :D

Need to change the select according to what the param is...

SELECT a.ImportAlertID, a.ClientID,
		
	--IF @type = 1 then use this select
	(SELECT COUNT(*) 
		FROM invoicenotadvanced WHERE NAStatus = 0 
		AND a.ImportAlertID = ImportAlertID) AS numNA0,

	--IF @type = 2 then use this select
	(SELECT COUNT(*) 
		FROM customer WHERE NAStatus = 'Not Sent' 
		AND a.ImportAlertID = ImportAlertID) AS numNA0,

	a.CompleteStatus, a.CompletedBy		

FROM ImportAlert a


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

INNER JOIN (SELECT ImportAlertID, COUNT(*) AS numCustomers
	FROM customer GROUP BY ImportAlertID) cust ON a.ImportAlertID = cust.ImportAlertID

INNER JOIN (SELECT ImportAlertID, COUNT(*) AS numInvoices, SUM(invorigamt) AS sumInvoices					 
	FROM dbo.invoicenotadvanced 
	GROUP BY ImportAlertID) inv 
	ON a.ImportAlertID = inv.ImportAlertID

How can this be done?
A case statement won't work here.

Thanks,

Zath

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 14:03:58

[code]SELECT a.ImportAlertID, a.ClientID,

CASE @type WHEN 1 THEN tmp1.cnt1 WHEN 2 THEN tmp2.cnt2 END AS numNA0,

a.CompleteStatus, a.CompletedBy

FROM ImportAlert a

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

INNER JOIN (SELECT ImportAlertID, COUNT(*) AS numCustomers
FROM customer GROUP BY ImportAlertID) cust ON a.ImportAlertID = cust.ImportAlertID

INNER JOIN (SELECT ImportAlertID, COUNT(*) AS numInvoices, SUM(invorigamt) AS sumInvoices
FROM dbo.invoicenotadvanced
GROUP BY ImportAlertID) inv
ON a.ImportAlertID = inv.ImportAlertID
OUTER APPLY --IF @type = 1 then use this select
(SELECT COUNT(*) AS cnt1
FROM invoicenotadvanced WHERE NAStatus = 0
AND a.ImportAlertID = ImportAlertID)tmp1
OUTER APPLY (SELECT COUNT(*) AS cnt2
FROM customer WHERE NAStatus = 'Not Sent'
AND a.ImportAlertID = ImportAlertID)tmp2
[/code]

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-16 : 14:08:25

Very nice!

Never used an outer apply before.

Thanks!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 14:13:34

welcome

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-16 : 14:38:00

Forgot this is the legacy site and am using sql server 2000.
I use 2000/2005/2008 all in the same place and was
working in the 2005 window.

OUTER APPLY didn't come out till 2005.

I'll repost this in the 2000 forum...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 14:44:19

did you try this?


SELECT a.ImportAlertID, a.ClientID,
		
	CASE @type 
         WHEN 1 THEN
	(SELECT COUNT(*) 
		FROM invoicenotadvanced WHERE NAStatus = 0 
		AND a.ImportAlertID = ImportAlertID) 
	WHEN 2 THEN
	(SELECT COUNT(*) 
		FROM customer WHERE NAStatus = 'Not Sent' 
		AND a.ImportAlertID = ImportAlertID) END AS numNA0,

	a.CompleteStatus, a.CompletedBy		

FROM ImportAlert a


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

INNER JOIN (SELECT ImportAlertID, COUNT(*) AS numCustomers
	FROM customer GROUP BY ImportAlertID) cust ON a.ImportAlertID = cust.ImportAlertID

INNER JOIN (SELECT ImportAlertID, COUNT(*) AS numInvoices, SUM(invorigamt) AS sumInvoices					 
	FROM dbo.invoicenotadvanced 
	GROUP BY ImportAlertID) inv 
	ON a.ImportAlertID = inv.ImportAlertID