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]

Re: Difficult Query: is this possible in SQL?


"Robert Brown" <robertbrown197 1@yahoo.com> wrote in message
news:240a4d09.0 407021517.1ee87 cdb@posting.goo gle.com...[color=blue]
> 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?[/color]

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]

Re: Difficult Query: is this possible in SQL?

"Robert Brown" <robertbrown197 1@yahoo.com> wrote in message
news:240a4d09.0 407021517.1ee87 cdb@posting.goo gle.com...[color=blue]
> 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[/color]

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--]

Re: Difficult Query: is this possible in SQL?

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--]

Re: Difficult Query: is this possible in SQL?

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]

Re: Difficult Query: is this possible in SQL?

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,NUL L,20 UNION ALL
select 3,NULL,9,82,25 UNION ALL
select 4,'BLUE',NULL,6 7,NULL UNION ALL
select 5,'GRAY',NULL,N ULL,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,h gt), Foobar as f


Richard




robertbrown1971 @yahoo.com (Robert Brown) wrote in message news:<240a4d09. 0407021517.1ee8 7cdb@posting.go ogle.com>...[color=blue]
> 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[/color]


robertbrown1971 @yahoo.com (Robert Brown) wrote in message news:<240a4d09. 0407021517.1ee8 7cdb@posting.go ogle.com>...[color=blue]
> 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[/color]

[Aaron W. West]

Re: Difficult Query: is this possible in SQL?

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(c ast(level as char(1))+color) ,2,10)
,length = cast(substring( max(cast(level as char(1))+cast(l ength as
char(9))),2,9) as integer)
,width = cast(substring( max(cast(level as char(1))+cast(w idth as
char(9))),2,9) as integer)
,hgt = cast(substring( max(cast(level as char(1))+cast(h gt as char(9))),2,9)
as integer)
from Foobar

-aaron


"Richard" <rromley@optonl ine.net> wrote in message
news:bfbb57f7.0 407050932.4cc43 ea7@posting.goo gle.com...
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,NUL L,20 UNION ALL
select 3,NULL,9,82,25 UNION ALL
select 4,'BLUE',NULL,6 7,NULL UNION ALL
select 5,'GRAY',NULL,N ULL,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,h gt), Foobar as f


Richard




robertbrown1971 @yahoo.com (Robert Brown) wrote in message
news:<240a4d09. 0407021517.1ee8 7cdb@posting.go ogle.com>...[color=blue]
> 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[/color]


robertbrown1971 @yahoo.com (Robert Brown) wrote in message
news:<240a4d09. 0407021517.1ee8 7cdb@posting.go ogle.com>...[color=blue]
> 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[/color]

[Richard]

