?
Solved

DB2 SQL Question about Union All in a sub select

Posted on 2003-02-20
6
Medium Priority
?
1,681 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:wfinn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:mglxxx
ID: 7992919
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
0
 

Author Comment

by:wfinn
ID: 8008725
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.
0
 
LVL 1

Expert Comment

by:MFC123
ID: 8022373
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
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 9

Expert Comment

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

Author Comment

by:wfinn
ID: 8057733
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
0
 
LVL 9

Accepted Solution

by:
mglxxx earned 800 total points
ID: 8060243
This is a statement which should do the same as your
statement but avoiding the union:

with jnd (propertyID,
          leaseID,
          operAsName,
          unit,
          spaceTypeCD,
          rentableArea,
          spaceStart,
          spaceEnd,
          type,
          chgPSF) as (
select e.propertyID,
       e.leaseID,
       t.operAsName,
       e.unit,
       e.spaceTypeCD,
       e.rentableArea,
       e.spaceStart,
       e.spaceEnd,
       case
         when e.newRen = 'New' then 'New Leasing'
         when e.newRen = 'Renewal' then 'Renewals'
         else 'Expiry'
       end as type,
       tc.chgPSF
from (expiries e inner join tenants t
       on e.propertyID = t.propertyID
      and e.leaseID = t.leaseID) left outer join tencharges tc
       on e.propertyID = tc.propertyID
      and e.leaseID = tc.leaseID
      and e.spaceSeq = tc.spaceSeq
      and e.spaceEnd = tc.chargeEnd
where tc.chgGroup = 'Base Rent'
)
select propertyID,
       leaseID,
       operAsName,
       unit,
       spaceTypeCd,
       rentableArea,
       spaceStart,
       spaceEnd,
       type,
       sum(chgPSF) as chgPSF
from jnd
where propertyID = '0001'
  and spaceEnd >= date('01/01/2003')
  and spaceEnd <  date('01/01/2004')
group by propertyID,
         leaseID,
         operAsName,
         unit,
         spaceTypeCd,
         rentableArea,
         spaceStart,
         spaceEnd,
         type;
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question