Select if null set to -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 if null set to -1

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-30 : 14:00:36

The following is in use and works fine:

	SELECT a.ImportAlertID,  					
				
	(SELECT COUNT(*) 
		FROM invoicenotadvanced  						
		WHERE((creditlimitapproved IS NULL OR creditlimitapproved = 0) 
		OR (custconcentrationapproved IS NULL OR custconcentrationapproved = 0) 		
		OR (CBDateApproved IS NULL OR CBDateApproved = 0))
		AND a.ImportAlertID = ImportAlertID) 
	AS fundingRuleFail,

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

	WHERE	
		(
			(@clientID IS NULL OR @clientID = '' OR a.ClientID = @clientID)
			AND (@type IS NULL OR @type = '' OR a.ImportTypeID = @type)
			AND (@days IS NULL OR @days = '' OR DATEDIFF(day, a.CompletedDate, GETDATE()) <= @days)
		)	
		AND 
	a.CompleteStatus = 1

For the SELECT COUNT(*), if a.ImportAlertID does NOT exist in the table,
The fundingRuleFail should be set to '-1'

Been trying to get it to work but just can't see it.

Any suggestions are welcome,

Thanks,

Zath

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-30 : 14:23:28

[code]SELECT a.ImportAlertID,
ISNULL(tmp.fundingRuleFail,-1) AS fundingRuleFail
FROM ImportAlert a
INNER JOIN ImportType b ON a.ImportTypeID = b.ImportTypeID
INNER JOIN Client c ON a.ClientID = c.pk_id
OUTER APPLY (SELECT COUNT(*) AS fundingRuleFail
FROM invoicenotadvanced
WHERE((creditlimitapproved IS NULL OR creditlimitapproved = 0)
OR (custconcentrationapproved IS NULL OR custconcentrationapproved = 0)
OR (CBDateApproved IS NULL OR CBDateApproved = 0))
AND a.ImportAlertID = ImportAlertID) tmp
WHERE
(
(@clientID IS NULL OR @clientID = '' OR a.ClientID = @clientID)
AND (@type IS NULL OR @type = '' OR a.ImportTypeID = @type)
AND (@days IS NULL OR @days = '' OR DATEDIFF(day, a.CompletedDate, GETDATE()) <= @days)
)
AND
a.CompleteStatus = 1

[/code]

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-30 : 14:34:42

Ok, I did it again. This is a sql server 2000 database.

OUTER APPLY won't work.

Sorry about that, wrong forum agian...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-30 : 14:38:04

[code]
SELECT a.ImportAlertID,
ISNULL( (SELECT COUNT(*)
FROM invoicenotadvanced
WHERE((creditlimitapproved IS NULL OR creditlimitapproved = 0)
OR (custconcentrationapproved IS NULL OR custconcentrationapproved = 0)
OR (CBDateApproved IS NULL OR CBDateApproved = 0))
AND a.ImportAlertID = ImportAlertID) ,-1)
AS fundingRuleFail,

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

WHERE
(
(@clientID IS NULL OR @clientID = '' OR a.ClientID = @clientID)
AND (@type IS NULL OR @type = '' OR a.ImportTypeID = @type)
AND (@days IS NULL OR @days = '' OR DATEDIFF(day, a.CompletedDate, GETDATE()) <= @days)
)
AND
a.CompleteStatus = 1

[/code]

jeremygiaco
Starting Member

14 Posts

Posted - 2009-07-30 : 14:39:39

You could try something like this...


SELECT a.ImportAlertID, 
	IsNull(d.ImportAlertIdCount,-1) FundingRuleFail
FROM ImportAlert a
	INNER JOIN ImportType b ON a.ImportTypeID = b.ImportTypeID
	INNER JOIN Client c ON a.ClientID = c.pk_id
	LEFT JOIN (
		SELECT Count(*) ImportAlertIdCount, ImportAlertID
		FROM invoicenotadvanced  						
		WHERE((creditlimitapproved IS NULL OR creditlimitapproved = 0) 
			OR (custconcentrationapproved IS NULL OR custconcentrationapproved = 0) 		
			OR (CBDateApproved IS NULL OR CBDateApproved = 0))
		GROUP BY ImportAlertID
		) d on a.ImportAlertID = d.ImportAlertID
WHERE	
	(
		(IsNull(@clientID,'') = '' OR a.ClientID = @clientID)
		AND (IsNull(@type,'') = '' OR a.ImportTypeID = @type)
		AND (IsNull(@days,'') = '' OR DATEDIFF(day, a.CompletedDate, GETDATE()) <= @days)
	)	
	AND a.CompleteStatus = 1

Jeremy Giaco

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-30 : 15:17:37

Ok, tried both suggestions and thanks btw.

For Jeremy, I'm getting back the -1 but it should be 0 in some cases.

Here's more of an explaination...

If I run this:

SELECT COUNT(*) FROM invoicenotadvanced
WHERE ImportAlertID = 10

it comes up 0 becuase even though that id is in the ImportAlert table, it's NOT in the invoicenotadvanced table.

But others are and the count should be 0 or above if it's in the invoicenotadvanced table and -1 if it's not in the invoicenotadvanced table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources