Syntax Error IS NULL

  • Welsh Corgi (7/13/2015)


    I can copy and paste but not the whole line when I try and scroll.

    Could you please place the code outside of the CQL Code Tag?

    Thanks.:-)

    It might be easier to start the copy at the end of the line before the code block, and to end it at the beginning of the line after the code block.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • click at the start

    scroll to the end

    shift click

  • Welsh Corgi (7/13/2015)


    drew.allen (7/13/2015)


    ISNULL() requires an opening paren and the subquery also requires an opening paren. You are trying to conflate them. You also have the second argument of the ISNULL() in the middle of the subquery.

    update #tmpTotals SET StateCount = ISNULL((SELECT SUM(DayCount) FROM #tmpTotals t WHERE Loss_state = #tmpTotals.Loss_State ), 0)

    Drew

    Hi Drew,

    I can't copy sand paste the code.:blush:

    UPDATE #tmpTotals

    SETStateCount = ISNULL(( SELECT SUM(DayCount)

    FROM #tmpTotals t

    WHERE Loss_state = #tmpTotals.Loss_State

    ), 0);

    How's this format? Should be much easier to copy/paste! You just have to highlight and scroll down now.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • With all the talk about copy/paste of the code, I have the ask the obvious. Do you understand how the code works? If you don't understand what it's doing, then don't use it. Wayne's signature says it all - you'll be the one supporting it. Make sure you understand what you copy/paste from the internet.

  • Ed Wagner (7/14/2015)


    With all the talk about copy/paste of the code, I have the ask the obvious. Do you understand how the code works? If you don't understand what it's doing, then don't use it. Wayne's signature says it all - you'll be the one supporting it. Make sure you understand what you copy/paste from the internet.

    Wayne's code does come with a money back guarantee though!

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR (7/14/2015)


    Ed Wagner (7/14/2015)


    With all the talk about copy/paste of the code, I have the ask the obvious. Do you understand how the code works? If you don't understand what it's doing, then don't use it. Wayne's signature says it all - you'll be the one supporting it. Make sure you understand what you copy/paste from the internet.

    Wayne's code does come with a money back guarantee though!

    And you get what you pay for also!

  • Lynn Pettis (7/14/2015)


    SQLRNNR (7/14/2015)


    Ed Wagner (7/14/2015)


    With all the talk about copy/paste of the code, I have the ask the obvious. Do you understand how the code works? If you don't understand what it's doing, then don't use it. Wayne's signature says it all - you'll be the one supporting it. Make sure you understand what you copy/paste from the internet.

    Wayne's code does come with a money back guarantee though!

    And you get what you pay for also!

    Sorry, but I must disagree. I think we get a whole lot more than we pay for on SSC.

  • Welsh Corgi (7/13/2015)


    What is wrong with the following Statement?

    update #tmpTotals set StateCount = (Select ISNULL Sum(DayCount),0) from #tmpTotals t where Loss_state = #tmpTotals.Loss_State )

    As the first responder indicated, it's just a missing parenthesis for the ISNULL function.

    UPDATE #tmpTotals

    SET StateCount = (

    SELECT ISNULL(SUM(DayCount),0)

    FROM #tmpTotals AS t

    WHERE t.Loss_state = #tmpTotals.Loss_State

    )

    If that can't be copied, here's the plain text:

    UPDATE #tmpTotals

    SET StateCount = (

    SELECT ISNULL(SUM(DayCount),0)

    FROM #tmpTotals AS t

    WHERE t.Loss_state = #tmpTotals.Loss_State

    )

    Enjoy...

    EDIT: Corrected query based on Gail Shaw's (aka GilaMonster) subsequent post.

    Steve (aka sgmunson) πŸ™‚ πŸ™‚ πŸ™‚
    Rent Servers for Income (picks and shovels strategy)

  • Apart from your syntax error (which i have not checked)

    what's the purpose of comparing same column from same table?

    ..."WHERE Loss_state = #tmpTotals.Loss_State"

  • SolveSQL (7/14/2015)


    Apart from your syntax error (which i have not checked)

    what's the purpose of comparing same column from same table?

    ..."WHERE Loss_state = #tmpTotals.Loss_State"

    That's used to get a correlated subquery and get the proper value for each row instead of a single value for all rows in the table.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • SolveSQL (7/14/2015)


    what's the purpose of comparing same column from same table?

    ..."WHERE Loss_state = #tmpTotals.Loss_State"

    He's not.

    The table inside the subquery is aliased as t and the binding order for columns is to first check tables inside the subquery and then tables outside, so the comparison is bound as

    WHERE t.Loss_state = #tmpTotals.Loss_State

    It should have been written with the column explicitly qualified for safety.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Instead of updating the temp table, why not calculate the value when you populate it, like this:

    SUM(DayCount) OVER(PARTITION BY Loss_State)

    β€œ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

  • Makes sense. Thanks.

  • Try triple-clicking inside the CQL Code Tag.

Viewing 14 posts - 16 through 28 (of 28 total)

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