Set value if doesn't exist - 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 2000 Forums

SQL Server Development (2000)

Set value if doesn't exist

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-31 : 08:27:38

The following is not quite working the way it needs.
If the importalertID is not found in the table invoicenotadvanced,
it should return -1.

The way it is now, it returns -1 if it's not there OR it's 0 and is there.

It must return -1 ONLY if the importalertID does NOT exist in that table.

SELECT a.ImportAlertID,


ISNULL( (SELECT COUNT(*) 
FROM invoicenotadvanced						
WHERE((creditlimitapproved IS NULL OR creditlimitapproved = 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

Any suggestions?

Thanks,

Zath

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-31 : 13:11:04

Ok, I got it working!

Used a CASE statement.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources