Difficult Query: is this possible in SQL?

[Robert Brown]

suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

Thanks!
- Robert

[Mikito Harakiri]

"Robert Brown" <ro*************@yahoo.com> wrote in message
news:24**************************@posting.google.c om...

suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

with T as (
select 1 id,2 a,3 b,0 c,4 d from dual
union all
select 2, 6,2,0,5 from dual
union all
select 3, 1,7,9,0 from dual
union all
select 4, 0,2,0,0 from dual
) select distinct
(select a from T where id=(select max(id) from T where a!=0) ),
(select b from T where id=(select max(id) from T where b!=0) ),
(select c from T where id=(select max(id) from T where c!=0) ),
(select d from T where id=(select max(id) from T where d!=0) )
from T

[John Gilson]

"Robert Brown" <ro*************@yahoo.com> wrote in message
news:24**************************@posting.google.c om...

suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

Thanks!
- Robert

CREATE TABLE T
(
level INT NOT NULL PRIMARY KEY,
color VARCHAR(10) NULL,
length INT NULL,
width INT NULL,
height INT NULL
)

-- Option 1
SELECT (SELECT color FROM T WHERE level = M.LC) AS color,
(SELECT length FROM T WHERE level = M.LL) AS length,
(SELECT width FROM T WHERE level = M.LW) AS width,
(SELECT height FROM T WHERE level = M.LH) AS height
FROM (SELECT
MAX(CASE WHEN color IS NOT NULL THEN level END) AS LC,
MAX(CASE WHEN length IS NOT NULL THEN level END) AS LL,
MAX(CASE WHEN width IS NOT NULL THEN level END) AS LW,
MAX(CASE WHEN height IS NOT NULL THEN level END) AS LH
FROM T) AS M

-- Option 2
SELECT MIN(CASE WHEN T.level = M.LC THEN T.color END) AS color,
MIN(CASE WHEN T.level = M.LL THEN T.length END) AS length,
MIN(CASE WHEN T.level = M.LW THEN T.width END) AS width,
MIN(CASE WHEN T.level = M.LH THEN T.height END) AS height
FROM (SELECT
MAX(CASE WHEN color IS NOT NULL THEN level END) AS LC,
MAX(CASE WHEN length IS NOT NULL THEN level END) AS LL,
MAX(CASE WHEN width IS NOT NULL THEN level END) AS LW,
MAX(CASE WHEN height IS NOT NULL THEN level END) AS LH
FROM T) AS M
INNER JOIN T
ON T.level IN (M.LC, M.LL, M.LW, M.LH)

--
JAG

[--CELKO--]

DROP TABLE Foobar;
CREATE TABLE Foobar
(level INTEGER NOT NULL PRIMARY KEY,
color VARCHAR(10),
length INTEGER,
width INTEGER,
hgt INTEGER);

INSERT INTO Foobar VALUES (1, 'RED', 8, 10, 12);
INSERT INTO Foobar VALUES (2, NULL, NULL, NULL, 20);
INSERT INTO Foobar VALUES (3, NULL, 9, 82, 25);
INSERT INTO Foobar VALUES (4, 'BLUE', NULL, 67, NULL);
INSERT INTO Foobar VALUES (5, 'GRAY', NULL, NULL, NULL);

SELECT
COALESCE (F5.color, F4.color, F3.color, F2.color, F1.color) AS color,
COALESCE (F5.length, F4.length, F3.length, F2.length, F1.length) AS length,
COALESCE (F5.width, F4.width, F3.width, F2.width, F1.width) AS width,
COALESCE (F5.hgt, F4.hgt, F3.hgt, F2.hgt, F1.hgt) AS hgt
FROM Foobar AS F1, Foobar AS F2, Foobar AS F3,
Foobar AS F4, Foobar AS F5
WHERE F1.level = 1
AND F2.level = 2
AND F3.level = 3
AND F4.level = 4
AND F5.level = 5;

[--CELKO--]

DROP TABLE Foobar;
CREATE TABLE Foobar
(level INTEGER NOT NULL PRIMARY KEY,
color VARCHAR(10),
length INTEGER,
width INTEGER,
hgt INTEGER);

INSERT INTO Foobar VALUES (1, 'RED', 8, 10, 12);
INSERT INTO Foobar VALUES (2, NULL, NULL, NULL, 20);
INSERT INTO Foobar VALUES (3, NULL, 9, 82, 25);
INSERT INTO Foobar VALUES (4, 'BLUE', NULL, 67, NULL);
INSERT INTO Foobar VALUES (5, 'GRAY', NULL, NULL, NULL);

SELECT
COALESCE (F5.color, F4.color, F3.color, F2.color, F1.color) AS color,
COALESCE (F5.length, F4.length, F3.length, F2.length, F1.length) AS length,
COALESCE (F5.width, F4.width, F3.width, F2.width, F1.width) AS width,
COALESCE (F5.hgt, F4.hgt, F3.hgt, F2.hgt, F1.hgt) AS hgt
FROM Foobar AS F1, Foobar AS F2, Foobar AS F3,
Foobar AS F4, Foobar AS F5
WHERE F1.level = 1
AND F2.level = 2
AND F3.level = 3
AND F4.level = 4
AND F5.level = 5;

[Richard]

Hi Robert,

Here are 2 more solutions...
create table Foobar
(
level int NOT NULL , -- pk
color varchar(10) NULL ,
length int NULL ,
width int NULL ,
hgt int NULL ,

primary key clustered
( level )
)

insert Foobar
( level, color, length, width, hgt )
select 1,'RED',8,10,12 UNION ALL
select 2,NULL,NULL,NULL,20 UNION ALL
select 3,NULL,9,82,25 UNION ALL
select 4,'BLUE',NULL,67,NULL UNION ALL
select 5,'GRAY',NULL,NULL,NULL

select
(select color from Foobar where level =
(select max(level) from Foobar where color is not null)) as color ,
(select length from Foobar where level =
(select max(level) from Foobar where length is not null)) as length ,
(select width from Foobar where level =
(select max(level) from Foobar where width is not null)) as width ,
(select hgt from Foobar where level =
(select max(level) from Foobar where hgt is not null)) as hgt

select max(case when f.level = t.col then f.color end) as color ,
max(case when f.level = t.lth then f.length end) as length ,
max(case when f.level = t.wth then f.width end) as width ,
max(case when f.level = t.hgt then f.hgt end) as hgt
from (select max(case when color IS NOT NULL then level end),
max(case when length IS NOT NULL then level end),
max(case when width IS NOT NULL then level end),
max(case when hgt IS NOT NULL then level end)
from Foobar) as t(col,lth,wth,hgt), Foobar as f
Richard


ro*************@yahoo.com (Robert Brown) wrote in message news:<24**************************@posting.google. com>...

suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

Thanks!
- Robert

ro*************@yahoo.com (Robert Brown) wrote in message news:<24**************************@posting.google. com>... suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

Thanks!
- Robert

[Aaron W. West]

There's a technique of taking a max of two values concatenated then taking a
substring, which avoids the join needed in the below techniques.

Try this:

select color = substring(max(cast(level as char(1))+color),2,10)
,length = cast(substring(max(cast(level as char(1))+cast(length as
char(9))),2,9) as integer)
,width = cast(substring(max(cast(level as char(1))+cast(width as
char(9))),2,9) as integer)
,hgt = cast(substring(max(cast(level as char(1))+cast(hgt as char(9))),2,9)
as integer)
from Foobar

-aaron
"Richard" <rr*****@optonline.net> wrote in message
news:bf**************************@posting.google.c om...
Hi Robert,

Here are 2 more solutions...
create table Foobar
(
level int NOT NULL , -- pk
color varchar(10) NULL ,
length int NULL ,
width int NULL ,
hgt int NULL ,

primary key clustered
( level )
)

insert Foobar
( level, color, length, width, hgt )
select 1,'RED',8,10,12 UNION ALL
select 2,NULL,NULL,NULL,20 UNION ALL
select 3,NULL,9,82,25 UNION ALL
select 4,'BLUE',NULL,67,NULL UNION ALL
select 5,'GRAY',NULL,NULL,NULL

select
(select color from Foobar where level =
(select max(level) from Foobar where color is not null)) as color ,
(select length from Foobar where level =
(select max(level) from Foobar where length is not null)) as length ,
(select width from Foobar where level =
(select max(level) from Foobar where width is not null)) as width ,
(select hgt from Foobar where level =
(select max(level) from Foobar where hgt is not null)) as hgt

select max(case when f.level = t.col then f.color end) as color ,
max(case when f.level = t.lth then f.length end) as length ,
max(case when f.level = t.wth then f.width end) as width ,
max(case when f.level = t.hgt then f.hgt end) as hgt
from (select max(case when color IS NOT NULL then level end),
max(case when length IS NOT NULL then level end),
max(case when width IS NOT NULL then level end),
max(case when hgt IS NOT NULL then level end)
from Foobar) as t(col,lth,wth,hgt), Foobar as f
Richard


ro*************@yahoo.com (Robert Brown) wrote in message
news:<24**************************@posting.google. com>...

suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

Thanks!
- Robert

ro*************@yahoo.com (Robert Brown) wrote in message
news:<24**************************@posting.google. com>... suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

Thanks!
- Robert

[Richard]

Hi Aaron,
That's an excellent technique, but to use it in this example you need
to take the max() of ONLY the levels for which the tested columns are
NOT NULL. Otherwise you will always get the values for level 5 which
is incorrect. This is best done by adding a case statement inside the
aggregate. When you add that logic, and the conversions back and forth
from integer to char, it starts to get a bit messy.

Here is a working solution for this problem using that technique:
select color = substring(max(cast(case when color IS NOT NULL then
level else 0 end as char(1))+color),2,10),
length = cast(substring(max(cast(case when length IS NOT NULL
then level else 0 end as char(1))+cast(length as char(9))),2,9) as
integer),
width = cast(substring(max(cast(case when width IS NOT NULL
then level else 0 end as char(1))+cast(width as char(9))),2,9) as
integer),
hgt = cast(substring(max(cast(case when hgt IS NOT NULL then
level else 0 end as char(1))+cast(hgt as char(9))),2,9) as integer)
from Foobar

It works, but at some point you need to question whether any benefit
achieved is worth having unreadable code.

Richard
"Aaron W. West" <ta******@hotmail.NO.SPAM> wrote in message news:<TO********************@speakeasy.net>...

There's a technique of taking a max of two values concatenated then taking a
substring, which avoids the join needed in the below techniques.

Try this:

select color = substring(max(cast(level as char(1))+color),2,10)
,length = cast(substring(max(cast(level as char(1))+cast(length as
char(9))),2,9) as integer)
,width = cast(substring(max(cast(level as char(1))+cast(width as
char(9))),2,9) as integer)
,hgt = cast(substring(max(cast(level as char(1))+cast(hgt as char(9))),2,9)
as integer)
from Foobar

-aaron
"Richard" <rr*****@optonline.net> wrote in message
news:bf**************************@posting.google.c om...
Hi Robert,

Here are 2 more solutions...
create table Foobar
(
level int NOT NULL , -- pk
color varchar(10) NULL ,
length int NULL ,
width int NULL ,
hgt int NULL ,

primary key clustered
( level )
)

insert Foobar
( level, color, length, width, hgt )
select 1,'RED',8,10,12 UNION ALL
select 2,NULL,NULL,NULL,20 UNION ALL
select 3,NULL,9,82,25 UNION ALL
select 4,'BLUE',NULL,67,NULL UNION ALL
select 5,'GRAY',NULL,NULL,NULL

select
(select color from Foobar where level =
(select max(level) from Foobar where color is not null)) as color ,
(select length from Foobar where level =
(select max(level) from Foobar where length is not null)) as length ,
(select width from Foobar where level =
(select max(level) from Foobar where width is not null)) as width ,
(select hgt from Foobar where level =
(select max(level) from Foobar where hgt is not null)) as hgt

select max(case when f.level = t.col then f.color end) as color ,
max(case when f.level = t.lth then f.length end) as length ,
max(case when f.level = t.wth then f.width end) as width ,
max(case when f.level = t.hgt then f.hgt end) as hgt
from (select max(case when color IS NOT NULL then level end),
max(case when length IS NOT NULL then level end),
max(case when width IS NOT NULL then level end),
max(case when hgt IS NOT NULL then level end)
from Foobar) as t(col,lth,wth,hgt), Foobar as f
Richard


ro*************@yahoo.com (Robert Brown) wrote in message
news:<24**************************@posting.google. com>...

suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

Thanks!
- Robert

ro*************@yahoo.com (Robert Brown) wrote in message
news:<24**************************@posting.google. com>...

suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

Thanks!
- Robert

[Dario]

ro*************@yahoo.com (Robert Brown) wrote in message news:<24**************************@posting.google. com>...

suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

Thanks!
- Robert

SELECT * FROM
(
select ROW_NUMBER() OVER (ORDER BY L DESC) RN,
first_value(color) over ( order by case when color is null
then -1 else rownum end desc),
first_value(length) over ( order by case when length is null
then -1 else rownum end desc),
first_value(width) over ( order by case when width is null
then -1 else rownum end desc),
first_value(hgt) over ( order by case when hgt is null then
-1 else rownum end desc)
from ( SELECT * FROM foobar ORDER BY L)
)
WHERE RN = 1

[Dario]

ro*************@yahoo.com (Robert Brown) wrote in message news:<24**************************@posting.google. com>...

suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

Thanks!
- Robert

Oracle solution using analytical functions could be:

SELECT * FROM
(
select ROW_NUMBER() OVER (ORDER BY L DESC) RN,
first_value(color) over ( order by case when color is null
then -1 else rownum end desc),
first_value(length) over ( order by case when length is null
then -1 else rownum end desc),
first_value(width) over ( order by case when width is null
then -1 else rownum end desc),
first_value(hgt) over ( order by case when hgt is null then
-1 else rownum end desc)
from ( SELECT * FROM foobar ORDER BY L)
)
WHERE RN = 1

[Aaron W. West]

I got the same output with this technique as with the other two I was
replying to, without your case statements.

Output:
color length width hgt
----------- ----------- ----------- -----------
GRAY 9 67 25

dbcc useroptions
....
ansi_nulls
SET
concat_null_yields_null
SET

Now if I:
SET CONCAT_NULL_YIELDS_NULL OFF

And run my statement again, my output is all wrong:

color length width hgt
----------- ----------- ----------- -----------
GRAY 0 0 0

Perhaps being sensitive to such settings is a bad thing. But I don't change
those options, and consider it generally bad practice to (unless you change
them temporarily inside a transaction and change them back when done? But
it's still bad practice, because some code within the transaction might call
something like this that is dependent on such settings...)

Hmm, I suppose it's safer not to rely on such settings to be correct... But
this setting is usually on, anyway. Note BOL:

"SET CONCAT_NULL_YIELDS_NULL must be set to ON when you create or manipulate
indexes on computed columns or indexed views."

This reply is MS SQL specific so I removed the other newsgroups. Then again,
if CONCAT_NULL_YIELDS_NULL is not the default for other databases, then my
SQL is non-portable, anyway... but so is the CASE statement... I guess
that's the problem with many implementation-specific "tricks"...

-aaron

---

"Richard" <rr*****@optonline.net> wrote in message
news:bf**************************@posting.google.c om...
Hi Aaron,
That's an excellent technique, but to use it in this example you need
to take the max() of ONLY the levels for which the tested columns are
NOT NULL. Otherwise you will always get the values for level 5 which
is incorrect. This is best done by adding a case statement inside the
aggregate. When you add that logic, and the conversions back and forth
from integer to char, it starts to get a bit messy.

Here is a working solution for this problem using that technique:
select color = substring(max(cast(case when color IS NOT NULL then
level else 0 end as char(1))+color),2,10),
length = cast(substring(max(cast(case when length IS NOT NULL
then level else 0 end as char(1))+cast(length as char(9))),2,9) as
integer),
width = cast(substring(max(cast(case when width IS NOT NULL
then level else 0 end as char(1))+cast(width as char(9))),2,9) as
integer),
hgt = cast(substring(max(cast(case when hgt IS NOT NULL then
level else 0 end as char(1))+cast(hgt as char(9))),2,9) as integer)
from Foobar

It works, but at some point you need to question whether any benefit
achieved is worth having unreadable code.

Richard
"Aaron W. West" <ta******@hotmail.NO.SPAM> wrote in message
news:<TO********************@speakeasy.net>...

There's a technique of taking a max of two values concatenated then taking a substring, which avoids the join needed in the below techniques.

Try this:

select color = substring(max(cast(level as char(1))+color),2,10)
,length = cast(substring(max(cast(level as char(1))+cast(length as
char(9))),2,9) as integer)
,width = cast(substring(max(cast(level as char(1))+cast(width as
char(9))),2,9) as integer)
,hgt = cast(substring(max(cast(level as char(1))+cast(hgt as char(9))),2,9) as integer)
from Foobar

-aaron
"Richard" <rr*****@optonline.net> wrote in message
news:bf**************************@posting.google.c om...
Hi Robert,

Here are 2 more solutions...
create table Foobar
(
level int NOT NULL , -- pk
color varchar(10) NULL ,
length int NULL ,
width int NULL ,
hgt int NULL ,

primary key clustered
( level )
)

