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