The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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.

asked 03 Jan '12, 11:39

tmjac2's gravatar image

tmjac2
36117
accept rate: 0%

edited 03 Jan '12, 12:01

Volker%20Barth's gravatar image

Volker Barth
30.9k311457668


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.

permanent link

answered 03 Jan '12, 11:55

Volker%20Barth's gravatar image

Volker Barth
30.9k311457668
accept rate: 32%

edited 04 Jan '12, 03:36

Thanks for the term. It originally was happening on a subquery, but this was easier to recreate and I never changed it.

(03 Jan '12, 12:01) tmjac2

Thanks, works on 12.0.1.3519 (latest release).

(03 Jan '12, 14:42) tmjac2
Comment Text Removed
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412
×90
×35
×5

question asked: 03 Jan '12, 11:39

question was seen: 1,226 times

last updated: 17 Jan '12, 12:34