insert Foobar
( level, color, length, width, hgt )
select 1,'RED',8,10,12 UNION ALL
select 2,NULL,NULL,NULL,20 UNION ALL
select 3,NULL,9,82,25 UNION ALL
select 4,'BLUE',NULL,67,NULL UNION ALL
select 5,'GRAY',NULL,NULL,NULL

select
(select color from Foobar where level =
(select max(level) from Foobar where color is not null)) as color , (select length from Foobar where level =
(select max(level) from Foobar where length is not null)) as length , (select width from Foobar where level =
(select max(level) from Foobar where width is not null)) as width , (select hgt from Foobar where level =
(select max(level) from Foobar where hgt is not null)) as hgt

select max(case when f.level = t.col then f.color end) as color ,
max(case when f.level = t.lth then f.length end) as length ,
max(case when f.level = t.wth then f.width end) as width ,
max(case when f.level = t.hgt then f.hgt end) as hgt
from (select max(case when color IS NOT NULL then level end),
max(case when length IS NOT NULL then level end),
max(case when width IS NOT NULL then level end),
max(case when hgt IS NOT NULL then level end)
from Foobar) as t(col,lth,wth,hgt), Foobar as f
Richard


ro*************@yahoo.com (Robert Brown) wrote in message
news:<24**************************@posting.google. com>...

suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

Thanks!
- Robert

ro*************@yahoo.com (Robert Brown) wrote in message
news:<24**************************@posting.google. com>...

suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

Thanks!
- Robert

[Richard Romley]

>>SET CONCAT_NULL_YIELDS_NULL OFF
Hmm, I suppose it's safer not to rely on such settings to be correct... But
this setting is usually on, anyway...<<

Not in my environment it isn't. I run with ANSI NULLS OFF. The alternative
is to go back and fix 10-12 years worth of code that was written when the
default was the opposite of what it is now. I have learned to never assume
default settings when working in a Microsoft environment. Wait another
couple of years and you can be certain that Microsoft will reverse other
default settings. IMHO it's simply not worth having to debug your code all
over again after every upgrade. If it's specified in the code it will work
in spite of Microsoft. I have learned my lesson.

But your point is well taken.

Richard

"Aaron W. West" <ta******@hotmail.NO.SPAM> wrote in message
news:L9********************@speakeasy.net...

I got the same output with this technique as with the other two I was
replying to, without your case statements.

Output:
color length width hgt
----------- ----------- ----------- -----------
GRAY 9 67 25

dbcc useroptions
...
ansi_nulls
SET
concat_null_yields_null
SET

Now if I:
SET CONCAT_NULL_YIELDS_NULL OFF

And run my statement again, my output is all wrong:

color length width hgt
----------- ----------- ----------- -----------
GRAY 0 0 0

Perhaps being sensitive to such settings is a bad thing. But I don't change those options, and consider it generally bad practice to (unless you change them temporarily inside a transaction and change them back when done? But
it's still bad practice, because some code within the transaction might call something like this that is dependent on such settings...)

Hmm, I suppose it's safer not to rely on such settings to be correct... But this setting is usually on, anyway. Note BOL:

"SET CONCAT_NULL_YIELDS_NULL must be set to ON when you create or manipulate indexes on computed columns or indexed views."

This reply is MS SQL specific so I removed the other newsgroups. Then again, if CONCAT_NULL_YIELDS_NULL is not the default for other databases, then my
SQL is non-portable, anyway... but so is the CASE statement... I guess
that's the problem with many implementation-specific "tricks"...

-aaron

---

"Richard" <rr*****@optonline.net> wrote in message
news:bf**************************@posting.google.c om...
Hi Aaron,
That's an excellent technique, but to use it in this example you need
to take the max() of ONLY the levels for which the tested columns are
NOT NULL. Otherwise you will always get the values for level 5 which
is incorrect. This is best done by adding a case statement inside the
aggregate. When you add that logic, and the conversions back and forth
from integer to char, it starts to get a bit messy.

Here is a working solution for this problem using that technique:
select color = substring(max(cast(case when color IS NOT NULL then
level else 0 end as char(1))+color),2,10),
length = cast(substring(max(cast(case when length IS NOT NULL
then level else 0 end as char(1))+cast(length as char(9))),2,9) as
integer),
width = cast(substring(max(cast(case when width IS NOT NULL
then level else 0 end as char(1))+cast(width as char(9))),2,9) as
integer),
hgt = cast(substring(max(cast(case when hgt IS NOT NULL then
level else 0 end as char(1))+cast(hgt as char(9))),2,9) as integer)
from Foobar

It works, but at some point you need to question whether any benefit
achieved is worth having unreadable code.

Richard
"Aaron W. West" <ta******@hotmail.NO.SPAM> wrote in message
news:<TO********************@speakeasy.net>...

There's a technique of taking a max of two values concatenated then

taking a

substring, which avoids the join needed in the below techniques.

Try this:

select color = substring(max(cast(level as char(1))+color),2,10)
,length = cast(substring(max(cast(level as char(1))+cast(length as
char(9))),2,9) as integer)
,width = cast(substring(max(cast(level as char(1))+cast(width as
char(9))),2,9) as integer)
,hgt = cast(substring(max(cast(level as char(1))+cast(hgt as char(9))),2,9)

as integer)
from Foobar

-aaron
"Richard" <rr*****@optonline.net> wrote in message
news:bf**************************@posting.google.c om...
Hi Robert,

Here are 2 more solutions...
create table Foobar
(
level int NOT NULL , -- pk
color varchar(10) NULL ,
length int NULL ,
width int NULL ,
hgt int NULL ,

primary key clustered
( level )
)

insert Foobar
( level, color, length, width, hgt )
select 1,'RED',8,10,12 UNION ALL
select 2,NULL,NULL,NULL,20 UNION ALL
select 3,NULL,9,82,25 UNION ALL
select 4,'BLUE',NULL,67,NULL UNION ALL
select 5,'GRAY',NULL,NULL,NULL

select
(select color from Foobar where level =
(select max(level) from Foobar where color is not null)) as

color ,

(select length from Foobar where level =
(select max(level) from Foobar where length is not null)) as

length ,

(select width from Foobar where level =
(select max(level) from Foobar where width is not null)) as

width ,

(select hgt from Foobar where level =
(select max(level) from Foobar where hgt is not null)) as hgt

select max(case when f.level = t.col then f.color end) as color ,
max(case when f.level = t.lth then f.length end) as length ,
max(case when f.level = t.wth then f.width end) as width ,
max(case when f.level = t.hgt then f.hgt end) as hgt
from (select max(case when color IS NOT NULL then level end),
max(case when length IS NOT NULL then level end),
max(case when width IS NOT NULL then level end),
max(case when hgt IS NOT NULL then level end)
from Foobar) as t(col,lth,wth,hgt), Foobar as f
Richard


ro*************@yahoo.com (Robert Brown) wrote in message
news:<24**************************@posting.google. com>...

suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

Thanks!
- Robert

ro*************@yahoo.com (Robert Brown) wrote in message
news:<24**************************@posting.google. com>...

suppose I have the following table:

CREATE TABLE (int level, color varchar, length int, width int, height
int)

It has the following rows

1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL

I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)

So I want a query that will return

GRAY, 9, 67, 25

The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.

Is this possible in SQL without using stored procedures?

Thanks!
- Robert

[Erland Sommarskog]

Richard Romley (ri***********@optonline.net) writes:

Not in my environment it isn't. I run with ANSI NULLS OFF. The alternative
is to go back and fix 10-12 years worth of code that was written when the
default was the opposite of what it is now. I have learned to never assume
default settings when working in a Microsoft environment. Wait another
couple of years and you can be certain that Microsoft will reverse other
default settings. IMHO it's simply not worth having to debug your code all
over again after every upgrade. If it's specified in the code it will work
in spite of Microsoft. I have learned my lesson.

Microsoft does not change things at whim. In this particular case, the
original behaviour inherited from Sybase was just plain wrong. It's
basic to relational databases that NULL is never equal to anything,
not even another NULL value.

So even if it once worked with writing

IF x = NULL

it was bad practice already then.

I have to admit that the database I work also have a long history, and
we also run with several ANSI settings off. But my aim is clearly to
change this. To run with ANSI settings off means that you are swimming
against the stream, and that there are several features in SQL Server
you cannot use:

* Queries to linked servers (requires ANSI_NULLS and ANSI_WARNINGS)
* Indexed views (requires six settings to be ON and one to be OFF)
* Index on computed columns. (Ditto)

If all you have is a lot of = NULL, then you can use the SQL Best
Practice Analyzer (downloadable from microsoft.com) to track these down.
If you have more intricate issues like:

SELECT * FROM tbl WHERE col = @val

Where you should have had added OR col IS NULL AND val IS NULL, it's a
little more work. (We are in that boat. :-(

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Richard Romley]

>>Microsoft does not change things at whim.<<

Hmmm. Where have I heard that before? There is ALWAYS a good reason to
change defaults. There is ALWAYS a good reason to change the behavior of
existing code. Well, there is also one very good reason NOT to - doing so
breaks all the code that's been written since the beginning of time that
depended on the old rules.

Microsoft has proven time and time again that they simply don't give a damn
about the harm they inflict on their customers every time they do that. I am
absolutely convinced that nobody at Microsoft has ever worked in a real
production environment. Either that or they really are a bunch of heartless
arrogant b*******.

The best you can hope to do is to try to write ALL code so that it doesn't
depend on ANY defaults anywhere. Microsoft can and will change defaults -
always with a *good* reason - and always at the expense of their existing
customer base. New users never know what they missed - until the next time.

there are several features in SQL Server you cannot use<<
Yes, I am fully aware of that. When we have an absolute need to use one of
these features we will do what we have to to find all of the broken code. In
the meantime, as Microsoft is so fond of saying, there are *workarounds.*
(As you know, in the Microsoft world, if there is a workaround, there really
isn't a problem at all)

Bottom line, anyone who writes code in a Microsoft environment that depends
on Microsoft recommended defaults is setting themselves up for a future
disaster. It's not a matter of IF, but WHEN, it will happen.

As to changing behavior of existing code, there's really not much you can do
to protect against that. When it happens, you debug all over again, or don't
upgrade. Of course the latter is only a temporary solution.

<end of rant>

Richard

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Richard Romley (ri***********@optonline.net) writes:

Not in my environment it isn't. I run with ANSI NULLS OFF. The

alternative is to go back and fix 10-12 years worth of code that was written when the default was the opposite of what it is now. I have learned to never assume default settings when working in a Microsoft environment. Wait another
couple of years and you can be certain that Microsoft will reverse other
default settings. IMHO it's simply not worth having to debug your code all over again after every upgrade. If it's specified in the code it will work in spite of Microsoft. I have learned my lesson.

Microsoft does not change things at whim. In this particular case, the
original behaviour inherited from Sybase was just plain wrong. It's
basic to relational databases that NULL is never equal to anything,
not even another NULL value.

So even if it once worked with writing

IF x = NULL

it was bad practice already then.

I have to admit that the database I work also have a long history, and
we also run with several ANSI settings off. But my aim is clearly to
change this. To run with ANSI settings off means that you are swimming
against the stream, and that there are several features in SQL Server
you cannot use:

* Queries to linked servers (requires ANSI_NULLS and ANSI_WARNINGS)
* Indexed views (requires six settings to be ON and one to be OFF)
* Index on computed columns. (Ditto)

If all you have is a lot of = NULL, then you can use the SQL Best
Practice Analyzer (downloadable from microsoft.com) to track these down.
If you have more intricate issues like:

SELECT * FROM tbl WHERE col = @val

Where you should have had added OR col IS NULL AND val IS NULL, it's a
little more work. (We are in that boat. :-(

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Richard Romley (ri***********@optonline.net) writes:

Hmmm. Where have I heard that before? There is ALWAYS a good reason to
change defaults. There is ALWAYS a good reason to change the behavior of
existing code. Well, there is also one very good reason NOT to - doing so
breaks all the code that's been written since the beginning of time that
depended on the old rules.
If the product does absolutely silly things, you don't have no choice. For
instance in SQL Server 4.x you could say:

SELECT a, b, COUNT(*)
FROM tbl
GROUP BY a

Both Sybase and Microsoft outlawed this syntax in System 10 and SQL
Server 6 respectively.

For the issues we discuss, the keyword is ANSI. Microsoft wanted their
product to adhere more to ANSI standards, and this certainly applies
to existing code, to wit when you port from other products. However,
MS did not go all the way. If you say SET ANSI_DEFAULTS ON, there are
a couple of settings which are set, and I will add ARITHABORT and
CONCAT_NULL_YIELDS_NULL to these.

ANSI_NULL_DLFT_ON - this setting controls what happens if you create
a column in a table without specify explicit nullability. Since none
of NOT NULL or NULL is obvious, good practice is always state this
explicitly.

ANSI_PADDING - this column affects how trailing spaces are saved for
varchar values. It is not likely to affect that many appliactions.

ANSI_WARNINGS - the most important effect is that you get an error if
you try to store a value that does not fir into a char/binary column.
It does affect some application, but there is no dramatic issue. The
other things caused by ANSI_WARNINGS is likely to have even lower impact.

ARITHABORT - Few would probably complain that their division with zero
yields an error (this also comes with ANSI_WARNINGS). Most probably
find it a good thing.

QUOTED_IDENTIFIER - Now, here is one that with a huge impact, since
strings quoted by " suddenly became identifiers. Mitigating, though, is
that this can be fixed mechanically.

ANSI_NULLS - This is an setting that should have absolutely no effect on
properly written code. @x = NULL was wrong in 4.x days, it has always
been wrong, it was just that Sybase out of folly handled NULL as equal
to NULL. But of course, a system where the programmers did not have
understanding of NULL values takes a serious toll here. But as I said
the Best Practice Analyzer can help you out here.

CONCAT_NULL_YIELDS_NULL - Exactly the same thing applies here: on a
properly implemented system, this is not an issue.

.....................

IMPLICIT_TRANSACTIONS - Now here is one! This option is OFF by default,
and had MS made this the default, about every application out there would
have succumbed. Right or wrong, auto-commit has always been the way Sybase
and SQL Server has gone.

CURSOR_CLOSE_ON_COMMIT - This option is OFF by default as well. Since
cursors are something you should not use anyway, it should have less
impact. But the whole idea sounds corny to me. I might have a transaction
for each iteration in the cursor. Certainly I don't want it close.

So Microsoft acted judiciously when they decided what should be on by
default and what should not. ANSI_NULLS and CONCAT_NULL_YIELDS_NULL
may have a great impact your code, but let me again stress that the
original defaults were just plain wrong and indefensible to have in
an engine to aspire to be an enterprise DBMS.
Microsoft has proven time and time again that they simply don't give a
damn about the harm they inflict on their customers every time they do
that.

Since I have good contacts with the SQL Server team, I can assure you
that they are very anxious about compability. Very anxious.

But if you have ever worked with product development, you also know that
sometimes maintaining old functionality can be a true burden. (If you
work for a site where you only have one production environment, this is
not an issue for you. You can rip things out as you please.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Richard Romley]

Erland,

If the product does absolutely silly things, you don't have no choice<<
I have in my hand a SQL Server 1.0 manual dated 1989. Across the top of the
front cover it says "Microsoft" (and Ashton-Tate <g>). If the product did
absolutely silly things Microsoft never should have put their name on it -
and then maintained the *faulty* logic for 10 years before deciding, all of
a sudden, that the behavior was suddenly unacceptable.

In any case, in addition to the *absolutely silly things* that it did, it
also did perfectly reasonable things. One small example - the original
PATINDEX function returned a 0 if a match was made beginning with the 1st
character of a string. Then somebody noticed that the CHARINDEX function
returned a 1 on a 1st character match. Thinking that it would be nice for
these 2 similar functions to work the same way, they simply changed the
PATINDEX function. It wasn't even a documented change. That was a long time
ago, but I still remember very well what happened to my code. Just a small
example of many - and has nothing to do with ANSI compliance.

A more recent example was a *feature* introduced in release 7. Somebody
decided that the ARITHABORT command was really only to be taken seriously
for SELECT statements and should be ignored for INSERT and UPDATE
statements. This was also (initially) an undocumented feature. This really
wasn't very nice for those of us who process million row inserts at night
which include arithmetic calculations on some of the columns. Microsoft
refused to fix this until SQL 2000. That one *feature* alone made 7.0 an
unusable product for us.
the keyword is ANSI. Microsoft wanted their product to adhere more to ANSI standards<<

Erland, that is Microsoft's line and the excuse they use every time they
deliberately break something. The bottom line here is they are very
selective about WHICH ANSI rules they choose to implement and which they
conveniently ignore. If you don't believe it, call up your friends on the
development team and remind them that FROM clauses are illegal in UPDATE and
DELETE statements and they really shouldn't allow this non-ANSI behavior in
their product. Be sure to report back what they say.
...when you port from other products...<<
This is my favorite. How many products have stored procedures written in
TSQL? OK, SYBASE at least started out the same - but that was a long time
ago. When's the last time you tried to port a SYBASE stored procedure into
SQL Server? Oracle? Anybody? When's the last time you tried to port a SQL
Server stored procedure to any other database? I just love hearing about
portability!

Erland, there's nothing wrong with enhancements. Add all the features you
want but don't break my old code in the process. And don't tell me that
everything you do is to improve ANSI compliance. It's true sometimes, but
ONLY sometimes.

Microsoft simply has a terrible track record when it comes to backward
compatibility. I've been burned so many times you'll never convince me
otherwise.

In any case, what started this whole discussion was a query example that
only worked with defaults configured a particular way. I maintain that if
you code that way you will eventually be punished by Microsoft for trusting
them. It happens with almost every new release.

Richard
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Richard Romley (ri***********@optonline.net) writes:

Hmmm. Where have I heard that before? There is ALWAYS a good reason to
change defaults. There is ALWAYS a good reason to change the behavior of
existing code. Well, there is also one very good reason NOT to - doing

so breaks all the code that's been written since the beginning of time that
depended on the old rules.

If the product does absolutely silly things, you don't have no choice. For
instance in SQL Server 4.x you could say:

SELECT a, b, COUNT(*)
FROM tbl
GROUP BY a

Both Sybase and Microsoft outlawed this syntax in System 10 and SQL
Server 6 respectively.

For the issues we discuss, the keyword is ANSI. Microsoft wanted their
product to adhere more to ANSI standards, and this certainly applies
to existing code, to wit when you port from other products. However,
MS did not go all the way. If you say SET ANSI_DEFAULTS ON, there are
a couple of settings which are set, and I will add ARITHABORT and
CONCAT_NULL_YIELDS_NULL to these.

ANSI_NULL_DLFT_ON - this setting controls what happens if you create
a column in a table without specify explicit nullability. Since none
of NOT NULL or NULL is obvious, good practice is always state this
explicitly.

ANSI_PADDING - this column affects how trailing spaces are saved for
varchar values. It is not likely to affect that many appliactions.

ANSI_WARNINGS - the most important effect is that you get an error if
you try to store a value that does not fir into a char/binary column.
It does affect some application, but there is no dramatic issue. The
other things caused by ANSI_WARNINGS is likely to have even lower impact.

ARITHABORT - Few would probably complain that their division with zero
yields an error (this also comes with ANSI_WARNINGS). Most probably
find it a good thing.

QUOTED_IDENTIFIER - Now, here is one that with a huge impact, since
strings quoted by " suddenly became identifiers. Mitigating, though, is
that this can be fixed mechanically.

ANSI_NULLS - This is an setting that should have absolutely no effect on
properly written code. @x = NULL was wrong in 4.x days, it has always
been wrong, it was just that Sybase out of folly handled NULL as equal
to NULL. But of course, a system where the programmers did not have
understanding of NULL values takes a serious toll here. But as I said
the Best Practice Analyzer can help you out here.

CONCAT_NULL_YIELDS_NULL - Exactly the same thing applies here: on a
properly implemented system, this is not an issue.

....................

IMPLICIT_TRANSACTIONS - Now here is one! This option is OFF by default,
and had MS made this the default, about every application out there would
have succumbed. Right or wrong, auto-commit has always been the way Sybase
and SQL Server has gone.

CURSOR_CLOSE_ON_COMMIT - This option is OFF by default as well. Since
cursors are something you should not use anyway, it should have less
impact. But the whole idea sounds corny to me. I might have a transaction
for each iteration in the cursor. Certainly I don't want it close.

So Microsoft acted judiciously when they decided what should be on by
default and what should not. ANSI_NULLS and CONCAT_NULL_YIELDS_NULL
may have a great impact your code, but let me again stress that the
original defaults were just plain wrong and indefensible to have in
an engine to aspire to be an enterprise DBMS.

Microsoft has proven time and time again that they simply don't give a
damn about the harm they inflict on their customers every time they do
that.

Since I have good contacts with the SQL Server team, I can assure you
that they are very anxious about compability. Very anxious.

But if you have ever worked with product development, you also know that
sometimes maintaining old functionality can be a true burden. (If you
work for a site where you only have one production environment, this is
not an issue for you. You can rip things out as you please.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Richard Romley (rr*****@optonline.net) writes:

I have in my hand a SQL Server 1.0 manual dated 1989. Across the top of
the front cover it says "Microsoft" (and Ashton-Tate <g>). If the
product did absolutely silly things Microsoft never should have put
their name on it - and then maintained the *faulty* logic for 10 years
before deciding, all of a sudden, that the behavior was suddenly
unacceptable.
You seem to think that Microsoft is some being in itself. It isn't.
Microsoft consists of people. And Microsoft 1989 was a very different
company from Microsoft 2004.

In the 1980s Microsoft did not have the resources to develop a DB
engine themselves, so they hooked up an available partner. That was
Sybase (and Ashton-Tate). I don't know what alternatives they had, but
I doubt that for instance Oracle was among them. The choice could not
be made technical qualifications only.

Also, you should keep in mind that there was less of standards in SQL
than there is today. SQL itself was not universal as a query language.
In 1989 my DBMS was DEC/Rdb and the query language was RDO.

Let me also remind you that it is extremely unfair to slam the SQL
Server team for events of 1989, as all MS did in those days was to
port to OS/2, and had very little influence of the features. There
wasn't any SQL Server team of today in 1989.
In any case, in addition to the *absolutely silly things* that it did, it
also did perfectly reasonable things. One small example - the original
PATINDEX function returned a 0 if a match was made beginning with the 1st
character of a string. Then somebody noticed that the CHARINDEX function
returned a 1 on a 1st character match. Thinking that it would be nice for
these 2 similar functions to work the same way, they simply changed the
PATINDEX function.
Are you sure that Microsoft did that change? I checked some online manuals
I have of System 10, which says this about patindex:

Returns an integer representing the starting position of the first
occurrence of pattern in the specified character expression, or a
zero if pattern is not found. By default, patindex returns the offset
in characters; to return the offset in bytes (multibyte character
strings), specify using bytes.

The text is a bit confused, since it talks about both starting position
and offset. The text for 12.5 is on
http://manuals.sybase.com:80/onlineb...eric__BookView
also mixes offset and starting position, but example 4 makes it clear
that the return value is > 0 for a match in position 1.

So if there was a change, maybe you should blame Sybase for it.
A more recent example was a *feature* introduced in release 7. Somebody
decided that the ARITHABORT command was really only to be taken seriously
for SELECT statements and should be ignored for INSERT and UPDATE
statements. This was also (initially) an undocumented feature. This really
wasn't very nice for those of us who process million row inserts at night
which include arithmetic calculations on some of the columns. Microsoft
refused to fix this until SQL 2000. That one *feature* alone made 7.0 an
unusable product for us.
I'm sorry, but you are wrong. I ran this repro on SQL7 SP2 and SQL 6.5 SP5:

CREATE TABLE yxa (a int NOT NULL,
b smallint NOT NULL)
go
SET ARITHABORT ON
SET ANSI_WARNINGS OFF -- Or ON
go
INSERT yxa (a, b) VALUES (100000, 234)
INSERT yxa (a, b) SELECT b, a FROM yxa
INSERT yxa (a, b) VALUES (1, 251)
go
INSERT yxa (a, b) VALUES (100000, 0)
INSERT yxa (a, b) SELECT a/b, 9 FROM yxa
INSERT yxa (a, b) VALUES (1, 251)
go
SELECT * FROM yxa
go
DROP TABLE yxa

The output was identical. Maybe it did not work in SQL7 RTM, which I
assum it was a bug. Bugs happens in the best families.

the keyword is ANSI. Microsoft wanted their product to adhere more to > > ANSI standards<<

Erland, that is Microsoft's line and the excuse they use every time they
deliberately break something. The bottom line here is they are very
selective about WHICH ANSI rules they choose to implement and which they
conveniently ignore. If you don't believe it, call up your friends on
the development team and remind them that FROM clauses are illegal in
UPDATE and DELETE statements and they really shouldn't allow this
non-ANSI behavior in their product. Be sure to report back what they
say.

Sigh. I posted a long list of ANSI options with a through discussion of
which are now the defaults (if you connect with ODBC or OLE DB), and
pointed out that some of the ANSI settings are ignored. For very good
reasons. You don't have to tell me that they are selective. Of course
they are keeping the good features like FROM for UPDATE.

But what you don't seem to get is that ANSI_NULLS and
CONCAT_NULLS_YIELDS_NULL are about fundamental things in a relational
database. They *had* to change it.

But, note that the change was made in a way that it did not affect
This is my favorite. How many products have stored procedures written in
TSQL?
Many applications have SQL sent from the client. Not everything is
stored procedures.
When's the last time you tried to port a SQL Server stored procedure to
any other database? I just love hearing about portability!
For some people, like me, portability is a non-issue. For other people
it's extremely important. If you can mandate which customer can use, you
can ignore being portable. If the customer decides the engine, you
have to be portable.

And if you think I'm just driveling, think SAP and other big apps. If
you can get SAP to run on your engine, you have increased your income
potential significantly.
Microsoft simply has a terrible track record when it comes to backward
compatibility. I've been burned so many times you'll never convince me
otherwise.
As I said, I know that the SQL Server team is very anxious about
backward compatibility. Sometimes, yes, they do make changes which
breaks existing code, but often then because this code relied on
behaviour that never was correct. And in such cases, you can get
back the old behaviour by setting the compatibility level for the
database to lower than one of the current product.

Oh, probably not for optimizer changes. A change in the optimizer can
be of benefit to many, but a disaster to some. Then again, that is
not unique to SQL Server, but to about any DBMS product.
In any case, what started this whole discussion was a query example that
only worked with defaults configured a particular way.

Not any particular random way, but *the* way a proper DBMS should
behave. You seem to be missing this all the time.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Richard Romley]

>>You seem to think that Microsoft is some being in itself. It isn't.
Microsoft consists of people. And Microsoft 1989 was a very different
company from Microsoft 2004.<<

I'm sorry Erland. I completely disagree. The way the company treats its
customers is dictated by senior management and, by observation, it has never
changed. It makes no difference who the employees are at any given time. You
would never tolerate an explanation like that from your phone company, auto
manufacturer, or anyone else. We've been conditioned to tolerate grief from
software that we would never accept from anyplace else. I'm sure you've seen
this... http://www.vbrad.com/Misc/hum_ms_cars.htm

...it is extremely unfair to slam the SQL Server team for events of 1989...<<
You're right, it is. Because of the time frame it was a bad example. It just
happened to cause me an enormous amount of grief so I remember it well. If
it were an isolated event it wouldn't be worth discussing. Unfortunately it
isn't.

Are you sure that Microsoft did that change?<< I have no way of knowing who was controlling the software when the decision
was made to make that change. I can assure you it happened. And I clearly
remember that the change wasn't even mentioned in the release notes. But
you're right - this particular incident happened a long time ago.
Somebody decided that the ARITHABORT command was really only to be taken seriously for SELECT statements and should be ignored for INSERT and UPDATE
statements.<<
I'm sorry, but you are wrong.<<
Sorry for the confusion Erland, but I am NOT wrong. You have the condition
backward in your test. The problem is that "set arithabort OFF" is ignored
for INSERT and UPDATE statements. It's only respected for SELECT statements.

Run this script and you will see. This works fine in all releases of SQL
server EXCEPT 7. To the best of my knowledge Microsoft never publicly
acknowledged this. We had many meetings over it. At first they denied it.
They then acknowledged it and agreed to fix it - then changed their mind and
refused to fix it until SQL 2000 - which is what happened.
CREATE TABLE yxa
(
a int NULL ,
b smallint NULL
)
go
SET ARITHABORT OFF
SET ARITHIGNORE ON
go
INSERT yxa (a, b) VALUES (100000, 234)
INSERT yxa (a, b) SELECT b, a FROM yxa
INSERT yxa (a, b) VALUES (1, 251)
go
INSERT yxa (a, b) VALUES (100000, 0)
INSERT yxa (a, b) SELECT a/b, 9 FROM yxa
INSERT yxa (a, b) VALUES (1, 251)
go
SELECT * FROM yxa
go
DROP TABLE yxa
go

There were many people trying to figure out what they were doing wrong and
trying to work around this. There WAS no practical work-around.

Here are a few newsgroup posts I just found...

http://groups.google.com/groups?hl=e...8%40decath.com

http://groups.google.com/groups?hl=e...ews.tesser.com

http://groups.google.com/groups?hl=e...%40cppssbbsa03

What always bugged me about this is that I just can't believe that it wasn't
a simple fix to correct this. Somebody, for whatever reason, added this test
and they simply refused to correct it. Given that we are one of Microsoft's
larger SQL Server customers (275,000 employees), it was simply astonishing
to observe Microsoft's arrogance. When the PATINDEX problem occurred I was
working for a company with about 20 employees. At the time I assumed that
was the reason for the lack of cooperation. I have since learned it doesn't
matter who you are.
Sigh. I posted a long list of ANSI options...<< You did - and it wasn't necessary. I can assure you I'm familar with them.
I'm sorry but last night's post was made at around 1:00AM and my alarm goes
off at 5:30. I didn't have the strength - nor did I think it was necessary -
to go through the list.
...some of the ANSI settings are ignored. For very good reasons...<<
Ah! We're making progress! All this time I thought the ANSI spec was the
Bible - to work toward at all costs. Now it turns out that parts of the spec
really aren't very good after all - so we'll just ignore the *bad* parts! I
wonder what the folks who wrote the spec would think about that.

Erland, what is this instruction supposed to do if col1 is NOT the primary
key of table b?

update a
set a.col2 = b.col2
from a,b
where a.col1 = b.col1

Do you think this instruction makes sense...

select col1
from tbl
order by col2
How about CASE statements in GROUP BY clauses? in ORDER BY clauses?

This stuff all works in SQL Server. People write code like this every day.
Will it work in the next release of SQL Server? Who knows? None of it is
ANSI compliant. It all depends on whether somebody in development thinks
these are *good* rules or *bad* rules (now that we've established that both
exist!). What if the guy who makes this decision leaves the company and is
replaced with somebody who disagrees with him. Don't you see - you just
can't keep changing the rules and breaking people's code while quoting ANSI
compliance - not when you are deliberately ignoring the parts you don't
like. It is exactly that kind of thinking that causes all the resentment.
Think about it.

Richard

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Richard Romley (rr*****@optonline.net) writes:

I have in my hand a SQL Server 1.0 manual dated 1989. Across the top of
the front cover it says "Microsoft" (and Ashton-Tate <g>). If the
product did absolutely silly things Microsoft never should have put
their name on it - and then maintained the *faulty* logic for 10 years
before deciding, all of a sudden, that the behavior was suddenly
unacceptable.
You seem to think that Microsoft is some being in itself. It isn't.
Microsoft consists of people. And Microsoft 1989 was a very different
company from Microsoft 2004.

In the 1980s Microsoft did not have the resources to develop a DB
engine themselves, so they hooked up an available partner. That was
Sybase (and Ashton-Tate). I don't know what alternatives they had, but
I doubt that for instance Oracle was among them. The choice could not
be made technical qualifications only.

Also, you should keep in mind that there was less of standards in SQL
than there is today. SQL itself was not universal as a query language.
In 1989 my DBMS was DEC/Rdb and the query language was RDO.

Let me also remind you that it is extremely unfair to slam the SQL
Server team for events of 1989, as all MS did in those days was to
port to OS/2, and had very little influence of the features. There
wasn't any SQL Server team of today in 1989.

In any case, in addition to the *absolutely silly things* that it did,

it also did perfectly reasonable things. One small example - the original
PATINDEX function returned a 0 if a match was made beginning with the 1st character of a string. Then somebody noticed that the CHARINDEX function
returned a 1 on a 1st character match. Thinking that it would be nice for these 2 similar functions to work the same way, they simply changed the
PATINDEX function.

Are you sure that Microsoft did that change? I checked some online manuals
I have of System 10, which says this about patindex:

Returns an integer representing the starting position of the first
occurrence of pattern in the specified character expression, or a
zero if pattern is not found. By default, patindex returns the offset
in characters; to return the offset in bytes (multibyte character
strings), specify using bytes.

The text is a bit confused, since it talks about both starting position
and offset. The text for 12.5 is on

http://manuals.sybase.com:80/onlineb...eric__BookView also mixes offset and starting position, but example 4 makes it clear
that the return value is > 0 for a match in position 1.

So if there was a change, maybe you should blame Sybase for it.

A more recent example was a *feature* introduced in release 7. Somebody
decided that the ARITHABORT command was really only to be taken seriously for SELECT statements and should be ignored for INSERT and UPDATE
statements. This was also (initially) an undocumented feature. This really wasn't very nice for those of us who process million row inserts at night which include arithmetic calculations on some of the columns. Microsoft
refused to fix this until SQL 2000. That one *feature* alone made 7.0 an
unusable product for us.
I'm sorry, but you are wrong. I ran this repro on SQL7 SP2 and SQL 6.5

SP5:
CREATE TABLE yxa (a int NOT NULL,
b smallint NOT NULL)
go
SET ARITHABORT ON
SET ANSI_WARNINGS OFF -- Or ON
go
INSERT yxa (a, b) VALUES (100000, 234)
INSERT yxa (a, b) SELECT b, a FROM yxa
INSERT yxa (a, b) VALUES (1, 251)
go
INSERT yxa (a, b) VALUES (100000, 0)
INSERT yxa (a, b) SELECT a/b, 9 FROM yxa
INSERT yxa (a, b) VALUES (1, 251)
go
SELECT * FROM yxa
go
DROP TABLE yxa

The output was identical. Maybe it did not work in SQL7 RTM, which I
assum it was a bug. Bugs happens in the best families.
the keyword is ANSI. Microsoft wanted their product to adhere more to >

ANSI standards<<

Erland, that is Microsoft's line and the excuse they use every time they
deliberately break something. The bottom line here is they are very
selective about WHICH ANSI rules they choose to implement and which they
conveniently ignore. If you don't believe it, call up your friends on
the development team and remind them that FROM clauses are illegal in
UPDATE and DELETE statements and they really shouldn't allow this
non-ANSI behavior in their product. Be sure to report back what they
say.

Sigh. I posted a long list of ANSI options with a through discussion of
which are now the defaults (if you connect with ODBC or OLE DB), and
pointed out that some of the ANSI settings are ignored. For very good
reasons. You don't have to tell me that they are selective. Of course
they are keeping the good features like FROM for UPDATE.

But what you don't seem to get is that ANSI_NULLS and
CONCAT_NULLS_YIELDS_NULL are about fundamental things in a relational
database. They *had* to change it.

But, note that the change was made in a way that it did not affect

This is my favorite. How many products have stored procedures written in
TSQL?

Many applications have SQL sent from the client. Not everything is
stored procedures.

When's the last time you tried to port a SQL Server stored procedure to
any other database? I just love hearing about portability!

For some people, like me, portability is a non-issue. For other people
it's extremely important. If you can mandate which customer can use, you
can ignore being portable. If the customer decides the engine, you
have to be portable.

And if you think I'm just driveling, think SAP and other big apps. If
you can get SAP to run on your engine, you have increased your income
potential significantly.

Microsoft simply has a terrible track record when it comes to backward
compatibility. I've been burned so many times you'll never convince me
otherwise.

As I said, I know that the SQL Server team is very anxious about
backward compatibility. Sometimes, yes, they do make changes which
breaks existing code, but often then because this code relied on
behaviour that never was correct. And in such cases, you can get
back the old behaviour by setting the compatibility level for the
database to lower than one of the current product.

Oh, probably not for optimizer changes. A change in the optimizer can
be of benefit to many, but a disaster to some. Then again, that is
not unique to SQL Server, but to about any DBMS product.

In any case, what started this whole discussion was a query example that
only worked with defaults configured a particular way.

Not any particular random way, but *the* way a proper DBMS should
behave. You seem to be missing this all the time.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Richard Romley]

>>You seem to think that Microsoft is some being in itself. It isn't.
Microsoft consists of people. And Microsoft 1989 was a very different
company from Microsoft 2004.<<

I'm sorry Erland. I completely disagree. The way the company treats its
customers is dictated by senior management and, by observation, it has never
changed. It makes no difference who the employees are at any given time. You
would never tolerate an explanation like that from your phone company, auto
manufacturer, or anyone else. We've been conditioned to tolerate grief from
software that we would never accept from anyplace else. I'm sure you've seen
this... http://www.vbrad.com/Misc/hum_ms_cars.htm

...it is extremely unfair to slam the SQL Server team for events of 1989...<<
You're right, it is. Because of the time frame it was a bad example. It just
happened to cause me an enormous amount of grief so I remember it well. If
it were an isolated event it wouldn't be worth discussing. Unfortunately it
isn't.
Are you sure that Microsoft did that change?<< I have no way of knowing who was controlling the software when the decision
was made to make that change. I can assure you it happened. And I clearly
remember that the change wasn't even mentioned in the release notes. But
you're right - this particular incident happened a long time ago.
Somebody decided that the ARITHABORT command was really only to be taken seriously for SELECT statements and should be ignored for INSERT and UPDATE
statements.<<
I'm sorry, but you are wrong.<<
Sorry for the confusion Erland, but I am NOT wrong. You have the condition
backward in your test. The problem is that "set arithabort OFF" is ignored
for INSERT and UPDATE statements. It's only respected for SELECT statements.

Run this script and you will see. This works fine in all releases of SQL
server EXCEPT 7. To the best of my knowledge Microsoft never publicly
acknowledged this. We had many meetings over it. At first they denied it.
They then acknowledged it and agreed to fix it - then changed their mind and
refused to fix it until SQL 2000 - which is what happened.
CREATE TABLE yxa
(
a int NULL ,
b smallint NULL
)
go
SET ARITHABORT OFF
SET ARITHIGNORE ON
go
INSERT yxa (a, b) VALUES (100000, 234)
INSERT yxa (a, b) SELECT b, a FROM yxa
INSERT yxa (a, b) VALUES (1, 251)
go
INSERT yxa (a, b) VALUES (100000, 0)
INSERT yxa (a, b) SELECT a/b, 9 FROM yxa
INSERT yxa (a, b) VALUES (1, 251)
go
SELECT * FROM yxa
go
DROP TABLE yxa
go

There were many people trying to figure out what they were doing wrong and
trying to work around this. There WAS no practical work-around.

Here are a few newsgroup posts I just found...

http://groups.google.com/groups?hl=e...8%40decath.com

http://groups.google.com/groups?hl=e...ews.tesser.com

http://groups.google.com/groups?hl=e...%40cppssbbsa03

What always bugged me about this is that I just can't believe that it wasn't
a simple fix to correct this. Somebody, for whatever reason, added this test
and they simply refused to correct it. Given that we are one of Microsoft's
larger SQL Server customers (275,000 employees), it was simply astonishing
to observe Microsoft's arrogance. When the PATINDEX problem occurred I was
working for a company with about 20 employees. At the time I assumed that
was the reason for the lack of cooperation. I have since learned it doesn't
matter who you are.
Sigh. I posted a long list of ANSI options...<< You did - and it wasn't necessary. I can assure you I'm familar with them.
I'm sorry but last night's post was made at around 1:00AM and my alarm goes
off at 5:30. I didn't have the strength - nor did I think it was necessary -
to go through the list.
...some of the ANSI settings are ignored. For very good reasons...<<

Ah! We're making progress! All this time I thought the ANSI spec was the
Bible - to work toward at all costs. Now it turns out that parts of the spec
really aren't very good after all - so we'll just ignore the *bad* parts! I
wonder what the folks who wrote the spec would think about that.

Erland, what is this instruction supposed to do if col1 is NOT the primary
key of table b?

update a
set a.col2 = b.col2
from a,b
where a.col1 = b.col1

Do you think this instruction makes sense...

select col1
from tbl
order by col2
How about CASE statements in GROUP BY clauses? in ORDER BY clauses?

This stuff all works in SQL Server. People write code like this every day.
Will it work in the next release of SQL Server? Who knows? None of it is
ANSI compliant. It all depends on whether somebody in development thinks
these are *good* rules or *bad* rules (now that we've established that both
exist!). What if the guy who makes this decision leaves the company and is
replaced with somebody who disagrees with him. Don't you see - you just
can't keep changing the rules and breaking people's code while quoting ANSI
compliance - not when you are deliberately ignoring the parts you don't
like. It is exactly that kind of thinking that causes all the resentment.
Think about it.

Richard

[Richard Romley]

Oops. Sorry for the double post. I got an error the first time saying it had
failed.

[Erland Sommarskog]

Richard Romley (rr*****@optonline.net) writes:

I'm sorry Erland. I completely disagree. The way the company treats its
customers is dictated by senior management and, by observation, it has
never changed.
Or it is the observer that reufeses to change. It's is apparent from
the discussion that you have an opinion, and you are not going to change,
even when proven wrong.

Ever heard of sp_dbcmptlevel? If Microsoft did not care about backward
compatibility, why did they put this in? I have even heard it being
considered in one case to keep incorrectly behaviour in compatibility
level 80 for next version "in case some customers would depend on it".

Tell me, have you ever worked with product development yourself, and
ever had to consider compatibility issues? In that case you would
know that maintaining 100% compatibility in a complex system is about
impossible. If nothing else, it is extremely expenseive, and even at
Microsoft there is a limit of resources.
I have no way of knowing who was controlling the software when the
decision was made to make that change.
Up to 4.x Sybase made all the decisions and Microsoft's influence was
very limited. Kalen Delaneys "Inside SQL Server 2000" includes a chapter
on the history of SQL Server which describes this fairly well. (And even
in 6.0 Sybase shines through. Some constructs that Sybase added in
System 10, made it to 6.0. Presumably they got those with the code
from Sybase.)
Run this script and you will see. This works fine in all releases of SQL
server EXCEPT 7. To the best of my knowledge Microsoft never publicly
acknowledged this. We had many meetings over it. At first they denied it.
They then acknowledged it and agreed to fix it - then changed their mind
and refused to fix it until SQL 2000 - which is what happened.
I have run your script on SQL 6.5 SP2, SQL7 SP2, SQL 2000 SP3 and
SQL Express 2005, and the behaviour was exactly the same on all
versions. However, I had to add SET ANSI_WARNINGS OFF to prevent
errors from being raised. Again, this applied to all the versions above.

But it is true that it happens that bugs found are deferred to the
next major release. Why? Again, if you have experience of product
development, you would know. There is a trade-off between the impact
of the bug, available workarounds, and the risks involved with the bug.
It's no good fixing a minor bug and introduce a major one.

Personally, for this particular case, I have never understood the point
with ARITHIGNORE - or ARITHABORT OFF for that matter. I would never
accept that a system that was involved with to run with ARITHIGNORE on.
http://groups.google.com/groups?hl=e...8%40decath.com
I tried this repro too. Same result on all versions I tried with. But
the post was from March 1999, so this obviously related to the RTM
version. SQL 7 RTM had some vile bugs, but then it was also a huge
rewrite from SQL 6.5.
What always bugged me about this is that I just can't believe that it wasn't
a simple fix to correct this. Somebody, for whatever reason, added this test
and they simply refused to correct it.

It may seem simple to you, but it could well be have been a bug that
was due to a combination of factors, and it was not obvious where to
change. And keep in mind, as I said, that SQL7 was a huge rewrite, so
it is not likley that we are not talking some few lines of code someone
added for fun.

Sigh. I posted a long list of ANSI options...<<

You did - and it wasn't necessary. I can assure you I'm familar with them.

Ah! We're making progress! All this time I thought the ANSI spec was the
Bible - to work toward at all costs.

Maybe you should have gone through my list of ANSI options anyway. It
would have been clear already to you it never was a talk of a bible, and
you could have spared us your sarcasms.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Richard Romley]

>>It's is apparent from the discussion that you have an opinion, and you are
not going to change, even when proven wrong.<<

That's pretty presumptuous on your part, don't you think? I can make exactly
the same argument about you. Let me ask you something: When you're involved
in a debate with someone who *refuses* to see your point of view, why is it
the OTHER guy who is being stubborn?

Tell me, have you ever worked with product development yourself...<
Since you've asked several times I will answer your question: Yes, I've
worked in product development for almost 40 years. In the old days I was
involved in flight simulation. I designed and built the first digital
simulator for a commercial inertial navigation system. I designed and built
the VAMP (variable anamorphic motion picture) visual system used (at that
time) for flight training on many of the world's airlines. In fact I have
the fondest memories of the time I spend at the Bromma Flygplats during the
SAS installation. (I wonder if SAS flight training is still there?) So the
next time you fly you can take comfort in the fact that your flight crew
might very well have trained on equipment I designed and built. In recent
years I have been working for the largest financial services company in the
world involved in the design, development, and maintenance of the system
that supports all of its retail branches. So, yes, I have worked with
product development. How about you?

I have run your script on SQL 6.5 SP2, SQL7 SP2, SQL 2000 SP3 and SQL Express 2005, and the behaviour was exactly the same on all versions.<<

Erland, I find that absolutely amazing. I wonder when they fixed it.
Microsoft was adamant that it would never be fixed in SQL 7. That cost them
a lot of money. Nobody ever informed us that it had been fixed.

I would never accept that a system that was involved with to run with ARITHIGNORE on.<<

You are certainly entitled to your opinion. It is way beyond the scope of
this discussion to go into the issues involved. But I'd be careful with my
use of the word *never.* It makes you sound inflexible - kind of like you're
accusing me of being.
...you could have spared us your sarcasms<<
I'm really sorry you feel that way. I was trying to point out the hypocrisy
of Microsoft's position - and the examples I gave spoke to the heart of the
issue. Erland, why is it that if I choose to ignore an ANSI rule I am guilty
of bad design - but when I give examples of Microsoft doing it I am being
sarcastic? I don't understand. The ENTIRE POINT OF THE DISCUSSION is that
SQL Server is LOADED with ANSI violations that won't be removed because
doing so would break the code of virtually the entire customer base. Don't
you then find it a bit disingenuous of Microsoft when they DO change
something that breaks existing code to quote ANSI compliance as
justification for doing so? I am truly sorry (and disappointed) if you find
that to be sarcastic.

I am afraid we are going to have to agree to disagree.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Richard Romley (rr*****@optonline.net) writes:

I'm sorry Erland. I completely disagree. The way the company treats its
customers is dictated by senior management and, by observation, it has
never changed.
Or it is the observer that reufeses to change. It's is apparent from
the discussion that you have an opinion, and you are not going to change,
even when proven wrong.

Ever heard of sp_dbcmptlevel? If Microsoft did not care about backward
compatibility, why did they put this in? I have even heard it being
considered in one case to keep incorrectly behaviour in compatibility
level 80 for next version "in case some customers would depend on it".

Tell me, have you ever worked with product development yourself, and
ever had to consider compatibility issues? In that case you would
know that maintaining 100% compatibility in a complex system is about
impossible. If nothing else, it is extremely expenseive, and even at
Microsoft there is a limit of resources.

I have no way of knowing who was controlling the software when the
decision was made to make that change.

Up to 4.x Sybase made all the decisions and Microsoft's influence was
very limited. Kalen Delaneys "Inside SQL Server 2000" includes a chapter
on the history of SQL Server which describes this fairly well. (And even
in 6.0 Sybase shines through. Some constructs that Sybase added in
System 10, made it to 6.0. Presumably they got those with the code
from Sybase.)

Run this script and you will see. This works fine in all releases of SQL
server EXCEPT 7. To the best of my knowledge Microsoft never publicly
acknowledged this. We had many meetings over it. At first they denied

it. They then acknowledged it and agreed to fix it - then changed their mind
and refused to fix it until SQL 2000 - which is what happened.

I have run your script on SQL 6.5 SP2, SQL7 SP2, SQL 2000 SP3 and
SQL Express 2005, and the behaviour was exactly the same on all
versions. However, I had to add SET ANSI_WARNINGS OFF to prevent
errors from being raised. Again, this applied to all the versions above.

But it is true that it happens that bugs found are deferred to the
next major release. Why? Again, if you have experience of product
development, you would know. There is a trade-off between the impact
of the bug, available workarounds, and the risks involved with the bug.
It's no good fixing a minor bug and introduce a major one.

Personally, for this particular case, I have never understood the point
with ARITHIGNORE - or ARITHABORT OFF for that matter. I would never
accept that a system that was involved with to run with ARITHIGNORE on.

http://groups.google.com/groups?hl=e...8%40decath.com
I tried this repro too. Same result on all versions I tried with. But
the post was from March 1999, so this obviously related to the RTM
version. SQL 7 RTM had some vile bugs, but then it was also a huge
rewrite from SQL 6.5.

What always bugged me about this is that I just can't believe that it

wasn't a simple fix to correct this. Somebody, for whatever reason, added this test and they simply refused to correct it.

It may seem simple to you, but it could well be have been a bug that
was due to a combination of factors, and it was not obvious where to
change. And keep in mind, as I said, that SQL7 was a huge rewrite, so
it is not likley that we are not talking some few lines of code someone
added for fun.
Sigh. I posted a long list of ANSI options...<<

You did - and it wasn't necessary. I can assure you I'm familar with them.
Ah! We're making progress! All this time I thought the ANSI spec was the
Bible - to work toward at all costs.

Maybe you should have gone through my list of ANSI options anyway. It
would have been clear already to you it never was a talk of a bible, and
you could have spared us your sarcasms.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Richard Romley (rr*****@optonline.net) writes:

That's pretty presumptuous on your part, don't you think? I can make
exactly the same argument about you. Let me ask you something: When
you're involved in a debate with someone who *refuses* to see your point
of view, why is it the OTHER guy who is being stubborn?
The main problem I have with you is that you throw a lot of Microsoft
for no good reason. It does give, as they say, a unprofessional impression.
Initially I ignored that in my responses and focused on the technical
details. But you preferred to skip over a lot of that.
So, yes, I have worked with product development. How about you?
I work for a small ISV, so you can guess the rest. Of course, since
we have a very small customer base, our situation is quite different
from Microsoft. But I do recognize the problems. "Do we really want to
keep this obscure feature at any cost." It would surprise me if you
never have run into that dilemma yourself.
I'm really sorry you feel that way. I was trying to point out the
hypocrisy of Microsoft's position - and the examples I gave spoke to the
heart of the issue. Erland, why is it that if I choose to ignore an ANSI
rule I am guilty of bad design - but when I give examples of Microsoft
doing it I am being sarcastic?
Because handling NULL as an unknown value which is not equal to another
unknown value is a very basic concept in relational databases.
I don't understand. The ENTIRE POINT OF THE DISCUSSION is that
SQL Server is LOADED with ANSI violations that won't be removed because
doing so would break the code of virtually the entire customer base.

Many non-ANSI features will remain there forever, and Microsoft will
also add features that are not in ANSI. But really bad ones will go
away. Extraneous columns with GROUP BY went away already in 6.0. *= and
=* will also go away some day, the sooner the better. (It has been
deprecated in Books Online in the last two versions.)

And, mind you, when Microsoft changed the defaults they did that
depending on the client library. If you connect with DB-Library
everything is off. Then again, there will be the day when DB-Library
will no longer be with us.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp