Why does using the "in" clause cause an error in my "Having" condition in 2000, but not 2005?

I'm using the following SQL, which works against 2005 but fails against 2000:

select "table1"."LevelKey" AS "level0key",
case when "table1"."UnitCode" in ('012201', '09C202', '10C010')
then 'A'
else 'B' end AS "levelkey",
sum("table2"."Total") AS "Total"

from "t_mlcdm_d_businessGrp" "table1",
"t_mlcdm_f_facilitySnapshot" "table2"

where

"Table1"."levelKey" = "Table2"."GrpKey"

Group by "Business_Grp"."BusinessGrpAbbrev",
case when "Business_Grp"."GLUnitCode" in ('012201', '09C202', '10C010')
then 'A'
else 'B' end
having

case when ("Business_Grp"."GLUnitCode" in ('012201', '09C202', '10C010'))
then 'A'
else 'B' end = 'A'

If I replace the case portion with something like:

case

when ("Business_Grp"."GLUnitCode" = '012201' then 'A'

when ("Business_Grp"."GLUnitCode" = '09C202', then 'A'

when ("Business_Grp"."GLUnitCode" = '10C010' then 'A'

else 'B'

end

Then it works in Sql2000. Is this a MSql 2000 bug

Aslom Rob,

1 - Can you tell us what is the error

2 - I do not see this table in the statement, may be the error is related to it or are we missing something here .

"Business_Grp"."BusinessGrpAbbrev"

3 - Seems that you want the result just for rows that match the criteria:

"table1"."UnitCode" in ('012201', '09C202', '10C010')

so, I would recommend to put that filter in the "where" clause.

select

"table1"."LevelKey" AS "level0key",

sum("table2"."Total") AS "Total"

from

"t_mlcdm_d_businessGrp" "table1"

inner join
"t_mlcdm_f_facilitySnapshot" "table2"

on "Table1"."levelKey" = "Table2"."GrpKey"

where

"table1"."UnitCode" in ('012201', '09C202', '10C010')

Group by

"table1"."LevelKey"

AMB

Try it without all the double quotes.

Can you also post the actual queries - the one you say works obviously doesn't as it is missing close brackets.

I'm guessing that it's because it hasn't spotted that your group by clause is the same as the expression in the select.

Think the having cluase (which should be a where clause) will get rid of all groups which aren't A.

So the code is really

select "table1"."LevelKey" AS "level0key",
'A' AS levelkey,
sum("table2"."Total") AS "Total"

from "t_mlcdm_d_businessGrp" "table1",
"t_mlcdm_f_facilitySnapshot" "table2"

where

"Table1"."levelKey" = "Table2"."GrpKey"

and "Business_Grp"."GLUnitCode" in ('012201', '09C202', '10C010')

Group by "Business_Grp"."BusinessGrpAbbrev"

Hmm..guess giving the error might help:

I modified the SQL I posted to try to strip out the "real" database names ( didn't do a good job of that, did I ). Regardless, I know how to make it work. The SQL is coming out of my BI system (auto-generated), I'm just tyring to determine if its a BI problem or a MSSQL problem. The fact that it works in SQL 2005 suggests that this is a bug in SQL2000 (which means logging it with Microsoft).

select "Business_Grp"."BusinessGrpAbbrev" AS "level0key",

case

when "Business_Grp"."GLUnitCode" in ('012201', '09C202', '10C010') then 'A'

else 'B'

end AS "levelkey",

sum("Facility_Snapshot"."TotalCmtmnt") AS "Total_Cmtmnt"

from "t_mlcdm_d_businessGrp" "Business_Grp", "t_mlcdm_f_facilitySnapshot" "Facility_Snapshot"

where "Business_Grp"."BusinessGrpKey" = "Facility_Snapshot"."BusinessGrpKey"

group by "Business_Grp"."BusinessGrpAbbrev",

case

when "Business_Grp"."GLUnitCode" in ('012201', '09C202', '10C010') then 'A'

else 'B'

end

having

case

when ("Business_Grp"."GLUnitCode" in ('012201', '09C202', '10C010')) then 'A'

else 'B'

end = 'A'

Keep in mind that this works correctly in SQL 2005, so the syntax should be valid as is. Replace all the case statements with simple comparisons, and it works in SQL2000 as well.

What about the error msg

AMB

Was in the reply just before your post.:

Column 'Business_Grp.GLUnitCode' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Hi Abslom Rob,

I have not found an explanation for your error, but I would recommend to move the filter to the "where" clause. Why to calculate the sum for "B"s if you do not want then in the result.

select

"Business_Grp"."BusinessGrpAbbrev" AS "level0key",

'A' AS "levelkey",

sum("Facility_Snapshot"."TotalCmtmnt") AS "Total_Cmtmnt"

from "t_mlcdm_d_businessGrp" "Business_Grp", "t_mlcdm_f_facilitySnapshot" "Facility_Snapshot"

"Business_Grp"."BusinessGrpKey" = "Facility_Snapshot"."BusinessGrpKey" and

group by "Business_Grp"."BusinessGrpAbbrev"

go

AMB

This SQL is coming out of a query generation tool, and is representational of a more complex query. I'm not so much concerned about how to make the SQL work as I am about making the query generator work correctly. If the SQL is actually invalid for some reason, that I can just log a bug against my query tool, but if the problem is in Microsoft SQL2000 itself, then that bug will just get bounced back to me.

I now have a case open with Microsoft, so I'll let y'all know what they say about it.