ISNull Not working

  • 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

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

    That might work in this case..

    Aaron

  • 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

  • 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

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

  • 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
  • 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

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

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