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 «««1234»»

Divide by zero Expand / Collapse
Author
Message
Posted Thursday, December 27, 2012 7:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
Hugo Kornelis (12/26/2012)
SanDroid (12/26/2012)
Hugo, Great question that realy made me think.
However part of your explination seems incomplete or missleading.

If statements and other control flow language create new batches, and that is why they can not be used in the definition of a view. Anything that creates more than one batch can not be used in the definition of a view.

Hi SanDroid,

Thank you for your kind words! (and thank you to other people who wrote kind words as well).

I don't really understand the point you raise about the incomplete explanation. What you say is right - you cannot use control flow in a view definition; [color=#FF0000]in fact, a view definition has to be a single SELECT.[/color] But how does that relate to the question and explanation? Can you clarify (maybe with some sample code to illustrate the issue)?


You do not know the difference between creating a new TSQL batch and a control flow function? With all the ";" followed by a "go" in your example code I guess that is no suprise. Here is some infomration online you can read...
http://www.sqlservergeeks.com/blogs/piyush.bajaj.2007/sql-server-bi/280/sql-server-%E2%80%93-transact-sql-batches

Also your red colored quote above is totaly wrong. More than one select can be in any View, but only one batch. Please tell me you do not need a code example to know the differnce between one select statement and one TSQL batch...
Don't bait me... I'm no fish... :
Post #1400606
Posted Thursday, December 27, 2012 8:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 6,043, Visits: 8,324
SanDroid (12/27/2012)
You do not know the difference between creating a new TSQL batch and a control flow function?

I don't even know what a control flow function is. All control flow language elements I know are statements: BEGIN...END, BREAK, CONTINUE, GOTO, IF...ELSE, RETURN, THROW, TRY...CATCH, WAITFOR, and WHILE. (See http://msdn.microsoft.com/en-us/library/ms174290.aspx).

If you meant to write controol flow statements, then the answer is yes, obviously I do know the difference between creating a new batch and a control flow statement. I just don't see how that relates to this question of the day.


With all the ";" followed by a "go" in your example code I guess that is no suprise.

I fail to see the relation between properly terminating statements and control-flow language. And you make it sound as if a semicolon before the batch seperator is a bad thing. It is not.
For the record, the semicolon is the statement terminator in T-SQL. Using it has always been allowed, but used to be optional. As of SQL Server 2005 (if I recall correctly), not terminating statements has been put on the deprecated list, meanning it is still supported now, but will not be supported in a future release. It is already to properly terminate every statement that precedes a CTE or a Service Broker query (anything that starts with WITH), and it is also already mandatory to terminate MERGE statements.
Since 2005, I have forced myself to write all new code in the advised form - with semicolons terminating all statements.
Sources: Transact-SQL Syntax Conventions and Deprecated Database Engine Features in SQL Server 2012.



Thanks for the link. It didn't learn me anything I didn't already know, but it might be useful for other readers of this discussion.
For the record, I always prefer to post links to Books Online or other official Microsoft documentation. If I can't find any of that, my second choice is a blog from one of the people who work on the SQL Server team. The third choice is then a blog, where I still try to limit myself to the most authorative SQL Server authors - people like Kalen Delaney, Paul Randal, Kimberly Tripp, Paul White, and a few others.


Also your red colored quote above is totaly wrong. More than one select can be in any View, but only one batch.

You're right, I should have said: "a view definition has to be a single query". ("query" instead of "select"). Using subqueries, that single query can contain multiple occurrences of the keyword select.



Please tell me you do not need a code example to know the differnce between one select statement and one TSQL batch...

No, I don't. I do still need either code examples or a much better explanation to understand why you think that part of the explanation of the question "seems incomplete or missleading". So far, you only managed to confuse me more.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1400632
Posted Friday, December 28, 2012 4:52 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:53 AM
Points: 108, Visits: 208
I really like the question - you had to carefully go through each example to get the right answers - shame about the 'choose 2' cock-up but it did make me check my reasoning several times . Learnt something new about dividing by NULL, and NULLIF.
Post #1400870
Posted Wednesday, January 2, 2013 1:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, October 19, 2014 2:53 AM
Points: 3,133, Visits: 3,402
DugyC (12/24/2012)
Merde! If only I'd waited a bit longer... however Steve might be on hols with the family already.

....

Having determined 1,4 & 5 were correct in SQL2008, thought maybe this question was over all versions of SQL. So hooked up to a SQL2000 box and tried that, which only allowed 4 & 5. Hence my answers.


....
Wishing both Hugo and Tom, and all other SQL bods out there, a very Merry Christmas and a memorable New Year... hic!



+1
Doesn't help that we haven't got an install of 2012 here either so any questions on 2012 have to be educated guesses for me anyway


-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1401693
Posted Wednesday, January 2, 2013 7:29 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:56 PM
Points: 136, Visits: 624
The "official" answer given is not accurate. Views 3 and 6 are not created due to syntax errors and from the rest 1, 4 and 5 don't return errors if the Divider is 0. I gave 1 and 5 as a result but the correct was given for 1 and 4. The question was to indicate 2 views that don't cause error and not the first 2 views that don't cause errors. Here is the code:

