SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Divide by zero


Divide by zero

Author
Message
SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1598 Visits: 1046
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)?


Ermm You do not know the difference between creating a new TSQL batch and a control flow function? :-P 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... Hehe
Don't bait me... I'm no fish... :
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11266 Visits: 12005
SanDroid (12/27/2012)
Ermm You do not know the difference between creating a new TSQL batch and a control flow function? :-P

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... Hehe

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
sipas
sipas
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 718
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.
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5358 Visits: 4694
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! w00t Hehe



+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
the sqlist
the sqlist
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 724
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
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4886 Visits: 72519
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 Smile

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
Andre Ranieri
Andre Ranieri
Mr or Mrs. 500
Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)

Group: General Forum Members
Points: 567 Visits: 379
I answered 1,4,5 on this one.

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

Andre
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3639 Visits: 3889
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<>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
Allan Kote
Allan Kote
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 35
I also answered 1, 4 and 5. Thanks for all the explanations.
asifkareem
asifkareem
SSChasing Mays
SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)

Group: General Forum Members
Points: 659 Visits: 151
Good One.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search