ISNull Not working

Question

Post reply

ISNull Not working

Faye Fouladi

SSCrazy

Points: 2770
More actions
November 2, 2009 at 6:38 am

#139254

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

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

amfrizzell SSC Journeyman Points: 78 More actions · Answer 1

Just for giggles, try using the caolescle(ltlrim(rtrim(value)), 0) instead of isnull(value, 0)

That might work in this case..

Aaron

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

Johan Bijnens SSC Guru Points: 135506 More actions · Answer 3

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

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

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

- 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

Faye Fouladi SSCrazy Points: 2770 More actions · Answer 4

this problem was posted long time ago. I no longer work on this project, therefore, I cannot test it.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

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? :hehe:

______________________________________________________________________

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

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

Jason L. Selburg

Johan Bijnens SSC Guru Points: 135506 More actions · Answer 6

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.:crazy:

Never mind. Such things happen.;-)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

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

- 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

ISNull Not working

Cookies on SQLServerCentral