create table MyTable(KeyColumn int, Dividend int, Divisor int)
go
insert into MyTable values(1,2,0)
-- View 1
CREATE VIEW dbo.View1
AS
SELECT KeyColumn, Dividend, Divisor,
CASE WHEN Divisor <> 0 THEN Dividend / Divisor END AS Division
FROM dbo.MyTable;
go

-- View 2
CREATE VIEW dbo.View2
AS
SELECT KeyColumn, Dividend, Divisor,
NULLIF(Dividend / Divisor, 0) AS Division
FROM dbo.MyTable;
go

-- View 3
CREATE VIEW dbo.View3
AS
SELECT KeyColumn, Dividend, Divisor,
IF (Divisor <> 0) THEN Dividend / Divisor AS Divisor
FROM dbo.MyTable;
go

-- View 4
CREATE VIEW dbo.View4
AS
SELECT KeyColumn, Dividend, Divisor,
Dividend / CASE WHEN Divisor <> 0 THEN Divisor END AS Division
FROM dbo.MyTable;
go

-- View 5
CREATE VIEW dbo.View5
AS
SELECT KeyColumn, Dividend, Divisor,
Dividend / NULLIF(Divisor, 0) AS Division
FROM dbo.MyTable;
go

-- View 6
CREATE VIEW dbo.View6
AS
SELECT KeyColumn, Dividend, Divisor,
Dividend / IF (Divisor <> 0) THEN Divisor AS Divisor
FROM dbo.MyTable;
go


select '1' vw,* from dbo.View1
go
select '2' vw,* from dbo.View2
go
select '4' vw,* from dbo.View4
go
select '5' vw,* from dbo.View5
go

Results:
vw KeyColumn Dividend Divisor Division
---- ----------- ----------- ----------- -----------
1 1 2 0 NULL

(1 row(s) affected)

vw KeyColumn Dividend Divisor Division
---- ----------- ----------- ----------- -----------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

vw KeyColumn Dividend Divisor Division
---- ----------- ----------- ----------- -----------
4 1 2 0 NULL

(1 row(s) affected)

vw KeyColumn Dividend Divisor Division
---- ----------- ----------- ----------- -----------
5 1 2 0 NULL

(1 row(s) affected)


Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

Post #1401834
Posted Wednesday, January 2, 2013 7:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
Hugo Kornelis (12/23/2012)
I have to offer my apologies for this question. I don't know what went wrong. I always check my question, answers and explanation several times before submitting, but I don't keep screenshots - so I cannot check if I really did mess this one up, or if something changed the data I submitted.

The question should of course have read "check THREE answers". And the correct answer options should have been 1, 4, and 5, as explained in the explanation. I'll contact Steve and ask him to correct this as soon as possible.


woah, that's a relief... Hopefully steve comes back soon. I was able to deduce in my head that 1,4,5 would work, and then was a bit shocked when I was wrong :)

All is good Hugo... sometimes the keyboard hates us is all.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1401858
Posted Tuesday, January 8, 2013 12:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:59 AM
Points: 290, Visits: 312
I answered 1,4,5 on this one.

It was a good refresher experience. Thanks for the question!

Andre
Post #1404417
Posted Thursday, January 10, 2013 3:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
Hugo Kornelis (12/23/2012)

While hunting for this information, I did find a few Connect items about errors with WHEN (something that's never true) THEN MIN(1/0) or WHEN (something that's always true) THEN 1 ELSE MIN(1/0) - they are the connect items that prompted this clarification to be added to Books Online.

I think this more a theoretical problem though.
SQL Server seems short-cirquit constant expressions (1/0) first, but usually you wont have such an expression in your queries (unless generated by code).

Example: The version with 1/0 returns the error, but the semantically identical version with 1/(x-x) does not return the error:

SELECT CASE WHEN object_id = object_id+1 THEN MIN(1/0) ELSE 1 END
FROM sys.objects GROUP BY object_id


-----------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.


SELECT CASE WHEN object_id = object_id+1 THEN MIN(1/(object_id-object_id))  ELSE 1 END
FROM sys.objects GROUP BY object_id

...
(1456 row(s) affected)


And we can even short-cirquit version 1 once more to make it work again:
SELECT CASE WHEN 1&lt;&gt;1 THEN MIN(1/0) ELSE 1 END FROM sys.objects GROUP BY object_id

...
(1456 row(s) affected)

SQL Server knows that 1<>1 is never true, and therefore doesn't bother to evaluate MIN(1/0)


Best Regards,
Chris Büttner
Post #1405301
Posted Tuesday, January 15, 2013 5:36 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 18, 2013 3:24 AM
Points: 74, Visits: 35
I also answered 1, 4 and 5. Thanks for all the explanations.
Post #1407195
Posted Tuesday, January 15, 2013 1:19 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 8:54 PM
Points: 606, Visits: 151
Good One.
Post #1407456
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse