Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1691
  • Last Modified:

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.
0
wfinn
Asked:
wfinn
  • 3
  • 2
1 Solution
 
mglxxxCommented:
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
 
wfinnAuthor Commented:
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
 
MFC123Commented:
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
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
mglxxxCommented:
Could you post the original SQL statement, i.e. exactly
the one which doesn't work?
The 'union' should work in DB2/400.
0
 
wfinnAuthor Commented:
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
 
mglxxxCommented:
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now