asked on

DB2 SQL Question about Union All in a sub select

I am trying to do a Union All in a sub select in DB2 and it is failing.

The structure of the SQL looks like this:

Select A, B, C, Sum(d)
FROM
((Select A, B, C, D
FROM some tables)

UNION ALL

(Select A, B, C, D
FROM some tables)) AS AA
WHERE AA.A = some value
Group By A, B, C

This doesn't work and I don't understand why. If I remove the Union ALL and the second sub select, it works fine. If I run each piece by itself, they all work fine.

Any ideas?

Thanks.

mglxxx

Does this work:

with aa (a,b,c,d) as (
select a,b,c,d
from some_table
union all
select a,b,c,d
from some_other_table
)
select a,b,c,sum(d)
from aa a
where a.a = some_value
group by a, b, c

wfinn

ASKER

I get the same error:

Err.number=-2147217900
Err.Description = [IBM][Client Access ODBC Driver (32-bit)][DB2/400 SQL]SQL0199 - Keyword UNION not expected. Valid tokens: ).

If I remove the Union All and the second sub select, it does work. It is the same result as the original query I had submitted.

Thanks.

MFC123

wfinn,
The following statement works from db2 command prompt (v8). Did you try to issue the sql from command prompt ?

select a,b,c, sum(d) from ( (select a,b,c,d from t1) union all (select a,b,c,d from t2)) as aa where aa.a = 1 group by a,b,c

mglxxx

Could you post the original SQL statement, i.e. exactly
the one which doesn't work?
The 'union' should work in DB2/400.

wfinn

ASKER

Okay, here are the table definitions and the original SQL I am using. Just on a side note, the version of DB2 I am using is AS400 version 4.5

TenCharges
(PropertyID Char(9),
LeaseID Char(9),
Unit Char(9),
SpaceSeq Dec(12,2),
ChargeCode Char(3),
SpaceType Char(3),
StartDate Date,
EndDate Date,
ChargeEnd Date,
Frequency Char(3),
ChgPSF Dec(12,2),
ChgPerYear Dec(12,2),
ChgPerMth Dec(12,2),
ChgDesc Char(30),
ChgGroup Char(30));

Tenants
(PropertyID char(9),
TenantID char(9),
LeaseID char(9),
TenantName char(30),
OperAsName char(30),
CorpName char(30),
LeaseStart date,
LeaseEnd date,
OccStart date,
OccEnd date);

Expiries
(PropertyID Char(9),
LeaseID Char(9),
SpaceSeq Dec(12,2),
Floor Char(9),
SpaceTypeCD Char(3),
Unit Char(9),
SpaceStart Date,
SpaceEnd Date,
RentableArea Dec(12,2),
RenewedArea Dec(12,2),
ExpiredArea Dec(12,2),
StatArea Char(1),
NewRen Char(10));

select
A.PropertyID
,A.LeaseID
,A.OperAsName
,A.Unit
,A.SpaceTypeCd
,A.RentableArea
,A.SpaceStart
,A.SpaceEnd
,A.Type
,sum(A.ChgPSF) as ChgPSF
From

(Select EXP.PropertyID
,EXP.LeaseID
,EXP.OperAsName
,EXP.Unit
,EXP.SpaceTypeCD
,EXp.RentableArea
,TENC.ChgPSF
,EXP.SpaceStart
,EXP.SpaceEnd
,'Expiry' as Type
FROM
(select Expiries.PropertyID,
Tenants.LeaseID
,Tenants.OperAsName
,Expiries.Unit
,Expiries.RentableArea
,Expiries.SpaceStart
,Expiries.SpaceEnd
,Expiries.SpaceTypeCD
,Expiries.SpaceSEQ
from
Expiries
,Tenants
Where Expiries.PropertyID = Tenants.PropertyID and
Expiries.LeaseID = Tenants.LeaseID) as EXP

Left Outer Join

(Select PropertyID,
LeaseID
,ChgPSF
,ChargeEND
,SpaceSEQ
,ChgGroup
From TenCharges) AS TENC

ON (EXP.PropertyID = TENC.PropertyID
and EXP.LeaseID=TENC.LeaseID
and EXP.SpaceSEQ=TENC.SpaceSEQ
and EXP.SpaceEnd=TENC.ChargeEnd
and TENC.Chggroup='Base Rent')

UNION ALL

(Select NR.PropertyID
,NR.LeaseID
,NR.OperAsName
,NR.Unit
,NR.SpaceTypeCD
,NR.RentableArea
,TENC1.ChgPSF
,NR.SpaceStart
,NR.SpaceEnd
,(Case When NR.NewRen='New' then 'New Leasing' else 'Renewals' end) as Type
FROM
(select Expiries.PropertyID,
Tenants.LeaseID
,Tenants.OperAsName
,Expiries.Unit
,Expiries.RentableArea
,Expiries.SpaceStart
,Expiries.SpaceEnd
,Expiries.SpaceTypeCD
,Expiries.SpaceSEQ
,Expiries.NewRen
from
Expiries
,Tenants
Where Expiries.PropertyID = Tenants.PropertyID and
Expiries.LeaseID = Tenants.LeaseID) as NR

Left Outer Join

(Select PropertyID,
LeaseID
,ChgPSF
,ChargeEND
,SpaceSEQ
,ChgGroup
From TenCharges) AS TENC1

ON (NR.PropertyID = TENC1.PropertyID
and NR.LeaseID=TENC1.LeaseID
and NR.SpaceSEQ=TENC1.SpaceSEQ
and NR.SpaceEnd=TENC1.ChargeEnd
and TENC1.Chggroup='Base Rent'
and (NR.NewRen = 'New' or
NR.NewRen = 'Renewal')))) AS A

WHERE
A.PropertyID='0001' and
A.SpaceEnd >= Date('01/01/2003') and A.SpaceEnd < Date('01/01/2004')
Group By A.PropertyID, A.LeaseID, A.OperAsName, A.Unit, A.SpaceTypeCd, A.RentableArea, A.SpaceStart, A.SpaceEnd, Type

ASKER CERTIFIED SOLUTION

mglxxx

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial