Select query problem with null value

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)

Select query problem with null value

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2006-02-17 : 11:12:44

I have the following selet query, on my machine it does bring records if there is null value also, but i use the same query on a different machine there it does'nt bringany records and complains
procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.


SELECT TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, CONVERT(varchar(10), TAB_ccsNetSU.PDReceivedDate, 101) AS PDReceivedDate, 
     CONVERT(varchar(10), TAB_ccsNetSU.PDToReviewDate, 101) AS PDToReviewDate, CONVERT(varchar(10), TAB_ccsNetSU.PDToContractorDate, 101) AS PDToContractorDate, TAB_ccsNetSU.PDSupervisor, 
                      TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription, 
                      CONVERT(varchar(10), TAB_ccsNetSU.PDNeedDate, 101) AS [When]
FROM         TAB_ccsNetSU INNER JOIN
TAB_ccsNetActions ON TAB_ccsNetSU.SUID = TAB_ccsNetActions.ModuleRecordID WHERE     (TAB_ccsNetActions.ModuleName = 'SU')
					GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, TAB_ccsNetSU.PDToReviewDate,  TAB_ccsNetSU.PDNeedDate,
					TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription
The 'USP_GetStatReportSU' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Thank you very much for the information.

here is my whole stored procedure, i used a print statement executing with 'All' in the above query:


--This stp is needed to return the program, project, and/or contracts a user at 
--enterprise level below Division has access to.

CREATE PROCEDURE USP_GetStatReportSU
		(@eStatus			nvarchar(20))

AS

DECLARE @@ErrorCode				int
DECLARE @SQLStatement 			nvarchar(2000)
DECLARE @SQLJoin 				nvarchar(1000)

SET NOCOUNT ON


SELECT @SQLJoin = 
	CASE 	@eStatus
		WHEN 'All' THEN 'WHERE     (TAB_ccsNetActions.ModuleName = ''SU'')
					GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, 

TAB_ccsNetSU.PDToReviewDate,  TAB_ccsNetSU.PDNeedDate,
					TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, 

TAB_ccsNetActions.ActionDescription'

		WHEN 'Open' THEN 'WHERE     (TAB_ccsNetActions.ModuleName = ''SU'' and 

isdate(TAB_ccsNetSU.PDToContractorDate) = 0 and isdate(TAB_ccsNetSU.PDReceivedDate) = 1)
			GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, 

TAB_ccsNetSU.PDToReviewDate,  TAB_ccsNetSU.PDNeedDate,
			TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, 

TAB_ccsNetActions.ActionDescription'	
			
		WHEN 'Closed' THEN 'WHERE     (TAB_ccsNetActions.ModuleName = ''SU'' and 

isdate(TAB_ccsNetSU.PDToContractorDate) = 1 and isdate(TAB_ccsNetSU.PDReceivedDate) = 1)
			GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, 

TAB_ccsNetSU.PDToReviewDate,  TAB_ccsNetSU.PDNeedDate,
			TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, 

TAB_ccsNetActions.ActionDescription'	 

		WHEN 'Late' THEN 'WHERE     (TAB_ccsNetActions.ModuleName = ''SU'' and isdate(TAB_ccsNetSU.PDReceivedDate) = 

1 and isdate(TAB_ccsNetSU.PDCompleteddate) = 0 and isdate(TAB_ccsNetSU.pdneeddate) = 1 and TAB_ccsNetSU.pdneeddate < 

getdate())
					GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, 

TAB_ccsNetSU.PDToReviewDate,  TAB_ccsNetSU.PDNeedDate,
					TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, 

TAB_ccsNetActions.ActionDescription'

		ELSE 'WHERE     (TAB_ccsNetActions.ModuleName = ''SU'')
			GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, 

TAB_ccsNetSU.PDToReviewDate, TAB_ccsNetSU.PDNeedDate,
			TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, 

TAB_ccsNetActions.ActionDescription'
END


SET @SQLStatement = 'SELECT TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, 

rtrim(isnull(CONVERT(varchar(10),TAB_ccsNetSU.PDReceivedDate,101),'''')) AS PDReceivedDate, 
rtrim(isnull(CONVERT(varchar(10),TAB_ccsNetSU.PDToReviewDate,101),'''')) AS PDToReviewDate, 
rtrim(isnull(CONVERT(varchar(10),TAB_ccsNetSU.PDToContractorDate,101),'''')) AS PDToContractorDate, 
rtrim(isnull(TAB_ccsNetSU.PDSupervisor,'''')) as PDSupervisor, 
TAB_ccsNetSU.SUID, rtrim(isnull(TAB_ccsNetActions.ActionDescription,'''')) as ActionDescription, 
rtrim(isnull(CONVERT(varchar(10),TAB_ccsNetSU.PDNeedDate,101),'''')) AS [When]
FROM         TAB_ccsNetSU INNER JOIN
TAB_ccsNetActions ON TAB_ccsNetSU.SUID = TAB_ccsNetActions.ModuleRecordID ' + @SQLJoin

EXEC (@SQLStatement)
--print (@SQLStatement)
SELECT @@ErrorCode = @@Error

SET NOCOUNT OFF
RETURN @@ErrorCode
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources