on 2012 Jan 03 11:39 AM
I have a subquery that is returning incorrect results. When I run the subquery by itself it returns the correct values, but when it is run as a subquery it returns a different result set.
When I run the following:
select * from (select acct, patient_code, sub_num, max(coverage) acoverage, count(*) cnt from #temp group by acct, patient_code, sub_num having count(*) > 1) zas order by acct
It returns:
acct,patient_code,sub_num,acoverage,cnt
1623,'B',2,2,2
3025,'B',2,1,2
3029,'B',2,1,2
(Sometimes acoverage is all 1's or some combination of 1's and 2's)
When I run the subquery alone:
select acct, patient_code, sub_num, max(coverage) acoverage, count(*) cnt from #temp group by acct, patient_code, sub_num having count(*) > 1 order by acct
It returns:
acct,patient_code,sub_num,acoverage,cnt
1623,'B',2,2,2
3025,'B',2,2,2
3029,'B',2,2,2
(Notice acoverage is now 2)
Is there something I am missing, or is this really a SQL bug? Thanks
The code to create the #temp table is as below:
create table #temp (acct integer, patient_code char(1), coverage integer, sub_num integer); begin declare @i integer; set @i = 1; while @i < 5500 loop insert into #temp (acct, patient_code, coverage, sub_num) values (@i,'B',1,2); set @i = @i + 1; end loop; end; insert into #temp (acct, patient_code, coverage, sub_num) values (1623,'B',2,2); insert into #temp (acct, patient_code, coverage, sub_num) values (3025,'B',2,2); insert into #temp (acct, patient_code, coverage, sub_num) values (3029,'B',2,2);
This is running on 12.0.1.3152 on Windows 7.
Request clarification before answering.
It seems to be a bug solved in newer versions.
With 12.0.1.3324, I can reproduce your results. When using the newest Windows build (12.0.1.3519), the query with the derived table lists the desired results, i.e. both queries return the same result:
select acct, patient_code, sub_num, max(coverage) acoverage, count(*) cnt from #temp group by acct, patient_code, sub_num having count(*) > 1 order by acct; select * from (select acct, patient_code, sub_num, max(coverage) acoverage, count(*) cnt from #temp group by acct, patient_code, sub_num having count(*) > 1) zas order by acct
So I would recommend to use a current 12.0.1 EBF - you seem to be using the (quite old) original GA version 12.0.1.3152.
BTW: AFAIK, "zas" is not used as a subquery but as a "derived query" here.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
50 | |
9 | |
8 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.