Broken Connection - Union Query

[infomania]

Using SQL Server 2000 with JDEdwards OneWorld

I keep getting the following error message when I try to run a union query:

Server: Msg 21, Level 20, State 1, Line 5
Warning: Fatal error 1203 occurred at Jun 17 2003 11:00PM

Connection Broken

The indiviual select statements run OK and two in combination but adding the third caused the error. Below is the query. Any ideas to solve this?


select
abac11
, sales=0
, cost=0
, profit=0
, orders=0
, pysales=0
, plansales=0
, sum(sduorg*sduprc)/10000 bkLogAmt

from proddta.f4211 sd
left join proddta.f0101 ab
on sdan8=aban8

where
abac11 not in ('MG','CH')
and sdtrdj>103001
and sddrqj<=103181
and sdivd=0
and sddcto='SO' and sdsocn=0 and sdktln=0
and sdlnty not in ('f','m','rs')

group by
abac11

having abac11!=''

union

select
abac11
, sales=sum(sdaexp)/100
, cost=sum(sdecst)/100
, profit=sum(sdaexp-sdecst)/100
, orders=count(distinct(sddoco))
, pysales=0
, plansales=0
, bkLogAmt=0

from proddta.f4211 sd
left join proddta.f0101 ab
on sdan8=aban8

where
abac11 not in ('MG','CH')
and sdivd>=103152 and sdivd<=103168
and sddct='ri' and sdsocn=0 and sdktln=0
and sdlnty not in ('f','m','rs')

group by
abac11

having abac11!=''

union

select
abac11
, sales=0
, cost=0
, profit=0
, orders=0
, pysales=sum(sdaexp)/100
, plansales=0
, bkLogAmt=0

from proddta.f4211 sd
left join proddta.f0101 ab
on sdan8=aban8

where
abac11 not in ('MG','CH')
and sdivd>=103152 and sdivd<=103168
and sddct='ri' and sdsocn=0 and sdktln=0
and sdlnty not in ('f','m','rs')

group by
abac11

having abac11!=''

 

[AWithers]

might be a connection time out issue...i.e. the lenght of time required to execute all unioned expressions is too long

Andy

 

[infomania]

No... I don't think that's it. Each individual query completes in less than 10 sec. I'm getting the feeling that the broken connection has somedthing to do with the connection between the UNION queries, specifically the abac11 field (String,3). Perhaps if I do a ltrim(abac11) I can make this work (compensating for white space in the field).

Aahh! Success! That seems to be the problem.

Thank you for your interest.