Difficulty in implementing

How difficult is it to implement the full text searching capability provided
by SQL server if it wasn't implemented to start with? Currently I have an
application that allows searching based on user profile fields. However
it's not taking advantage of the full text indexing.
How much code change is involved? Thanks.
Very little code changes will be required.
First you need to create a catalog and build the index for the column you
wish to index.
Then you add a contains or freetext clause to your where query, ie:
select col1, col2, col3 from tablename where contains(IndexedColumnName,
'SearchPhrase').
For performance reasons you will want to limit your results set and use
ContainsTable, and possibly put your data you are indexing in a child table.
"Shabam" <blislecp@.hotmail.com> wrote in message
news:_sGdnbj6K8tAqODcRVn-iA@.adelphia.com...
> How difficult is it to implement the full text searching capability
provided
> by SQL server if it wasn't implemented to start with? Currently I have an
> application that allows searching based on user profile fields. However
> it's not taking advantage of the full text indexing.
> How much code change is involved? Thanks.
>
|||Shabam,
That depends upon several factors. What is the exact version & OS platform
that you have SQL Server installed? Could you post the full output of --
SELECT @.@.version -- as this is helpful in understanding your environment.
Could you post the output of the following SQL code?
SELECT FullTextServiceProperty('IsFulltextInstalled')
If the Full-text Search (FTS) components are installed (as they are
installed by default for SQL Server 2000, but not for SQL Server 7.0), you
can use the Full-Text Indexing Wizard (sqlftwiz.exe) and launch this wizard
from the Enterprise Manager and it will walk you though all the necessary
steps for FT Indexing your tables. You should also review Books online (BOL)
and using the search tab, search on "full text" (with the double quotes) as
well as search on CONTAINS and FREETEXT for more information on using FTS.
Regards,
John
"Shabam" <blislecp@.hotmail.com> wrote in message
news:_sGdnbj6K8tAqODcRVn-iA@.adelphia.com...
> How difficult is it to implement the full text searching capability
provided
> by SQL server if it wasn't implemented to start with? Currently I have an
> application that allows searching based on user profile fields. However
> it's not taking advantage of the full text indexing.
> How much code change is involved? Thanks.
>
|||> First you need to create a catalog and build the index for the column you
> wish to index.
> Then you add a contains or freetext clause to your where query, ie:
> select col1, col2, col3 from tablename where contains(IndexedColumnName,
> 'SearchPhrase').
> For performance reasons you will want to limit your results set and use
> ContainsTable, and possibly put your data you are indexing in a child
table.
Why should the data be put in a child table? How does that help with
performance?
Right now, I have a "Users" table. It contains various profile fields.
Some of the fields contain comma-delimited list items (hobbies, separated by
commas). When viewing the user's profile, these hobby items are displayed
and linked to a search, item by item.
I suggested to the programmer it would be much better to put the individual
hobby items in a separate table, linked with an index of the user ID.
However he's saying it would cause more overhead during the display of the
user profile page. What are your thoughts? (I know this is a tangeant from
the original question).
|||Shabam,
Actually, it is not necessary to put the data in a child table and this
alone will not help performance and your programmer is correct as it would
cause more overhead during the display of the user profile page. Depending
upon the number of rows (see below sql code), it is best to keep everything
in one table.
What is important is what is your SQL Server version and determine if the
Full-text Search (FTS) components are installed. Could you post the full
output of the following sql code:
use <your_database_name_here>
go
SELECT @.@.version
SELECT FullTextServiceProperty('IsFulltextInstalled')
SELECT count(*) from Users
EXEC sp_help Users
go
The above info will give me a better understanding of your environment and
allow me to reply with the correct information and estimates of performance
as well as the code necessary to implement FTS. Depending upon the OS
platform (from @.@.version), there may be issues with FTS queries against
column data with comma-delimited list items, unless you use the Neutral
"Language for Word Breaker" or are have SQL Server (version?) installed on
Window Server 2003 or Windows XP.
Thanks,
John
"Shabam" <blislecp@.hotmail.com> wrote in message
news:vIidnfVIBYAy5eDcRVn-3A@.adelphia.com...[vbcol=seagreen]
you
> table.
> Why should the data be put in a child table? How does that help with
> performance?
> Right now, I have a "Users" table. It contains various profile fields.
> Some of the fields contain comma-delimited list items (hobbies, separated
by
> commas). When viewing the user's profile, these hobby items are displayed
> and linked to a search, item by item.
> I suggested to the programmer it would be much better to put the
individual
> hobby items in a separate table, linked with an index of the user ID.
> However he's saying it would cause more overhead during the display of the
> user profile page. What are your thoughts? (I know this is a tangeant
from
> the original question).
>
>
|||The textual content in the parent table will make the "table wider". If this
textual column is involved in other queries, storing it in a child table
will require the use of a join and you will not get performance
improvements. However, if this textual column is not used in most queries or
to resolve queries, you will get better performance by storing it in a child
table, as your remaining parent table will be considerably narrower and
hence more rows can be stored per database page. Consequently SQL Server
will have to go to disk less to return pages.
Also when you are doing a ContainsTable or FreeTextTable query, you can join
on the parent table and not have to query the child table directly at all.
Depending on the number of rows and the size of your tables the performance
improvement can by huge.
If everytime you need to display a record you have to return the contents on
this field, it probably should be in the parent.
You will have to test to see what works best for you. In my experience on
most of the search applications I have worked on, splitting your textual
data which you are indexing into a seperate table is a must. Your results
may vary.
"Shabam" <blislecp@.hotmail.com> wrote in message
news:vIidnfVIBYAy5eDcRVn-3A@.adelphia.com...[vbcol=seagreen]
you
> table.
> Why should the data be put in a child table? How does that help with
> performance?
> Right now, I have a "Users" table. It contains various profile fields.
> Some of the fields contain comma-delimited list items (hobbies, separated
by
> commas). When viewing the user's profile, these hobby items are displayed
> and linked to a search, item by item.
> I suggested to the programmer it would be much better to put the
individual
> hobby items in a separate table, linked with an index of the user ID.
> However he's saying it would cause more overhead during the display of the
> user profile page. What are your thoughts? (I know this is a tangeant
from
> the original question).
>
>

difficult TSQL Problem -

Hello !
I have a difficult TSQL Problem.
Perhaps there is an expert, who can help me ...
I have 2 tables: "WorkingDays" and "Planned Pieces".
Table 1
year - month - workingdays
2004 - 12 - 21
2005 - 01 - 20
2005 - 02 - 19
...
Table 2
customernr - year - plannedpieces
11339 - 2004 - 25000
11442 - 2005 - 36000
...
Now:
I share the plannedpieces proportional on every month:
--PLANZAHLEN
declare @.zaehler int
set @.zaehler = 1
while @.zaehler <= 12
BEGIN
insert FAKT_SALESFOLLOWUP
select p.kdnr,convert(datetime,'01.' + cast(p.jahr as varchar) + '-'
+ cast(@.zaehler as varchar) ,104),p.stueck_ori/12 as planstueck
from xdatplankunde p
set @.zaehler = @.zaehler +1
END
Future:
Based on these 2 tables I want to create a new table (=source for
analysis services) with a row for each month.
But I want to share the year-plannedpieces in the in the proportion of
the workingdays.
Sample:
sum(workingdays) where (year=2005) = 240
February have 19 workingdays
Pieces of Feburary: 25000 / (19/240)
Do you have any idea ?
Thanks
aaapaul
Try,
select
t1.[year],
t1.[month],
t2.customernr,
t2.plannedpieces / nullif((t_ym.sum_workingdays /
nullif(t_y.sum_workingdays * 1.00, 0)), 0)
from
table1 as t1
inner join
table2 as t2
on t1.[year] = t2.[year]
inner join
(
select
[year],
[month],
sum(workingdays) as sum_workingdays_ym
from
table1
group by
[year],
[month]
) as t_ym
on t1.[year] = t_ym.[year] and t1.[month] = t_ym.[month]
inner join
(
select
[year],
sum(workingdays) as sum_workingdays_y
from
table1
group by
[year]
) as t_y
on t1.[year] = t_y.[year];
AMB
"aaapaul" wrote:

> Hello !
> I have a difficult TSQL Problem.
> Perhaps there is an expert, who can help me ...
> I have 2 tables: "WorkingDays" and "Planned Pieces".
> Table 1
> year - month - workingdays
> 2004 - 12 - 21
> 2005 - 01 - 20
> 2005 - 02 - 19
> ...
> Table 2
> customernr - year - plannedpieces
> 11339 - 2004 - 25000
> 11442 - 2005 - 36000
> ...
> Now:
> I share the plannedpieces proportional on every month:
> --PLANZAHLEN
> declare @.zaehler int
> set @.zaehler = 1
> while @.zaehler <= 12
> BEGIN
> insert FAKT_SALESFOLLOWUP
> select p.kdnr,convert(datetime,'01.' + cast(p.jahr as varchar) + '-'
> + cast(@.zaehler as varchar) ,104),p.stueck_ori/12 as planstueck
> from xdatplankunde p
> set @.zaehler = @.zaehler +1
> END
>
> Future:
> Based on these 2 tables I want to create a new table (=source for
> analysis services) with a row for each month.
> But I want to share the year-plannedpieces in the in the proportion of
> the workingdays.
> Sample:
> sum(workingdays) where (year=2005) = 240
> February have 19 workingdays
> Pieces of Feburary: 25000 / (19/240)
> Do you have any idea ?
>
> Thanks
> aaapaul
>
|||Correction,
select
t1.[year],
t1.[month],
t2.customernr,
t2.plannedpieces / nullif((t_ym.sum_workingdays_ym /
nullif(t_y.sum_workingdays_y * 1.00, 0)), 0)
from
table1 as t1
inner join
table2 as t2
on t1.[year] = t2.[year]
inner join
(
select
[year],
[month],
sum(workingdays) as sum_workingdays_ym
from
table1
group by
[year],
[month]
) as t_ym
on t1.[year] = t_ym.[year] and t1.[month] = t_ym.[month]
inner join
(
select
[year],
sum(workingdays) as sum_workingdays_y
from
table1
group by
[year]
) as t_y
on t1.[year] = t_y.[year];
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Try,
> select
> t1.[year],
> t1.[month],
> t2.customernr,
> t2.plannedpieces / nullif((t_ym.sum_workingdays /
> nullif(t_y.sum_workingdays * 1.00, 0)), 0)
> from
> table1 as t1
> inner join
> table2 as t2
> on t1.[year] = t2.[year]
> inner join
> (
> select
> [year],
> [month],
> sum(workingdays) as sum_workingdays_ym
> from
> table1
> group by
> [year],
> [month]
> ) as t_ym
> on t1.[year] = t_ym.[year] and t1.[month] = t_ym.[month]
> inner join
> (
> select
> [year],
> sum(workingdays) as sum_workingdays_y
> from
> table1
> group by
> [year]
> ) as t_y
> on t1.[year] = t_y.[year];
>
> AMB
>
> "aaapaul" wrote:
|||Many Thanks SQLGod !
It works fine.
aaapaul

difficult TSQL Problem -

Hello !
I have a difficult TSQL Problem.
Perhaps there is an expert, who can help me ...
I have 2 tables: "WorkingDays" and "Planned Pieces".
Table 1
year - month - workingdays
2004 - 12 - 21
2005 - 01 - 20
2005 - 02 - 19
...
Table 2
customernr - year - plannedpieces
11339 - 2004 - 25000
11442 - 2005 - 36000
...
Now:
I share the plannedpieces proportional on every month:
--PLANZAHLEN
declare @.zaehler int
set @.zaehler = 1
while @.zaehler <= 12
BEGIN
insert FAKT_SALESFOLLOWUP
select p.kdnr,convert(datetime,'01.' + cast(p.jahr as varchar) + '-'
+ cast(@.zaehler as varchar) ,104),p.stueck_ori/12 as planstueck
from xdatplankunde p
set @.zaehler = @.zaehler +1
END
Future:
Based on these 2 tables I want to create a new table (=source for
analysis services) with a row for each month.
But I want to share the year-plannedpieces in the in the proportion of
the workingdays.
Sample:
sum(workingdays) where (year=2005) = 240
February have 19 workingdays
Pieces of Feburary: 25000 / (19/240)
Do you have any idea ?
Thanks
aaapaulTry,
select
t1.[year],
t1.[month],
t2.customernr,
t2.plannedpieces / nullif((t_ym.sum_workingdays /
nullif(t_y.sum_workingdays * 1.00, 0)), 0)
from
table1 as t1
inner join
table2 as t2
on t1.[year] = t2.[year]
inner join
(
select
[year],
[month],
sum(workingdays) as sum_workingdays_ym
from
table1
group by
[year],
[month]
) as t_ym
on t1.[year] = t_ym.[year] and t1.[month] = t_ym.[month]
inner join
(
select
[year],
sum(workingdays) as sum_workingdays_y
from
table1
group by
[year]
) as t_y
on t1.[year] = t_y.[year];
AMB
"aaapaul" wrote:

> Hello !
> I have a difficult TSQL Problem.
> Perhaps there is an expert, who can help me ...
> I have 2 tables: "WorkingDays" and "Planned Pieces".
> Table 1
> year - month - workingdays
> 2004 - 12 - 21
> 2005 - 01 - 20
> 2005 - 02 - 19
> ...
> Table 2
> customernr - year - plannedpieces
> 11339 - 2004 - 25000
> 11442 - 2005 - 36000
> ...
> Now:
> I share the plannedpieces proportional on every month:
> --PLANZAHLEN
> declare @.zaehler int
> set @.zaehler = 1
> while @.zaehler <= 12
> BEGIN
> insert FAKT_SALESFOLLOWUP
> select p.kdnr,convert(datetime,'01.' + cast(p.jahr as varchar) + '-'
> + cast(@.zaehler as varchar) ,104),p.stueck_ori/12 as planstueck
> from xdatplankunde p
> set @.zaehler = @.zaehler +1
> END
>
> Future:
> Based on these 2 tables I want to create a new table (=source for
> analysis services) with a row for each month.
> But I want to share the year-plannedpieces in the in the proportion of
> the workingdays.
> Sample:
> sum(workingdays) where (year=2005) = 240
> February have 19 workingdays
> Pieces of Feburary: 25000 / (19/240)
> Do you have any idea ?
>
> Thanks
> aaapaul
>|||Correction,
select
t1.[year],
t1.[month],
t2.customernr,
t2.plannedpieces / nullif((t_ym.sum_workingdays_ym /
nullif(t_y.sum_workingdays_y * 1.00, 0)), 0)
from
table1 as t1
inner join
table2 as t2
on t1.[year] = t2.[year]
inner join
(
select
[year],
[month],
sum(workingdays) as sum_workingdays_ym
from
table1
group by
[year],
[month]
) as t_ym
on t1.[year] = t_ym.[year] and t1.[month] = t_ym.[month]
inner join
(
select
[year],
sum(workingdays) as sum_workingdays_y
from
table1
group by
[year]
) as t_y
on t1.[year] = t_y.[year];
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Try,
> select
> t1.[year],
> t1.[month],
> t2.customernr,
> t2.plannedpieces / nullif((t_ym.sum_workingdays /
> nullif(t_y.sum_workingdays * 1.00, 0)), 0)
> from
> table1 as t1
> inner join
> table2 as t2
> on t1.[year] = t2.[year]
> inner join
> (
> select
> [year],
> [month],
> sum(workingdays) as sum_workingdays_ym
> from
> table1
> group by
> [year],
> [month]
> ) as t_ym
> on t1.[year] = t_ym.[year] and t1.[month] = t_ym.[month]
> inner join
> (
> select
> [year],
> sum(workingdays) as sum_workingdays_y
> from
> table1
> group by
> [year]
> ) as t_y
> on t1.[year] = t_y.[year];
>
> AMB
>
> "aaapaul" wrote:
>|||Many Thanks SQLGod !
It works fine.
aaapaul

difficult TSQL Problem -

Hello !
I have a difficult TSQL Problem.
Perhaps there is an expert, who can help me ...
I have 2 tables: "WorkingDays" and "Planned Pieces".
Table 1
year - month - workingdays
2004 - 12 - 21
2005 - 01 - 20
2005 - 02 - 19
...
Table 2
customernr - year - plannedpieces
11339 - 2004 - 25000
11442 - 2005 - 36000
...
Now:
I share the plannedpieces proportional on every month:
--PLANZAHLEN
declare @.zaehler int
set @.zaehler = 1
while @.zaehler <= 12
BEGIN
insert FAKT_SALESFOLLOWUP
select p.kdnr,convert(datetime,'01.' + cast(p.jahr as varchar) + '-'
+ cast(@.zaehler as varchar) ,104),p.stueck_ori/12 as planstueck
from xdatplankunde p
set @.zaehler = @.zaehler +1
END
Future:
Based on these 2 tables I want to create a new table (=source for
analysis services) with a row for each month.
But I want to share the year-plannedpieces in the in the proportion of
the workingdays.
Sample:
sum(workingdays) where (year=2005) = 240
February have 19 workingdays
Pieces of Feburary: 25000 / (19/240)
Do you have any idea ?
Thanks
aaapaulTry,
select
t1.[year],
t1.[month],
t2.customernr,
t2.plannedpieces / nullif((t_ym.sum_workingdays /
nullif(t_y.sum_workingdays * 1.00, 0)), 0)
from
table1 as t1
inner join
table2 as t2
on t1.[year] = t2.[year]
inner join
(
select
[year],
[month],
sum(workingdays) as sum_workingdays_ym
from
table1
group by
[year],
[month]
) as t_ym
on t1.[year] = t_ym.[year] and t1.[month] = t_ym.[month]
inner join
(
select
[year],
sum(workingdays) as sum_workingdays_y
from
table1
group by
[year]
) as t_y
on t1.[year] = t_y.[year];
AMB
"aaapaul" wrote:
> Hello !
> I have a difficult TSQL Problem.
> Perhaps there is an expert, who can help me ...
> I have 2 tables: "WorkingDays" and "Planned Pieces".
> Table 1
> year - month - workingdays
> 2004 - 12 - 21
> 2005 - 01 - 20
> 2005 - 02 - 19
> ...
> Table 2
> customernr - year - plannedpieces
> 11339 - 2004 - 25000
> 11442 - 2005 - 36000
> ...
> Now:
> I share the plannedpieces proportional on every month:
> --PLANZAHLEN
> declare @.zaehler int
> set @.zaehler = 1
> while @.zaehler <= 12
> BEGIN
> insert FAKT_SALESFOLLOWUP
> select p.kdnr,convert(datetime,'01.' + cast(p.jahr as varchar) + '-'
> + cast(@.zaehler as varchar) ,104),p.stueck_ori/12 as planstueck
> from xdatplankunde p
> set @.zaehler = @.zaehler +1
> END
>
> Future:
> Based on these 2 tables I want to create a new table (=source for
> analysis services) with a row for each month.
> But I want to share the year-plannedpieces in the in the proportion of
> the workingdays.
> Sample:
> sum(workingdays) where (year=2005) = 240
> February have 19 workingdays
> Pieces of Feburary: 25000 / (19/240)
> Do you have any idea ?
>
> Thanks
> aaapaul
>|||Correction,
select
t1.[year],
t1.[month],
t2.customernr,
t2.plannedpieces / nullif((t_ym.sum_workingdays_ym /
nullif(t_y.sum_workingdays_y * 1.00, 0)), 0)
from
table1 as t1
inner join
table2 as t2
on t1.[year] = t2.[year]
inner join
(
select
[year],
[month],
sum(workingdays) as sum_workingdays_ym
from
table1
group by
[year],
[month]
) as t_ym
on t1.[year] = t_ym.[year] and t1.[month] = t_ym.[month]
inner join
(
select
[year],
sum(workingdays) as sum_workingdays_y
from
table1
group by
[year]
) as t_y
on t1.[year] = t_y.[year];
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> t1.[year],
> t1.[month],
> t2.customernr,
> t2.plannedpieces / nullif((t_ym.sum_workingdays /
> nullif(t_y.sum_workingdays * 1.00, 0)), 0)
> from
> table1 as t1
> inner join
> table2 as t2
> on t1.[year] = t2.[year]
> inner join
> (
> select
> [year],
> [month],
> sum(workingdays) as sum_workingdays_ym
> from
> table1
> group by
> [year],
> [month]
> ) as t_ym
> on t1.[year] = t_ym.[year] and t1.[month] = t_ym.[month]
> inner join
> (
> select
> [year],
> sum(workingdays) as sum_workingdays_y
> from
> table1
> group by
> [year]
> ) as t_y
> on t1.[year] = t_y.[year];
>
> AMB
>
> "aaapaul" wrote:
> > Hello !
> >
> > I have a difficult TSQL Problem.
> >
> > Perhaps there is an expert, who can help me ...
> >
> > I have 2 tables: "WorkingDays" and "Planned Pieces".
> >
> > Table 1
> > year - month - workingdays
> > 2004 - 12 - 21
> > 2005 - 01 - 20
> > 2005 - 02 - 19
> > ...
> >
> > Table 2
> > customernr - year - plannedpieces
> > 11339 - 2004 - 25000
> > 11442 - 2005 - 36000
> > ...
> >
> > Now:
> > I share the plannedpieces proportional on every month:
> >
> > --PLANZAHLEN
> > declare @.zaehler int
> > set @.zaehler = 1
> >
> > while @.zaehler <= 12
> > BEGIN
> > insert FAKT_SALESFOLLOWUP
> > select p.kdnr,convert(datetime,'01.' + cast(p.jahr as varchar) + '-'
> > + cast(@.zaehler as varchar) ,104),p.stueck_ori/12 as planstueck
> > from xdatplankunde p
> > set @.zaehler = @.zaehler +1
> > END
> >
> >
> > Future:
> > Based on these 2 tables I want to create a new table (=source for
> > analysis services) with a row for each month.
> > But I want to share the year-plannedpieces in the in the proportion of
> > the workingdays.
> >
> > Sample:
> > sum(workingdays) where (year=2005) = 240
> >
> > February have 19 workingdays
> > Pieces of Feburary: 25000 / (19/240)
> >
> > Do you have any idea ?
> >
> >
> > Thanks
> > aaapaul
> >|||Many Thanks SQLGod !
It works fine.
aaapaul

Difficult times deploying a few packages to SQL Server and running as a job

Hi Guys!

I have created a big list of packages, some calling others. They all work fine from my computer using Visual Studio.

When I try to deploy them (building them with deployment turned on and running them either directly from Management Studio or as a job) I get the errors with the password of connection strings. From what I read so far its the encryption process that kills it.

I have tried to add a password to some packages, but it still didnt work (only when run directly on my computer in management studio after deploying to SQL Server, but not as a job).

I have tried to change ProtectionLevel to SecurityStorage, wouldnt let me save in Visual Studio (I understand it is ot allowed in VS because you are saving to File System, how the hell am I supposed to save it to anything else? why is it showing there if its not even valid?).

If anyone can please give me the steps to doing it properly, that would be awesome. I simply need to run the packages from SQL Server! thats all! I have no idea why it has to be soooo difficult :/

A step-by-step guide would be different depending on several factors. Are you planning to store your packages as file system files? Are you using package configuration? How are you going to run the packages (may be via SQL Agent job)?

Personally, I store package as file system files and use something similar to method 4 in this KB article. I hope that helps

http://support.microsoft.com/kb/918760

|||

Hi Refael!

I don't mind saving it to the file system or putting it on SQL Server, as long as it works! :)

How do you actually create this package configuration? and how do you then indicate to the data process that the password is stored there?
I am replicating from Oracle using Microsoft Oracle provider (for which I need the password) to SQL Server.

Thanks so much for your help

Guy

|||

Weird thing: I have changed one package to have a password and when I import it to 'stored packages' or run it from the file system on Management Studio on the server itself (simply copying the files from the development machine to the server) its not asking me for a password and it runs it. does this makes sense?

|||

Just search for package configurations to set connection strings...it would make your packages nicely portables.

|||

I have tried to find out (google) how to make a configuration file in xml but couldn't find much and have no idea what the format should look like.

If you could help me here I would really appriciate it.

|||

guyguy2003 wrote:

I have tried to find out (google) how to make a configuration file in xml but couldn't find much and have no idea what the format should look like.

If you could help me here I would really appriciate it.

http://msdn2.microsoft.com/en-us/library/ms141682.aspxsql

Difficult SQL-Problem

Hello !

This is my table:

Ordernr Date Article
O1 1.1.05 22
O2 2.2.05 33
O3 5.5.05 22
O4 2.2.05 33
O7 8.8.05 55

I need one result-row for each article with the newest Order
(max(date)):

article lastDate lastOrdernumber
22 5.5.05 O3
33 2.2.05 O4
55 8.8.05 O7

How can I get this ?

I tried this:

SELECT distinct article, max(date), max(ordernr)
FROM table
GROUP BY article

article and max(date) is ok, but I am not sure that max(ordernr) and
max(date) comes from the same row.

I think, I will need complex subqueries.

Many thanks
aaapaulPerhaps something like this?

select
t.article,
t.ordernr,
t.orderdate
from
dbo.MyTable t
join
(
select
article,
max(ordernr) as 'ordernr'
from
dbo.MyTable
group by
article
) dt
on t.article = dt.article
and t.ordernr = dt.ordernr

If this doesn't give the results you expect, I suggest you post CREATE
TABLE and INSERT statements to set up a test case - you will probably
get a better response if people can copy and paste something into QA
for testing.

Simon|||Thanks Simon !

But the Problem is, that I need the order with the highest date not the
order with the highest ordernumber.

Perhaps you can modify the statement...

aaapaul|||Or perhaps you can :-) Just replace max(ordernr) with max(orderdate)
and change the join to be on that column.

Simon|||SELECT article, date, MAX(ordernr)
FROM Table AS T
WHERE date =
(SELECT MAX(date)
FROM Table
WHERE article = T.article)
GROUP BY article, date

--
David Portas
SQL Server MVP
--|||Thanks David !

This is what I need.

paul|||Hi Simon !

Thanks, but this doesn t work, too.

I think I need something like this:

(SELECT a.article,a.odate,max(a.ordernr) as maxordernr
FROM TEST a
GROUP BY a.article,a.odate) t1
JOIN
(
SELECT article,max(odate) as maxdat
FROM TEST
GROUP By article
) t2
on t1.article = t2.article and
t1.odate = t2.maxdat

But why cant I join this 2 tables.

Any suggestion ?

Thanks
aaapaul|||in SQL 2000

create table orders(Ordernr int, orderdt Datetime, Article int)

insert into orders values(1,'11/11/2005',1)
insert into orders values(3,'11/12/2005',1)
insert into orders values(5,'11/13/2005',1)

insert into orders values(2,'1/11/2005',2)
insert into orders values(4,'1/12/2005',2)
insert into orders values(6,'1/13/2005',2)

SELECT article, lastdate,
(select MAX(ordernr) from orders AS T
where t.article = latest.article
and t.orderdt = latest.lastdate)
from
(SELECT article, MAX(orderdt) lastdate
FROM orders
GROUP BY article) latest

article lastdate

---- ----------------
----
1 2005-11-13 00:00:00.000 5
2 2005-01-13 00:00:00.000 6

(2 row(s) affected)

drop table orders

in 2005,
use an OLAP function
(row_number() over(partition by article order by orderdt desc) = 1|||Hi David,
Excellent !!
But we can trim the query further If the lastOrderNumber is the
OrderNumber with Max(Orderdate) for a given article .

SELECT *
FROM orders AS T
WHERE orderdt =
(SELECT MAX(orderdt)
FROM orders
WHERE article = T.article)

With warm regards
Jatinder Singh

David Portas wrote:
> SELECT article, date, MAX(ordernr)
> FROM Table AS T
> WHERE date =
> (SELECT MAX(date)
> FROM Table
> WHERE article = T.article)
> GROUP BY article, date
> --
> David Portas
> SQL Server MVP
> --

Difficult SQL-JOIN/UNION-Problem

Hallo !

I have 2 tables with similar construction.

DataOLD and DataNEW.

In my grid I need a JOINED table.

On the one hand I need all the data from the old table and the data
from then new table with the same id (left outer join works)

But additionally I need all rows from the new table where no equivalent
rows are in the old table.

example:
TableOLD
diameter amountOLD
20 100
50 200
30 300

TableNEW
diameter amountNEW
20 500
60 600
70 120

Wished Result:
diameter amountOLD amoutNEW
20 100 500
50 200 NULL
30 300 NULL
60 NULL 600
70 NULL 120

Who can help me?

Many Thanks.
aaapaulThis should work:

select
coalesce(o.diameter, n.diameter),
o.amount,
n.amount
from
dbo.old o
full outer join dbo.new n
on o.diameter = n.diameter

Simon|||thanks simon.

I think, now it works correctly.
Perhaps you can check it...

select
coalesce(b.abdurch,max(pl.abdurch)) as abdurch,
coalesce(b.siebdr,max(pl.siebdr)) as siebdr,
coalesce(b.praege,max(pl.praege)) as praege,
coalesce(b.etikett,max(pl.etikett)) as etikett,
sum(stueckvorjahr) as vorjahrstueck,sum(umsatzvorjahr) as
vorjahrumsatz,
sum(stueckheuer) as heuerstueck,sum(umsatzheuer) as heuerumsatz,
max(pl.planstueck) as planstueck,max(planwert) as planwert, max(rfcq1)
as rfcq1, max(rfcq2) as rfcq2, max(rfcq3) as rfcq3, max(rfcq4) as rfcq4
from budgetplan b
full outer join xplankundeext pl
on b.abdurch = pl.abdurch and
b.siebdr = pl.siebdr and
b.praege = pl.praege and
b.etikett = pl.etikett
where (b.kdnr = '11461' or pl.kdnr = '11461')
group by b.abdurch,b.siebdr,b.praege,b.etikett|||(lvpaul@.gmx.net) writes:
> I think, now it works correctly.
> Perhaps you can check it...
> select
> coalesce(b.abdurch,max(pl.abdurch)) as abdurch,
> coalesce(b.siebdr,max(pl.siebdr)) as siebdr,
> coalesce(b.praege,max(pl.praege)) as praege,
> coalesce(b.etikett,max(pl.etikett)) as etikett,
> sum(stueckvorjahr) as vorjahrstueck,sum(umsatzvorjahr) as
> vorjahrumsatz,
> sum(stueckheuer) as heuerstueck,sum(umsatzheuer) as heuerumsatz,
> max(pl.planstueck) as planstueck,max(planwert) as planwert, max(rfcq1)
> as rfcq1, max(rfcq2) as rfcq2, max(rfcq3) as rfcq3, max(rfcq4) as rfcq4
> from budgetplan b
> full outer join xplankundeext pl
> on b.abdurch = pl.abdurch and
> b.siebdr = pl.siebdr and
> b.praege = pl.praege and
> b.etikett = pl.etikett
> where (b.kdnr = '11461' or pl.kdnr = '11461')
> group by b.abdurch,b.siebdr,b.praege,b.etikett

That's a bit of a mouthfull to ask people who don't know your data model
or your business to review your code.

...but it looks funny with the GROUP BY and all the MAX. Here is a
possible rewrite, where I am pre-aggregating in a derived table. But
it's a based on a lot of guesswork, so it may produce a whole bunch of
crap.

select coalesce(b.abdurch, pl.abdurch) as abdurch,
coalesce(b.siebdr, pl.siebdr) as siebdr,
coalesce(b.praege, pl.praege) as praege,
coalesce(b.etikett, pl.etikett) as etikett,
pl.vorjahrstueck, pl.vorjahrumsatz, pl.heuerstueck,
pl.heuerumsatz, pl.planstueck,
b.planwert, b.rfcq1, b.rfcq2, b.rfcq3, b.rfcq4
from budgetplan b
full join (SELECT abdurch, siebdr, praege, etikett,
sum(stueckvorjahr) as vorjahrstueck,
sum(umsatzvorjahr) as vorjahrumsatz,
sum(stueckheuer) as heuerstueck,
sum(umsatzheuer) as heuerumsatz,
max(pl.planstueck) as planstueck,
FROM xplankundeext
WHERE kdnr = '11461'
GROUP BY abdurch, siebdr, praege, etikett) AS pl
on b.abdurch = pl.abdurch and
b.siebdr = pl.siebdr and
b.praege = pl.praege and
b.etikett = pl.etikett
where (b.kdnr = '11461' or pl.kdnr = IS NULL)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland:

Thats it. It works fine.

select
coalesce(t1.abdurch,t2.abdurch) as abdurch,
coalesce(t1.siebdr,t2.siebdr) as siebdr,
coalesce(t1.praege,t2.praege) as praege,
coalesce(t1.etikett,t2.etikett) as etikett,
t1.planstueck,
t1.planwert,
t1.rfcq1,
t1.rfcq2,
t1.rfcq3,
t1.rfcq4,
t2.vorjahrstueck,
t2.vorjahrumsatz,
t2.heuerstueck,
t2.heuerumsatz
from xplankundeext as t1
FULL JOIN
(
SELECT abdurch,siebdr,praege,etikett,
sum(stueckvorjahr) as vorjahrstueck,
sum(umsatzvorjahr) as vorjahrumsatz,
sum(stueckheuer) as heuerstueck,
sum(umsatzheuer) as heuerumsatz
FROM Budgetplan b left outer join dim_pcenter p1 on b.wgnr = p1.wgnr
WHERE b.kdnr = '11461' and p1.kstnr = 460
GROUP BY abdurch,siebdr,praege,etikett
) as t2
ON
t1.abdurch = t2.abdurch and
t1.siebdr = t2.siebdr and
t1.praege = t2.praege and
t1.etikett = t2.etikett
WHERE t1.kdnr = '11461' and t1.kstnr = 460

One Question at last:

Is it possible to join 2 Tables together where each table is grouped by
?

(Select a
from table tabA
group by a
) as t1
join
(Select b
from table tabB
group by b
) as t2
on t1.xy = t2.xy ??

Thanks.|||On 5 Aug 2005 04:06:20 -0700, lvpaul@.gmx.net wrote:

(snip)
>One Question at last:
>Is it possible to join 2 Tables together where each table is grouped by
>?
>(Select a
>from table tabA
>group by a
>) as t1
>join
>(Select b
>from table tabB
>group by b
>) as t2
>on t1.xy = t2.xy ??

Hi lvpaul,

Quite easy to test for yourself, isn't it?

The answer, BTW, is yes. You can use a non-correlated subquery anywhere
you can specify a tablename. Check out "derived tables" in Books Online.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank You all !

Difficult SQL Statment

Hello !

I habe 2 Tables

Table1: Orders
Fields: Ordernr, Opieces

Table2: Calloffs
Ordernr, Cpieces

In Table1 ordernr is primary key.
In Table2 the same ordernr can exist often

My problem
If the sum(Cpieces) < Opieces:
I have to create a new virtual calloff
with Cpieces = opieces - sum(cpieces)

Its too high for me.

Please help

Best regards
aaapaulOn 25 Jan 2006 07:50:57 -0800, lvpaul@.gmx.net wrote:

>Hello !
>I habe 2 Tables
>Table1: Orders
>Fields: Ordernr, Opieces
>Table2: Calloffs
>Ordernr, Cpieces
>In Table1 ordernr is primary key.
>In Table2 the same ordernr can exist often
>My problem
>If the sum(Cpieces) < Opieces:
>I have to create a new virtual calloff
>with Cpieces = opieces - sum(cpieces)
>Its too high for me.
>Please help
>Best regards
>aaapaul

Hi aaapaul,

Maybe something like this?

INSERT INTO Calloffs (Ordernr, Cpieces)
SELECT o.Ordernr, o.Opieces - COALESCE(SUM(c.CPieces), 0)
FROM Orders AS o
LEFT JOIN Calloffs AS c
ON c.Ordernr = o.Ordernr
GROUP BY o.Ordernr, o.Opieces
HAVING o.Opieces > COALESCE(SUM(c.CPieces), 0)

(untested - see www.aspfaq.com/5006 if you prefer a tested reply or if
this doesn't do what you want)

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo !

Thanks - it works fine.

INSERT INTO POOL_LIEFERDAT(aufnr,werk,lstueck,ldatum)
SELECT a.aufnr,a.werk,a.voffenstueck -
coalesce(sum(l.lstueck),0),'31.12.2006'
FROM FAKT_AUFTRAG a
LEFT OUTER JOIN POOL_LIEFERDAT l
ON a.aufnr = l.aufnr and a.werk = l.werk
GROUP BY a.aufnr,a.werk,a.voffenstueck
HAVING a.voffenstueck > coalesce(sum(l.lstueck),0)

At the moment I am writing one record with the difference.

But in the future I want to write each time 4 records with

Record1: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY
Record2: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY + 1
months
Record3: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY + 2
months
Record4: pieces = coalesce(sum(l.lstueck),0),/4 date = dateXY + 3
months

I want to distribute the virtual call offs over the next 4 month !

Is it possible to make this with SQL ??

Paul|||I think I have to use a temporary table !

How can I define 4 variables with the date of the first day of the next
4 month ?

var1=1.2.06
var2=1.3.06
var3=1.4.06
var5=1.5.06

Thanks
aaapaul|||On 26 Jan 2006 01:36:09 -0800, lvpaul@.gmx.net wrote:

>Hi Hugo !
>Thanks - it works fine.
>INSERT INTO POOL_LIEFERDAT(aufnr,werk,lstueck,ldatum)
>SELECT a.aufnr,a.werk,a.voffenstueck -
>coalesce(sum(l.lstueck),0),'31.12.2006'
>FROM FAKT_AUFTRAG a
>LEFT OUTER JOIN POOL_LIEFERDAT l
>ON a.aufnr = l.aufnr and a.werk = l.werk
>GROUP BY a.aufnr,a.werk,a.voffenstueck
>HAVING a.voffenstueck > coalesce(sum(l.lstueck),0)

Hi Paul,

Don't use locale-dependent date formats in your code. It will cause
unexpected things to happen when SQL Server misinterprets the date
format you intended. Use yyyymmdd (20061231).

>At the moment I am writing one record with the difference.
>But in the future I want to write each time 4 records with
>Record1: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY
>Record2: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY + 1
>months
>Record3: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY + 2
>months
>Record4: pieces = coalesce(sum(l.lstueck),0),/4 date = dateXY + 3
>months
>I want to distribute the virtual call offs over the next 4 month !
>Is it possible to make this with SQL ??
>Paul

Yes, it's possible - and you don't need a temp table for it.

I'm not sure where dateXY comes from. Is that the date constant
(20061231) in the query above? Will it be a constant in the final query,
or is it taken from some other table?

Also - do you really want pieces to be a quarter of SUM(l.lstueck), or
should it be a quarter of a.voffenstueck - SUM(l.lstueck)?

Assuming that dateXY lives in the Auftrge table:

INSERT INTO POOL_LIEFERDAT(aufnr,werk,lstueck,ldatum)
SELECT a.aufnr, a.werk,
(a.voffenstueck - coalesce(sum(l.lstueck),0)) / 4,
DATEADD(month, Numbers.N, a.dateXY)
FROM FAKT_AUFTRAG a
LEFT OUTER JOIN POOL_LIEFERDAT l
ON a.aufnr = l.aufnr and a.werk = l.werk
CROSS JOIN (SELECT 0 AS N
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3) AS Numbers
GROUP BY a.aufnr, a.werk, a.voffenstueck, Numbers.N
HAVING a.voffenstueck > coalesce(sum(l.lstueck),0)

(If you already have a numbers table, you can use that insted of the
derived table).

If dateXY is fixed, you can simply use a derived table with the four
precalculated dates instead of a numbers table and the DATEADD function.

NOTE: Queries are untested. See www.aspfaq.com/5006 if you prefer a
tested solution.

--
Hugo Kornelis, SQL Server MVP|||Hallo Hugo !

Thank you. Its fine.

I read a very interesting article about sql and datetime at
www.insidesql.de. I will use the unseparated format in the future.

I will check your SQL-Statment.

Paul|||All right now. Thank I have learned new possibilities:

Paul

My code:

declare @.dat1 as datetime

-- dat1 = 1. Tag vom nchsten Monat
set @.dat1 =
dateadd(month,1,(CAST(CONVERT(char(8),CURRENT_TIME STAMP,112) as
datetime) - Day(CURRENT_TIMESTAMP)+1))

INSERT INTO POOL_LIEFERDAT(aufnr,ldatum,lstueck,werk)
SELECT a.aufnr,dateadd(month,numbers.n,@.dat1) as ldatum,(a.voffenstueck
- coalesce(sum(l.lstueck),0))/4 as lstueck,a.werk
FROM FAKT_AUFTRAG a
LEFT OUTER JOIN POOL_LIEFERDAT l
ON a.aufnr = l.aufnr and a.werk = l.werk
CROSS JOIN
(SELECT 0 AS N
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3) AS NUMBERS
-- Achtung verursacht FEHLER WHERE not(l.werk is null)
GROUP BY a.aufnr,a.werk,a.voffenstueck,numbers.n
HAVING (a.voffenstueck > coalesce(sum(l.lstueck),0))
Order by a.aufnr|||On 27 Jan 2006 00:55:43 -0800, lvpaul@.gmx.net wrote:

>Hallo Hugo !
>Thank you. Its fine.
>I read a very interesting article about sql and datetime at
>www.insidesql.de. I will use the unseparated format in the future.
>I will check your SQL-Statment.
>Paul

Hi Paul,

I was going to point you to Tibor Karaszi's ultimate guide to the
datetime datatype, but I see that a German translation of it is
available at the insidesql site - probably exactly the article that
you're refering to!

--
Hugo Kornelis, SQL Server MVP|||On 27 Jan 2006 06:22:41 -0800, lvpaul@.gmx.net wrote:

>All right now. Thank I have learned new possibilities:
(snip)
>-- Achtung verursacht FEHLER WHERE not(l.werk is null)

Hi Paul,

What do you mean with the comment? Do you mean that you get errors with
the WHERE clause included, or that you get errors if you exclude it?

With this where clause, the LEFT OUTER JOIN is in effect reduced to an
INNER JOIN. This means that you will get correct information for rows
with at least one corresponding row in POOL_LIEFERDAT, but if you have a
FAKT_AUFTRAG with no rows yet in POOL_LIEFTERDAT, you won't get any rows
for it in the INSERT.

Removing the WHERE clause means that you'll also get four rows for each
FAKT_AUFTRAG with no corresponding POOL_LIEFERDAT.

Tch!

--
Hugo Kornelis, SQL Server MVP

difficult SQL select statement

Hi all,

i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.

sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.

i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.

Alon.

public string GenerateSqlSelect(string[] columnNames){

return "SELECT "+String.Join(", ",columnNames)+" FROM MyTable";

}

|||You could use params keyword to specify that your function has a variable number of arguments.
|||How user will know what are the columns from the table. Does he will write them, or select them using check boxes or some other method. I realy don't understand what are you for. Can you describe how user interface will look like, or some scenario of application work.
It is common practice that you always get all columns from table, but user can control which column want to see, for example in a grid or listview. So he will at first see all columns but it will be able to remove some of them. That configuration can be saved and used for next application start.

difficult SQL select statement

Hi all,

i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.

sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.

i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.

Alon.

public string GenerateSqlSelect(string[] columnNames){

return "SELECT "+String.Join(", ",columnNames)+" FROM MyTable";

}

|||You could use params keyword to specify that your function has a variable number of arguments.
|||How user will know what are the columns from the table. Does he will write them, or select them using check boxes or some other method. I realy don't understand what are you for. Can you describe how user interface will look like, or some scenario of application work.
It is common practice that you always get all columns from table, but user can control which column want to see, for example in a grid or listview. So he will at first see all columns but it will be able to remove some of them. That configuration can be saved and used for next application start.sql

difficult SQL select statement

Hi all,

i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.

sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.

i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.

Alon.

public string GenerateSqlSelect(string[] columnNames){

return "SELECT "+String.Join(", ",columnNames)+" FROM MyTable";

}

|||You could use params keyword to specify that your function has a variable number of arguments.
|||How user will know what are the columns from the table. Does he will write them, or select them using check boxes or some other method. I realy don't understand what are you for. Can you describe how user interface will look like, or some scenario of application work.
It is common practice that you always get all columns from table, but user can control which column want to see, for example in a grid or listview. So he will at first see all columns but it will be able to remove some of them. That configuration can be saved and used for next application start.

Difficult SQL

Hello,

Please can anyone help?

I have got two tables, accounts and account customers.

The accounts table has more than one row for each account, identified by the account number and the sub account.

Each customer should be linked to all of the sub accounts for their account (in the account customers table).

I have found some accounts where one customer might be linked to all the sub accounts but the 2nd customer on the account is only linked to one of the sub accounts. This is wrong & needs to be corrected.

Please can anyone advise how I go about selecting all of the problem accounts.

To start with I grouped all the existing links together, I then need to somehow check each account against this main list.

Please can you advise.

Thanks,
BethBeth, describe the tables and give sample data. :confused:

difficult SP

I have the following table:
tblFriends
OwnerCode FriendCode
7 10
7 14
10 7
10 12
10 13
12 10
13 10
13 18
14 7
18 13

I need a SP which return the following (im unsure about the best return datatype and the sql statement):

I want return all friendcodes of user nr 7 (10 and 14)
and I want to return all friendcodes of user 10 and 14 (7,12,13,7) WITHOUT user 7

(if possible WITHOUT the use of a temptable!)

SELECT FriendCode

FROM tblFriends

WHERE OwnerCode=7

UNION

SELECT t2.FriendCode

FROM tblFriends t1

JOIN tblFriends t2 ON (t2.OwnerCode=t1.FriendCode)

WHERE t1.OwnerCode=7 AND t2.FriendCode<>7

That's not really a SP, but you can put it in one if you want.

|||GREAT!!! :-D|||hmm...it does what I want but I need a bit more...
I need to check if a certain value (e.g. 6) is in the result set...

I tried the following (which does not work):

SELECT

COUNT(*)FROM

(

SELECT

FriendCode

FROM

tblFriends

WHERE

OwnerCode=5

UNION

SELECT

t2.FriendCode

FROM

tblFriends t1

JOIN

tblFriends t2ON(t2.OwnerCode=t1.FriendCode)

WHERE

t1.OwnerCode=5AND t2.FriendCode<>5

)

WHERE

FriendCode=6I get the error:

Incorrect syntax near the keyword 'WHERE'.

|||

Close. You need to give your subquery an alias. For example, change "WHERE" to "t1 WHERE" or

SELECT COUNT(*)

FROM ( ... ) t1

WHERE ...

difficult select distinct query

Hi,

I have a table as following

aa Text1 aa, p@.xxx.be, 15267
aa Text1 aa, p@.xxx.be, 16598
aa Text1 aa, p@.xxx.be, 17568
aa Text2 aa, p@.xxx.be, 25698
aa Text3 aa, x@.zzz.be, 12258

I have to write a query as follows ...

SELECT DISTINCT TOP 500 fldText, fldContact, fldItemid
FROM table
WHERE fldCat = 10 AND CONTAINS (fldText, 'Text1')

In the example you can see the table has rows in which text and contact or
double but with different itemid's. Now my employer wants me to show only 1
row when text and contact or the same. He doesn't mind which itemid I show
... but I have to show one.

I've an idea of how to do this using a cursor and a temporary table but I
guess that will be fatal for the performance because then I have to loop
through all selected rows, check each row with all other rows and store the
primary key in the temporary table if dedected it isn't double. Afterwards
I can execute ... SELECT ... FROM TABLE where primary key in (select
temp_primarykey from #temptable).

I hoped I could do everything in 1 "easy" SELECT but I should not know how?
Any ideas are much appreciated.

Thanks a lot.
Perre Van Wilrijk.On Tue, 24 Aug 2004 15:18:36 +0200, Perre Van Wilrijk wrote:

>Hi,
>I have a table as following
>aa Text1 aa, p@.xxx.be, 15267
>aa Text1 aa, p@.xxx.be, 16598
>aa Text1 aa, p@.xxx.be, 17568
>aa Text2 aa, p@.xxx.be, 25698
>aa Text3 aa, x@.zzz.be, 12258
>I have to write a query as follows ...
>SELECT DISTINCT TOP 500 fldText, fldContact, fldItemid
>FROM table
>WHERE fldCat = 10 AND CONTAINS (fldText, 'Text1')
>In the example you can see the table has rows in which text and contact or
>double but with different itemid's. Now my employer wants me to show only 1
>row when text and contact or the same. He doesn't mind which itemid I show
>... but I have to show one.
>I've an idea of how to do this using a cursor and a temporary table but I
>guess that will be fatal for the performance because then I have to loop
>through all selected rows, check each row with all other rows and store the
>primary key in the temporary table if dedected it isn't double. Afterwards
>I can execute ... SELECT ... FROM TABLE where primary key in (select
>temp_primarykey from #temptable).
>I hoped I could do everything in 1 "easy" SELECT but I should not know how?
>Any ideas are much appreciated.
>Thanks a lot.
>Perre Van Wilrijk.

Hi Perre,

Try it with this instead:

SELECT fldText, fldContact, MIN(fldItemid)
FROM table
WHERE fldCat = 10
AND CONTAINS (fldText, 'Text1')
GROUP BY fldText, fldContact
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Difficult question regarding Reporting Services

We are having an issue that 1) I can not find any documentation on how
to setup correclty and 2) have not had any postings from other experts
that have actually worked.
Here's the issue along with our environment:
Server 1 - Windows 2003 SP1 with SQL Server 2005 SP1
Server 2 - Windows 2003 SP! with SQL Server 2005 Analysis Services SP1
Server 3 - Windows 2003 SP1 with SQL Server 2005 Reporting Services
SP1
Server 1 and 2 are both member servers in the same domain. Server 3
is a workgroup server only, not a member of any domain. Due to
security concerns we can not make this server part of the domain.
Objective - to provide external access of reports to outside users.
Server 3 will sit in a DMZ and communicate with the inside data
sources.
Server 1 contains the Report Server Database
Server 2 contains the user databases used by the reports
Server 3 contains the Report Server and Report Manager websites and
Reporting Services engine which points to Server 1 for the report
server database
Every thing works fine with one exception. The website will load
correctly, authenticate the user (we are using a local account on
workgroup Server 3 to control access to the Report Manager websites).
The report data sources are configured to Store Credentials Securely
in the Database. When we store and use a SQL Server Standard Login,
the reports load correctly. We can monitor in Profiler on both Server
1 and Server 2 and see Reporting Services first hit the Report Server
database and then hit the user database defined in the data source.
The problem is that we must use a Windows Account so that our reports
may access Analysis Services (which does not support Standard
Logins). Please do not reply stating to make sure the check box in
the data source is checked as it is and I wish this were that simple.
If I join server 3 to the domain, the report runs perfectly using the
exact same data source definition defined with the domain windows
account and the checkbox selected to used Windows Credentials. But
when I take server 3 back out of the domain and into just a workgroup,
the report fails to run, specifically in the RS logs during the
RendReport process. I'm beginning to think that you simply can not
store and use Windows account information in the data source when the
Report Server is not in the domain, which to me does not make any
sense at all but will all the testing we have gone through, that's the
only conclusion I can see.
Has anyone out there experienced this issue or a similar issue? Or if
any of you MVPs out there want to try this out, use Virtual PC. We
tested it and the behavior is exactly the same as on our physical
servers.
Any assistance anyone can provide is GREATLY appreciated!!
Thanks in advance.
MartinI have not done exactly this but I just read up on Analysis services and
believe I have a solution for you. Create a local windows user on the
Analysis services box. Give it the read only rights you want it to have in
Analysis services. Then use this username and password for your credentials.
Another possibility (in case you didn't alread try this) is try the windows
domain user by putting domainname\username in for the user and then its
password. See if that works as well.
Bruce Loehle-Conger
<martinghale@.gmail.com> wrote in message
news:1172175247.449272.235750@.k78g2000cwa.googlegroups.com...
> We are having an issue that 1) I can not find any documentation on how
> to setup correclty and 2) have not had any postings from other experts
> that have actually worked.
> Here's the issue along with our environment:
> Server 1 - Windows 2003 SP1 with SQL Server 2005 SP1
> Server 2 - Windows 2003 SP! with SQL Server 2005 Analysis Services SP1
> Server 3 - Windows 2003 SP1 with SQL Server 2005 Reporting Services
> SP1
> Server 1 and 2 are both member servers in the same domain. Server 3
> is a workgroup server only, not a member of any domain. Due to
> security concerns we can not make this server part of the domain.
> Objective - to provide external access of reports to outside users.
> Server 3 will sit in a DMZ and communicate with the inside data
> sources.
> Server 1 contains the Report Server Database
> Server 2 contains the user databases used by the reports
> Server 3 contains the Report Server and Report Manager websites and
> Reporting Services engine which points to Server 1 for the report
> server database
> Every thing works fine with one exception. The website will load
> correctly, authenticate the user (we are using a local account on
> workgroup Server 3 to control access to the Report Manager websites).
> The report data sources are configured to Store Credentials Securely
> in the Database. When we store and use a SQL Server Standard Login,
> the reports load correctly. We can monitor in Profiler on both Server
> 1 and Server 2 and see Reporting Services first hit the Report Server
> database and then hit the user database defined in the data source.
> The problem is that we must use a Windows Account so that our reports
> may access Analysis Services (which does not support Standard
> Logins). Please do not reply stating to make sure the check box in
> the data source is checked as it is and I wish this were that simple.
> If I join server 3 to the domain, the report runs perfectly using the
> exact same data source definition defined with the domain windows
> account and the checkbox selected to used Windows Credentials. But
> when I take server 3 back out of the domain and into just a workgroup,
> the report fails to run, specifically in the RS logs during the
> RendReport process. I'm beginning to think that you simply can not
> store and use Windows account information in the data source when the
> Report Server is not in the domain, which to me does not make any
> sense at all but will all the testing we have gone through, that's the
> only conclusion I can see.
> Has anyone out there experienced this issue or a similar issue? Or if
> any of you MVPs out there want to try this out, use Virtual PC. We
> tested it and the behavior is exactly the same as on our physical
> servers.
> Any assistance anyone can provide is GREATLY appreciated!!
> Thanks in advance.
> Martin
>|||Bruce,
Thanks for the advice. That was similar to what I had researched as
well. It didn't work before but thought I would give it another try.
I found something interesting. The local account not only needs to be
on the Analysis Server but also on the external report report server.
Once I created the same account name locally on both servers, RS was
able to make the connection with the Windows credentials stored in the
Data Source.
This is something that obviously is not needed when all machines are
in the same domain or are in trusted domains. I have only found this
issue when the report server is not of the domain or a trusted domain,
but only a workgroup...
Thanks for your input!|||Good to know (about creating both places). Glad to see it works for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<martinghale@.gmail.com> wrote in message
news:1172245522.365928.221680@.p10g2000cwp.googlegroups.com...
> Bruce,
> Thanks for the advice. That was similar to what I had researched as
> well. It didn't work before but thought I would give it another try.
> I found something interesting. The local account not only needs to be
> on the Analysis Server but also on the external report report server.
> Once I created the same account name locally on both servers, RS was
> able to make the connection with the Windows credentials stored in the
> Data Source.
> This is something that obviously is not needed when all machines are
> in the same domain or are in trusted domains. I have only found this
> issue when the report server is not of the domain or a trusted domain,
> but only a workgroup...
> Thanks for your input!
>sql

Difficult question

Hello,
I have a table with stock values, they are grouped by a stock id. now I want
to get the trend of each stock. I only need this for one value per w for
one month.
for example:
stockid price
1 20
2 10
3 5
1 21
2 9
3 5
1 24
2 8
3 4
1 28
2 5
3 5
now I wan tot group them like:
stockid price1 price2 price3 price4
1 20 21 24 28
2 10 9 8 5
3 5 5 4 5
This way I can tell what the trend of the prices are, going up or down.
Or is there an other way of doing this?
Any help is appreciated,
-MarkYour design is wrong; we need a date or something by which to arange
the prices. Get a book on basic RDBMS and read Dr. Codd's 12 rules.
Look at the rule about using scalar values in columns of tables to
model all relationships.
SELECT ticker_sym,
CASE WHEN quote_date = '2005-11-22'
THEN price END AS price_1,
CASE WHEN quote_date = '2005-11-23'
THEN price END AS price_2,
CASE WHEN quote_date = '2005-11-24'
THEN price END AS price_3
FROM StockHistory
GROUP BY ticker_sym;|||Thanks,
Well actually there is a date column and other columns as well, I just used
these because I thought they where the importante ones, my mistake.
Table Def:
ID (PK)
StockID (FK)
Date (datetime)
ClosePrice (money)
This gives me the following result:
StockID, P1, P2, P3, P4
1, 20, NULL, NULL, NULL
1, NULL, 21, NULL, NULL
1, NULL, NULL, 24, NULL
1, NULL, NULL, NULL, 28
What I would like is
StockID, P1, P2, P3, P4
1, 20, 21, 24, 28
-Mark
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132859937.260224.130920@.f14g2000cwb.googlegroups.com...
> Your design is wrong; we need a date or something by which to arange
> the prices. Get a book on basic RDBMS and read Dr. Codd's 12 rules.
> Look at the rule about using scalar values in columns of tables to
> model all relationships.
> SELECT ticker_sym,
> CASE WHEN quote_date = '2005-11-22'
> THEN price END AS price_1,
> CASE WHEN quote_date = '2005-11-23'
> THEN price END AS price_2,
> CASE WHEN quote_date = '2005-11-24'
> THEN price END AS price_3
> FROM StockHistory
> GROUP BY ticker_sym;
>|||SELECT ticker_sym,
SUM( CASE WHEN quote_date = '2005-11-22'
THEN price END) AS price_1,
SUM( CASE WHEN quote_date = '2005-11-23'
THEN price END) AS price_2,
SUM( CASE WHEN quote_date = '2005-11-24'
THEN price END) AS price_3
FROM StockHistory
GROUP BY ticker_sym;|||Thanks Celko,
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132861841.665270.278530@.g14g2000cwa.googlegroups.com...
> SELECT ticker_sym,
> SUM( CASE WHEN quote_date = '2005-11-22'
> THEN price END) AS price_1,
> SUM( CASE WHEN quote_date = '2005-11-23'
> THEN price END) AS price_2,
> SUM( CASE WHEN quote_date = '2005-11-24'
> THEN price END) AS price_3
> FROM StockHistory
> GROUP BY ticker_sym;
>

Difficult query: return recordset from concatenated strings?

Hi All,

I have what seems to me to be a difficult query request for a database
I've inherited.

I have a table that has a varchar(2000) column that is used to store
system and user messages from an on-line ordering system.

For some reason (I have no idea why), when the original database was
being designed no thought was given to putting these messages in
another table, one row per message, and I've now been asked to provide
some stats on the contents of this field across the recordset.

A pseudo example of the table would be:

custrep, orderid, orderdate, comments

1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer asked
for a brown model
2, 10002, 2004-04-12, :Comment 3:Comment 4:
1, 10003, 2004-04-12, :Comment 2:Comment 8:
2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:
2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled order

So, what I've been asked to provide is something like this:

orderdate, custrep, syscomment, countofsyscomments
2004-04-12, 1, Comment 1, 1
2004-04-12, 1, Comment 2, 2
2004-04-12, 1, Comment 3, 1
2004-04-12, 1, Comment 8, 1
2004-04-12, 2, Comment 1, 1
2004-04-12, 2, Comment 3, 1
2004-04-12, 2, Comment 4, 2
2004-04-12, 2, Comment 6, 2
2004-04-12, 2, Comment 7, 1

I have a table in which each of the system comments are defined.
Anything else appearing in the column is treated as a user comment.

Does anyone have any thoughts on how this could be achieved? The end
result will end up in an SQL Server 2000 stored procedure which will
be called from an ASP page to provide order taking stats.

Any help will be humbly and immensely appreciated!

Much warmth,

MurrayAssuming your tables look something like this:

CREATE TABLE Orders (custrep INTEGER NOT NULL, orderid INTEGER, orderdate
DATETIME NOT NULL, comment1 VARCHAR(2000) NULL, comment2 VARCHAR(2000) NULL,
comment3 VARCHAR(2000) NULL, comment4 VARCHAR(2000) NULL /*, PRIMARY KEY ?
*/)

CREATE TABLE SystemComments (comment VARCHAR(2000) PRIMARY KEY)

Try this:

SELECT O.orderdate, O.custrep, O.comment,
COUNT(S.comment) AS count_of_syscomments
FROM
(SELECT orderdate, custrep, comment1
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment2
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment3
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment4
FROM Orders)
AS O (orderdate, custrep, comment)
LEFT JOIN SystemComments AS S
ON O.comment = S.comment
GROUP BY O.orderdate, O.custrep, O.comment

--
David Portas
SQL Server MVP
--|||On Fri, 14 May 2004 15:51:25 +0100, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>Assuming your tables look something like this:
>CREATE TABLE Orders (custrep INTEGER NOT NULL, orderid INTEGER, orderdate
>DATETIME NOT NULL, comment1 VARCHAR(2000) NULL, comment2 VARCHAR(2000) NULL,
>comment3 VARCHAR(2000) NULL, comment4 VARCHAR(2000) NULL /*, PRIMARY KEY ?
>*/)
>CREATE TABLE SystemComments (comment VARCHAR(2000) PRIMARY KEY)
>Try this:
>SELECT O.orderdate, O.custrep, O.comment,
> COUNT(S.comment) AS count_of_syscomments
> FROM
> (SELECT orderdate, custrep, comment1
> FROM Orders
> UNION ALL
> SELECT orderdate, custrep, comment2
> FROM Orders
> UNION ALL
> SELECT orderdate, custrep, comment3
> FROM Orders
> UNION ALL
> SELECT orderdate, custrep, comment4
> FROM Orders)
> AS O (orderdate, custrep, comment)
> LEFT JOIN SystemComments AS S
> ON O.comment = S.comment
> GROUP BY O.orderdate, O.custrep, O.comment

Hi David,

Thanks for the suggestion, unfortunately that's not how the table is
defined.

Sorry, I should have posted a pseudo create table statement as well.

It looks something like:

CREATE TABLE OrderComments (custrep INTEGER NOT NULL, orderid INTEGER,
orderdate DATETIME NOT NULL, comments VARCHAR(2000))

The create table statement you have for SystemComments is fine.

So, in the OrderComments table, the comments column might contain:

':Comment 1:Comment 2: Comment 8:Comment whatever'

So, each of the system and user generated comments for a particular
order are concatenated into a string and are put into a single column
(comments column) for that order.

Sorry for the confusion...

Much warmth,

Murray|||>So, in the OrderComments table, the comments column might contain:
>':Comment 1:Comment 2: Comment 8:Comment whatever'
>So, each of the system and user generated comments for a particular
>order are concatenated into a string and are put into a single column
>(comments column) for that order.
>Sorry for the confusion...
>Much warmth,
>Murray

Can I assume that these are free form and free for all type of
comments and not standardized ?

Is there some kind of unique seperator between comments ?

Been there, done this real recently and it wasn't pretty at all.

Randy
http://members.aol.com/rsmeiner|||You can try this:

SELECT O.orderdate, O.custrep, O.comments,
COALESCE(SUM((LEN(O.comments)-LEN(REPLACE(O.comments,S.comment,'')))
/LEN(S.comment)),0)
FROM OrderComments AS O
LEFT JOIN SystemComments AS S
ON O.comments LIKE '%'+S.comment+'%'
GROUP BY O.orderdate, O.custrep, O.comments

Don't expect great performance though!

--
David Portas
SQL Server MVP
--|||On 14 May 2004 15:20:02 GMT, rsmeiner@.aol.comcrap (RSMEINER) wrote:

[snip]

>>
>Can I assume that these are free form and free for all type of
>comments and not standardized ?
>Is there some kind of unique seperator between comments ?
>Been there, done this real recently and it wasn't pretty at all.

Hi Randy,

Pretty much, except that I have a reference table of the exact wording
of each of the system comments that might be found in the concatenated
value in the comments column.

The comments are delimited by a colon character, but I can't assume
that user comments, which get concatenated in the same field, will
always be lacking colon characters.

The only thing I can think to do is create a temp table in a stored
procedure and do multiple update...select statements to populate the
temp table, using the values in the predfined comments table.

I hear you that it isn't pretty.

Much warth,

Murray|||>Hi Randy,
>Pretty much, except that I have a reference table of the exact wording
>of each of the system comments that might be found in the concatenated
>value in the comments column.
>The comments are delimited by a colon character, but I can't assume
>that user comments, which get concatenated in the same field, will
>always be lacking colon characters.
>The only thing I can think to do is create a temp table in a stored
>procedure and do multiple update...select statements to populate the
>temp table, using the values in the predfined comments table.
>I hear you that it isn't pretty.
>Much warth,
>Murray

Since you have a table of the system comments, it makes it
much easier. I'm thinking on this.

How big are these tables ?

Randy
http://members.aol.com/rsmeiner|||Did you try my second solution?

--
David Portas
SQL Server MVP
--

Difficult Query: is this possible in SQL?

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"Robert Brown" <robertbrown1971@.yahoo.com> wrote in message
news:240a4d09.0407021517.1ee87cdb@.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|||"Robert Brown" <robertbrown1971@.yahoo.com> wrote in message
news:240a4d09.0407021517.1ee87cdb@.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|||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;|||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;|||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

robertbrown1971@.yahoo.com (Robert Brown) wrote in message news:<240a4d09.0407021517.1ee87cdb@.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

robertbrown1971@.yahoo.com (Robert Brown) wrote in message news:<240a4d09.0407021517.1ee87cdb@.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|||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" <rromley@.optonline.net> wrote in message
news:bfbb57f7.0407050932.4cc43ea7@.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

robertbrown1971@.yahoo.com (Robert Brown) wrote in message
news:<240a4d09.0407021517.1ee87cdb@.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

robertbrown1971@.yahoo.com (Robert Brown) wrote in message
news:<240a4d09.0407021517.1ee87cdb@.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|||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" <tallpeak@.hotmail.NO.SPAM> wrote in message news:<TOGdnVYREfTFQHTdRVn-tw@.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" <rromley@.optonline.net> wrote in message
> news:bfbb57f7.0407050932.4cc43ea7@.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
>
>
> robertbrown1971@.yahoo.com (Robert Brown) wrote in message
> news:<240a4d09.0407021517.1ee87cdb@.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
>
> robertbrown1971@.yahoo.com (Robert Brown) wrote in message
> news:<240a4d09.0407021517.1ee87cdb@.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|||robertbrown1971@.yahoo.com (Robert Brown) wrote in message news:<240a4d09.0407021517.1ee87cdb@.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|||robertbrown1971@.yahoo.com (Robert Brown) wrote in message news:<240a4d09.0407021517.1ee87cdb@.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|||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" <rromley@.optonline.net> wrote in message
news:bfbb57f7.0407051925.4a4faa2c@.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" <tallpeak@.hotmail.NO.SPAM> wrote in message
news:<TOGdnVYREfTFQHTdRVn-tw@.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" <rromley@.optonline.net> wrote in message
> news:bfbb57f7.0407050932.4cc43ea7@.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
>
>
> robertbrown1971@.yahoo.com (Robert Brown) wrote in message
> news:<240a4d09.0407021517.1ee87cdb@.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
>
> robertbrown1971@.yahoo.com (Robert Brown) wrote in message
> news:<240a4d09.0407021517.1ee87cdb@.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|||>>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" <tallpeak@.hotmail.NO.SPAM> wrote in message
news:L9mdndTaBrk5wXfdRVn-ig@.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" <rromley@.optonline.net> wrote in message
> news:bfbb57f7.0407051925.4a4faa2c@.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" <tallpeak@.hotmail.NO.SPAM> wrote in message
> news:<TOGdnVYREfTFQHTdRVn-tw@.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" <rromley@.optonline.net> wrote in message
> > news:bfbb57f7.0407050932.4cc43ea7@.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
> > robertbrown1971@.yahoo.com (Robert Brown) wrote in message
> > news:<240a4d09.0407021517.1ee87cdb@.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
> > robertbrown1971@.yahoo.com (Robert Brown) wrote in message
> > news:<240a4d09.0407021517.1ee87cdb@.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 (richardromley@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>>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" <esquel@.sommarskog.se> wrote in message
news:Xns951F17F8D19BYazorman@.127.0.0.1...
> Richard Romley (richardromley@.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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Richard Romley (richardromley@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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" <esquel@.sommarskog.se> wrote in message
news:Xns951FF020B5D10Yazorman@.127.0.0.1...
> Richard Romley (richardromley@.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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Richard Romley (rromley@.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/online...neric__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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>>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=...98%40decath.com

http://groups.google.com/groups?hl=...news.tesser.com

http://groups.google.com/groups?hl=...3%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" <esquel@.sommarskog.se> wrote in message
news:Xns9520F41CEB2FDYazorman@.127.0.0.1...
> Richard Romley (rromley@.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/online...neric__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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||>>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=...98%40decath.com

http://groups.google.com/groups?hl=...news.tesser.com

http://groups.google.com/groups?hl=...3%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|||Oops. Sorry for the double post. I got an error the first time saying it had
failed.|||Richard Romley (rromley@.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=...98%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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>>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" <esquel@.sommarskog.se> wrote in message
news:Xns952232661C30Yazorman@.127.0.0.1...
> Richard Romley (rromley@.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=...98%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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Richard Romley (rromley@.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, esquel@.sommarskog.se

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