InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 13:16:21 GMT20http://www.sqlservercentral.com/Forums/Topic812342-80-1.aspx[quote][b]Faye Fouladi (7/21/2011)[/b][hr]this problem was posted long time ago. I no longer work on this project, therefore, I cannot test it.[/quote]Oops, I didn't see the original date is somewhere in 2009.:crazy:Never mind. Such things happen.;-)Thu, 21 Jul 2011 12:19:32 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic812342-80-1.aspx[quote][b]Faye Fouladi (7/21/2011)[/b][hr]this problem was posted long time ago. I no longer work on this project, therefore, I cannot test it.[/quote]You mean you don't work on the same issue for a year and a half? :hehe:Thu, 21 Jul 2011 08:35:22 GMTJason Selburghttp://www.sqlservercentral.com/Forums/Topic812342-80-1.aspxthis problem was posted long time ago. I no longer work on this project, therefore, I cannot test it.Thu, 21 Jul 2011 08:25:00 GMTFaye Fouladihttp://www.sqlservercentral.com/Forums/Topic812342-80-1.aspxThere is an Inner join with a outer joined object, so the inner join on NULL may be your issue .[code="sql"]Inner Join dbo.tblContracts C ON C.Contract_ID = TBL.TrackingID[/code]So, this may be avoided by altering the inner join to another left joinor maybe better a nested inner join, by shifting the left joined on clause like this.[code="sql"]SELECT OrgCode AS UnitState , OrgCode As State , IsNull(Count(*), 0) As [SLRP Approved]FROM tLookup_StateTable LKStateLeft Join dbo.tbl_Status_Of_ContractRequests_By_State TBL on LKState.OrgCode = TBL.UnitStateLEFT Join dbo.tblContracts C ------ altered inner to left join (consequences) ON C.Contract_ID = TBL.TrackingIDLEFT OUTER JOIN dbo.tLookup_Incentive Lkp ON C.IT_CODE = Lkp.IT_IDWhere ( TBL.APPROVED > 0 ) AND ( TBL.VERIFIED = 0 ) AND ( TBL.[VERIFICATION FAILED] = 0 ) AND ( TBL.[SYS VERIF - LOSS] = 0 ) AND ( C.ContractStatus = 'A' ) AND C.IT_Code = 'S'Group By OrgCodeSELECT OrgCode AS UnitState , OrgCode As State , IsNull(Count(*), 0) As [SLRP Approved]FROM tLookup_StateTable LKStateLeft Join dbo.tbl_Status_Of_ContractRequests_By_State TBLInner Join dbo.tblContracts C ON C.Contract_ID = TBL.TrackingID on LKState.OrgCode = TBL.UnitState --- nested inner join with left joinLEFT OUTER JOIN dbo.tLookup_Incentive Lkp ON C.IT_CODE = Lkp.IT_IDWhere ( TBL.APPROVED > 0 ) AND ( TBL.VERIFIED = 0 ) AND ( TBL.[VERIFICATION FAILED] = 0 ) AND ( TBL.[SYS VERIF - LOSS] = 0 ) AND ( C.ContractStatus = 'A' ) AND C.IT_Code = 'S'Group By OrgCode[/code]Please test it and let us know your findings !Thu, 21 Jul 2011 06:42:44 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic812342-80-1.aspxNot much information to go on. Run the following code and post back comments.[code="sql"]SELECT OrgCode AS UnitState , OrgCode As State, COUNT(*) AS Rows_Per_OrgCode --IsNull(Count(*), 0) As [SLRP Approved] FROM tLookup_StateTable LKState Left Join dbo.tbl_Status_Of_ContractRequests_By_State TBL on LKState.OrgCode = TBL.UnitState Inner Join dbo.tblContracts C ON C.Contract_ID = TBL.TrackingID LEFT OUTER JOIN dbo.tLookup_Incentive Lkp ON C.IT_CODE = Lkp.IT_IDWhere (TBL.APPROVED > 0) AND (TBL.VERIFIED = 0) AND (TBL.[VERIFICATION FAILED] = 0) AND (TBL.[SYS VERIF - LOSS] = 0) AND (C.ContractStatus = 'A') AND C.IT_Code = 'S'Group By OrgCode[/code]Thu, 21 Jul 2011 04:53:37 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic812342-80-1.aspxJust for giggles, try using the caolescle(ltlrim(rtrim(value)), 0) instead of isnull(value, 0)That might work in this case..AaronThu, 21 Jul 2011 04:16:53 GMTamfrizzellhttp://www.sqlservercentral.com/Forums/Topic812342-80-1.aspxSELECT OrgCode AS UnitState , OrgCode As State, IsNull(Count(*), 0) As [SLRP Approved] FROM tLookup_StateTable LKState Left Join dbo.tbl_Status_Of_ContractRequests_By_State TBL on LKState.OrgCode = TBL.UnitState Inner Join dbo.tblContracts C ON C.Contract_ID = TBL.TrackingID LEFT OUTER JOIN dbo.tLookup_Incentive Lkp ON C.IT_CODE = Lkp.IT_ID Where (TBL.APPROVED > 0) AND (TBL.VERIFIED = 0) AND (TBL.[VERIFICATION FAILED] = 0) AND (TBL.[SYS VERIF - LOSS] = 0) AND (C.ContractStatus = 'A') AND C.IT_Code = 'S' Group By OrgCodeI expect the above code to list all the states and when the count for a particular state is null replace it with zero, but this is not workingMon, 02 Nov 2009 06:38:28 GMTFaye Fouladi