Syntax Error IS NULL

Question

Syntax Error IS NULL

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

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

WayneS SSC Guru Points: 95405 More actions · Answer 1

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

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 2

crmitchell

SSCarpal Tunnel

Points: 4557

July 14, 2015 at 9:26 am

#1812371

click at the start

scroll to the end

shift click

SQLRNNR SSC Guru Points: 281324 More actions · Answer 3

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

Ed Wagner SSC Guru Points: 287015 More actions · Answer 4

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQLRNNR SSC Guru Points: 281324 More actions · Answer 5

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

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 6

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!

Ed Wagner SSC Guru Points: 287015 More actions · Answer 7

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

sgmunson SSC Guru Points: 110588 More actions · Answer 8

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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)

SolveSQL SSChasing Mays Points: 627 More actions · Answer 9

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"

Luis Cazares SSC Guru Points: 183686 More actions · Answer 10

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

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

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

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