Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ISNull Not working Expand / Collapse
Author
Message
Posted Monday, November 2, 2009 6:38 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 PM
Points: 114, Visits: 186
SELECT 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 OrgCode


I 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 working
Post #812342
Posted Thursday, July 21, 2011 4:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 8:26 AM
Points: 2, Visits: 109
Just for giggles, try using the caolescle(ltlrim(rtrim(value)), 0) instead of isnull(value, 0)

That might work in this case..

Aaron
Post #1145730
Posted Thursday, July 21, 2011 4:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 6,872, Visits: 14,185
Not much information to go on.
Run the following code and post back comments.

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_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 OrgCode



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1145741
Posted Thursday, July 21, 2011 6:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:21 AM
Points: 6,743, Visits: 8,517
There is an Inner join with a outer joined object, so the inner join on NULL may be your issue .

Inner Join dbo.tblContracts C
ON C.Contract_ID = TBL.TrackingID

So, this may be avoided by altering the inner join to another left join
or maybe better a nested inner join, by shifting the left joined on clause like this.

SELECT  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
LEFT Join dbo.tblContracts C ------ altered inner to left join (consequences)
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 OrgCode



SELECT 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
Inner Join dbo.tblContracts C
ON C.Contract_ID = TBL.TrackingID
on LKState.OrgCode = TBL.UnitState --- nested inner join with left join
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 OrgCode

Please test it and let us know your findings !


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1145799
Posted Thursday, July 21, 2011 8:25 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 PM
Points: 114, Visits: 186
this problem was posted long time ago. I no longer work on this project, therefore, I cannot test it.
Post #1145937
Posted Thursday, July 21, 2011 8:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:01 PM
Points: 2,717, Visits: 3,856
Faye Fouladi (7/21/2011)
this problem was posted long time ago. I no longer work on this project, therefore, I cannot test it.


You mean you don't work on the same issue for a year and a half?


______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #1145947
Posted Thursday, July 21, 2011 12:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:21 AM
Points: 6,743, Visits: 8,517
Faye Fouladi (7/21/2011)
this problem was posted long time ago. I no longer work on this project, therefore, I cannot test it.


Oops, I didn't see the original date is somewhere in 2009.

Never mind. Such things happen.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1146184
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse