Problems with queries on Win 2003 server

I am running into lot of problems with queries when
I run it on a windows 2003 server having Sql 2000 SP3A
pack.
I was able to run the following query just fine on
Windows NT with Sql 7 -
SELECT filing_area, File_num, contract_num, * from
eng_file where filing_area+ COALESCE(file_num, 'TEST') +
COALESCE(contract_num, 'TEST')
in (select filing_area + COALESCE(file_num, 'TEST') +
COALESCE(contract_num, 'TEST') from eng_file
group by filing_area + COALESCE(file_num, 'TEST') +
COALESCE(contract_num, 'TEST') having count(*) > 1 )
But the same query gives me the following errors
when run on Win 2003 with Sql 2000 SP3 or Win 2k with Sql
2k sp3.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'eng_file.FILING_AREA' is invalid in the select
list because it is not contained in either an aggregate
function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'eng_file.FILE_NUM' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'eng_file.FILE_NUM' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'eng_file.CONTRACT_NUM' is invalid in the select
list because it is not contained in either an aggregate
function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'eng_file.CONTRACT_NUM' is invalid in the select
list because it is not contained in either an aggregate
function or the GROUP BY clause.
What I am missing?
Thank you,
-Li
The behaviour of queries with expressions in the GROUP BY list doesn't
always seem very logical. I don't have 7.0 to verify but the following query
should work on both versions. In your IN comparison, notice that (a1+b1+c1 =
a2+b2+c2) is NOT the same comparison as (a1=a2, b1=b2, c1=c2) if these
values are variable-lengths strings. Unless the strings are CHAR or VARCHAR
of equal length then the result of your query may not be what I think you
intended.
SELECT T1.filing_area, T1.file_num, T1.contract_num, *
FROM eng_file AS T1
JOIN
(SELECT filing_area,
COALESCE(file_num, 'TEST'),
COALESCE(contract_num, 'TEST')
FROM eng_file
GROUP BY filing_area,
COALESCE(file_num, 'TEST'),
COALESCE(contract_num, 'TEST')
HAVING COUNT(*)>1)
AS T2(filing_area, file_num, contract_num)
ON T1.filing_area = T2.filing_area
AND COALESCE(T1.file_num, 'TEST') = T2.file_num
AND COALESCE(T1.contract_num, 'TEST') = T2.contract_num
David Portas
SQL Server MVP
|||Your original query works on SQL Server 2005 (9.00.852), so although it
doesn't help you now it does appear that the problem has been fixed. :-)
David Portas
SQL Server MVP
|||Li,
This certainly looks like a bug to me. I believe your query will
succeed if you replace COALESCE by ISNULL. The two functions are not
identical, however. The data type of COALESCE(x,y) accommodates all
the arguments, but the type of ISNULL(x,y) is the type of x. Also,
COALESCE takes multiple arguments and ISNULL must be nested.
I'll pass this on to Microsoft, since I have not seen it reported
before. Thanks!
Steve Kass
Drew University
Li wrote:

>I am running into lot of problems with queries when
>I run it on a windows 2003 server having Sql 2000 SP3A
>pack.
>I was able to run the following query just fine on
>Windows NT with Sql 7 -
>SELECT filing_area, File_num, contract_num, * from
>eng_file where filing_area+ COALESCE(file_num, 'TEST') +
>COALESCE(contract_num, 'TEST')
>in (select filing_area + COALESCE(file_num, 'TEST') +
>COALESCE(contract_num, 'TEST') from eng_file
>group by filing_area + COALESCE(file_num, 'TEST') +
>COALESCE(contract_num, 'TEST') having count(*) > 1 )
>But the same query gives me the following errors
>when run on Win 2003 with Sql 2000 SP3 or Win 2k with Sql
>2k sp3.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.FILING_AREA' is invalid in the select
>list because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.FILE_NUM' is invalid in the select list
>because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.FILE_NUM' is invalid in the select list
>because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.CONTRACT_NUM' is invalid in the select
>list because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.CONTRACT_NUM' is invalid in the select
>list because it is not contained in either an aggregate
>function or the GROUP BY clause.
>
>What I am missing?
>Thank you,
>-Li
>
>
|||Li,
A better workaround (I only checked a simpler query) is to replace
COALESCE(x,y) with CASE WHEN x IS NULL THEN x ELSE y END, which is
equivalent. I think the bug here is a parsing problem that comes up if
a subquery groups on a CASE WHEN NOT ... expression, which is how the
COALESCE must be getting rewritten.
SK
Li wrote:

>I am running into lot of problems with queries when
>I run it on a windows 2003 server having Sql 2000 SP3A
>pack.
>I was able to run the following query just fine on
>Windows NT with Sql 7 -
>SELECT filing_area, File_num, contract_num, * from
>eng_file where filing_area+ COALESCE(file_num, 'TEST') +
>COALESCE(contract_num, 'TEST')
>in (select filing_area + COALESCE(file_num, 'TEST') +
>COALESCE(contract_num, 'TEST') from eng_file
>group by filing_area + COALESCE(file_num, 'TEST') +
>COALESCE(contract_num, 'TEST') having count(*) > 1 )
>But the same query gives me the following errors
>when run on Win 2003 with Sql 2000 SP3 or Win 2k with Sql
>2k sp3.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.FILING_AREA' is invalid in the select
>list because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.FILE_NUM' is invalid in the select list
>because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.FILE_NUM' is invalid in the select list
>because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.CONTRACT_NUM' is invalid in the select
>list because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.CONTRACT_NUM' is invalid in the select
>list because it is not contained in either an aggregate
>function or the GROUP BY clause.
>
>What I am missing?
>Thank you,
>-Li
>
>
|||Li,
ISNUll worked - thank you.
Please do let me know if you get a chance to report
this to Microsoft and their reaction to it.
Thank you,
-Li

>--Original Message--
>Li,
> This certainly looks like a bug to me. I believe your
query will
>succeed if you replace COALESCE by ISNULL. The two
functions are not
>identical, however. The data type of COALESCE(x,y)
accommodates all
>the arguments, but the type of ISNULL(x,y) is the type
of x. Also,
>COALESCE takes multiple arguments and ISNULL must be
nested.
> I'll pass this on to Microsoft, since I have not seen
it reported[vbcol=seagreen]
>before. Thanks!
>Steve Kass
>Drew University
>Li wrote:
+[vbcol=seagreen]
Sql[vbcol=seagreen]
list[vbcol=seagreen]
list
>.
>
|||Steve,
I agree the query may not be the best one as it was
written to quickly take care of some data issues we are
facing and came up with this. Since it worked fine on Sql
7.0, I don't find any reason for it not to work with Sql
2K. David mentioned that it works on Sql 2k so the
problem may have been fixed. What am I missing that it
isn't working for me?
Any ideas?
I am going to write to David too.
Thank you very much for yout time and input -
-Li

>--Original Message--
>Li,
> A better workaround (I only checked a simpler query)
is to replace
>COALESCE(x,y) with CASE WHEN x IS NULL THEN x ELSE y
END, which is
>equivalent. I think the bug here is a parsing problem
that comes up if
>a subquery groups on a CASE WHEN NOT ... expression,
which is how the[vbcol=seagreen]
>COALESCE must be getting rewritten.
>SK
>Li wrote:
+[vbcol=seagreen]
Sql[vbcol=seagreen]
list[vbcol=seagreen]
list
>.
>
|||Steve,
Ignore my comments about why it isn't working for me.
When I went to reply to DAvid's email, I noticed that he
said it is fixed on 2005 and not 2000, that answers my
question.
-Li
>--Original Message--
>Li,
> A better workaround (I only checked a simpler query)
is to replace
>COALESCE(x,y) with CASE WHEN x IS NULL THEN x ELSE y
END, which is
>equivalent. I think the bug here is a parsing problem
that comes up if
>a subquery groups on a CASE WHEN NOT ... expression,
which is how the[vbcol=seagreen]
>COALESCE must be getting rewritten.
>SK
>Li wrote:
+[vbcol=seagreen]
Sql[vbcol=seagreen]
list[vbcol=seagreen]
list
>.
>