Re: Difficult Query: is this possible in SQL?

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(c ast(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(l ength 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(w idth 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(h gt 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" <tallpeak@hotma il.NO.SPAM> wrote in message news:<TOGdnVYRE fTFQHTdRVn-tw@speakeasy.ne t>...[color=blue]
> 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(c ast(level as char(1))+color) ,2,10)
> ,length = cast(substring( max(cast(level as char(1))+cast(l ength as
> char(9))),2,9) as integer)
> ,width = cast(substring( max(cast(level as char(1))+cast(w idth as
> char(9))),2,9) as integer)
> ,hgt = cast(substring( max(cast(level as char(1))+cast(h gt as char(9))),2,9)
> as integer)
> from Foobar
>
> -aaron
>
>
> "Richard" <rromley@optonl ine.net> wrote in message
> news:bfbb57f7.0 407050932.4cc43 ea7@posting.goo gle.com...
> 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,NUL L,20 UNION ALL
> select 3,NULL,9,82,25 UNION ALL
> select 4,'BLUE',NULL,6 7,NULL UNION ALL
> select 5,'GRAY',NULL,N ULL,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,h gt), Foobar as f
>
>
> Richard
>
>
>
>
> robertbrown1971 @yahoo.com (Robert Brown) wrote in message
> news:<240a4d09. 0407021517.1ee8 7cdb@posting.go ogle.com>...[color=green]
> > 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[/color]
>
>
> robertbrown1971 @yahoo.com (Robert Brown) wrote in message
> news:<240a4d09. 0407021517.1ee8 7cdb@posting.go ogle.com>...[color=green]
> > 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[/color][/color]

[Dario]

Re: Difficult Query: is this possible in SQL?

robertbrown1971 @yahoo.com (Robert Brown) wrote in message news:<240a4d09. 0407021517.1ee8 7cdb@posting.go ogle.com>...[color=blue]
> 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[/color]


SELECT * FROM
(
select ROW_NUMBER() OVER (ORDER BY L DESC) RN,
first_value(col or) over ( order by case when color is null
then -1 else rownum end desc),
first_value(len gth) over ( order by case when length is null
then -1 else rownum end desc),
first_value(wid th) 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]

Re: Difficult Query: is this possible in SQL?

robertbrown1971 @yahoo.com (Robert Brown) wrote in message news:<240a4d09. 0407021517.1ee8 7cdb@posting.go ogle.com>...[color=blue]
> 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[/color]

Oracle solution using analytical functions could be:

SELECT * FROM
(
select ROW_NUMBER() OVER (ORDER BY L DESC) RN,
first_value(col or) over ( order by case when color is null
then -1 else rownum end desc),
first_value(len gth) over ( order by case when length is null
then -1 else rownum end desc),
first_value(wid th) 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]

Re: Difficult Query: is this possible in SQL?

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_yie lds_null
SET

Now if I:
SET CONCAT_NULL_YIE LDS_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_YIE LDS_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_YIE LDS_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" <rromley@optonl ine.net> wrote in message
news:bfbb57f7.0 407051925.4a4fa a2c@posting.goo gle.com...
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(c ast(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(l ength 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(w idth 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(h gt 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" <tallpeak@hotma il.NO.SPAM> wrote in message
news:<TOGdnVYRE fTFQHTdRVn-tw@speakeasy.ne t>...[color=blue]
> There's a technique of taking a max of two values concatenated then taking[/color]
a[color=blue]
> substring, which avoids the join needed in the below techniques.
>
> Try this:
>
> select color = substring(max(c ast(level as char(1))+color) ,2,10)
> ,length = cast(substring( max(cast(level as char(1))+cast(l ength as
> char(9))),2,9) as integer)
> ,width = cast(substring( max(cast(level as char(1))+cast(w idth as
> char(9))),2,9) as integer)
> ,hgt = cast(substring( max(cast(level as char(1))+cast(h gt as[/color]
char(9))),2,9)[color=blue]
> as integer)
> from Foobar
>
> -aaron
>
>
> "Richard" <rromley@optonl ine.net> wrote in message
> news:bfbb57f7.0 407050932.4cc43 ea7@posting.goo gle.com...
> 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,NUL L,20 UNION ALL
> select 3,NULL,9,82,25 UNION ALL
> select 4,'BLUE',NULL,6 7,NULL UNION ALL
> select 5,'GRAY',NULL,N ULL,NULL
>
>
>
> select
> (select color from Foobar where level =
> (select max(level) from Foobar where color is not null)) as color[/color]
,[color=blue]
> (select length from Foobar where level =
> (select max(level) from Foobar where length is not null)) as length[/color]
,[color=blue]
> (select width from Foobar where level =
> (select max(level) from Foobar where width is not null)) as width[/color]
,[color=blue]
> (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,h gt), Foobar as f
>
>
> Richard
>
>
>
>
> robertbrown1971 @yahoo.com (Robert Brown) wrote in message
> news:<240a4d09. 0407021517.1ee8 7cdb@posting.go ogle.com>...[color=green]
> > 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[/color]
>
>
> robertbrown1971 @yahoo.com (Robert Brown) wrote in message
> news:<240a4d09. 0407021517.1ee8 7cdb@posting.go ogle.com>...[color=green]
> > 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[/color][/color]

[Richard Romley]

Re: Difficult Query: is this possible in SQL?

>>SET CONCAT_NULL_YIE LDS_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" <tallpeak@hotma il.NO.SPAM> wrote in message
news:L9mdndTaBr k5wXfdRVn-ig@speakeasy.ne t...[color=blue]
> 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_yie lds_null
> SET
>
> Now if I:
> SET CONCAT_NULL_YIE LDS_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[/color]
change[color=blue]
> those options, and consider it generally bad practice to (unless you[/color]
change[color=blue]
> them temporarily inside a transaction and change them back when done? But
> it's still bad practice, because some code within the transaction might[/color]
call[color=blue]
> something like this that is dependent on such settings...)
>
> Hmm, I suppose it's safer not to rely on such settings to be correct...[/color]
But[color=blue]
> this setting is usually on, anyway. Note BOL:
>
> "SET CONCAT_NULL_YIE LDS_NULL must be set to ON when you create or[/color]
manipulate[color=blue]
> indexes on computed columns or indexed views."
>
> This reply is MS SQL specific so I removed the other newsgroups. Then[/color]
again,[color=blue]
> if CONCAT_NULL_YIE LDS_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" <rromley@optonl ine.net> wrote in message
> news:bfbb57f7.0 407051925.4a4fa a2c@posting.goo gle.com...
> 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(c ast(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(l ength 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(w idth 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(h gt 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" <tallpeak@hotma il.NO.SPAM> wrote in message
> news:<TOGdnVYRE fTFQHTdRVn-tw@speakeasy.ne t>...[color=green]
> > There's a technique of taking a max of two values concatenated then[/color][/color]
taking[color=blue]
> a[color=green]
> > substring, which avoids the join needed in the below techniques.
> >
> > Try this:
> >
> > select color = substring(max(c ast(level as char(1))+color) ,2,10)
> > ,length = cast(substring( max(cast(level as char(1))+cast(l ength as
> > char(9))),2,9) as integer)
> > ,width = cast(substring( max(cast(level as char(1))+cast(w idth as
> > char(9))),2,9) as integer)
> > ,hgt = cast(substring( max(cast(level as char(1))+cast(h gt as[/color]
> char(9))),2,9)[color=green]
> > as integer)
> > from Foobar
> >
> > -aaron
> >
> >
> > "Richard" <rromley@optonl ine.net> wrote in message
> > news:bfbb57f7.0 407050932.4cc43 ea7@posting.goo gle.com...
> > 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,NUL L,20 UNION ALL
> > select 3,NULL,9,82,25 UNION ALL
> > select 4,'BLUE',NULL,6 7,NULL UNION ALL
> > select 5,'GRAY',NULL,N ULL,NULL
> >
> >
> >
> > select
> > (select color from Foobar where level =
> > (select max(level) from Foobar where color is not null)) as[/color][/color]
color[color=blue]
> ,[color=green]
> > (select length from Foobar where level =
> > (select max(level) from Foobar where length is not null)) as[/color][/color]
length[color=blue]
> ,[color=green]
> > (select width from Foobar where level =
> > (select max(level) from Foobar where width is not null)) as[/color][/color]
width[color=blue]
> ,[color=green]
> > (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,h gt), Foobar as f
> >
> >
> > Richard
> >
> >
> >
> >
> > robertbrown1971 @yahoo.com (Robert Brown) wrote in message
> > news:<240a4d09. 0407021517.1ee8 7cdb@posting.go ogle.com>...[color=darkred]
> > > 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[/color]
> >
> >
> > robertbrown1971 @yahoo.com (Robert Brown) wrote in message
> > news:<240a4d09. 0407021517.1ee8 7cdb@posting.go ogle.com>...[color=darkred]
> > > 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[/color][/color]
>
>[/color]

[Erland Sommarskog]

Re: Difficult Query: is this possible in SQL?

Richard Romley (richardromley@ optonline.net) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Richard Romley]

Re: Difficult Query: is this possible in SQL?

>>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.
[color=blue][color=green]
>>there are several features in SQL Server you cannot use<<[/color][/color]

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" <esquel@sommars kog.se> wrote in message
news:Xns951F17F 8D19BYazorman@1 27.0.0.1...[color=blue]
> Richard Romley (richardromley@ optonline.net) writes:[color=green]
> > Not in my environment it isn't. I run with ANSI NULLS OFF. The[/color][/color]
alternative[color=blue][color=green]
> > is to go back and fix 10-12 years worth of code that was written when[/color][/color]
the[color=blue][color=green]
> > default was the opposite of what it is now. I have learned to never[/color][/color]
assume[color=blue][color=green]
> > 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[/color][/color]
all[color=blue][color=green]
> > over again after every upgrade. If it's specified in the code it will[/color][/color]
work[color=blue][color=green]
> > in spite of Microsoft. I have learned my lesson.[/color]
>
> 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, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: Difficult Query: is this possible in SQL?

Richard Romley (richardromley@ optonline.net) writes:[color=blue]
> 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.[/color]

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_YIE LDS_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_IDENTIFI ER - 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_YIE LDS_NULL - Exactly the same thing applies here: on a
properly implemented system, this is not an issue.

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

IMPLICIT_TRANSA CTIONS - 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_YIE LDS_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.
[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp