Skip to Content
0
Former Member
Jul 03, 2014 at 10:12 PM

Union All is not bring the second part columns to the report

14 Views

I have 5 groups of series of Select statements and then I made a UNION ALL after the first group. Now it does run correctly and displays the output but it does not assign the second group' s field names.

For example. I am greatly abridging

Completed the SQL's needed to mimic a IBM i display screen. There are 5 product groups making up the screen. I was hoping to have one large Command SQL having the 5 SQLs using UNION ALL. This does 'compile' as a Command. However, it does only brings in the first part fields not the second. So this field is not included in the list of fields tree for COMMAND. PROGR2R2PST,

Is there something not correct how doing the UNION ALL? OUTPUT assigns the column name

of the first group to the second group. HLDGR1PUN 21454 87273

so if i wanted to have one large SQL with union ALLs, it wont work. is there something other than UNION ALL I should use? One idea is if I can have 5 commands to my disposal. I guess using subreports you can but it will be too slow.

SELECT

count(*) as PROGR1PST,

(

SELECT COALESCE(SUM(OdQty#),0)

FROM ASTCCDTA.OEORH48,ASTCCDTA.TRNSTAT2,ASTDTA.OEORD1

WHERE OHCOM# = TSCOM# AND OHORD# = TSORD# AND OHCOM# = ODCOM# AND OHORD# = ODORD#

AND TSSTAT IN('AEP','BGE')

AND OHORDT IN('RTR','INT','SAM')

AND OHREQD < replace(char(current date, iso), '-', '')

AND OHHLDC = ' '

AND ODPRLC = 'ENG'

AND substr(odprt#,1,5) <> 'NOENG' AND OHORD# in(SELECT a.TSORD# FROM ASTCCDTA.TRNSTAT2 a

WHERE a.tsstat IN('AEP','BGE','EAS','REL','STP'))

) AS PROGR1PUN,

(

SELECT count(*)

FROM ASTCCDTA.OEORH48,ASTCCDTA.TRNSTAT2,ASTCCDTA.OETRA99

WHERE OHCOM# = TSCOM# AND OHORD# = TSORD#

AND (otCOM# = OHCOM# AND OTORD#= OHORD# AND ottrnc = 'AQC')

AND TSSTAT IN('AEP','BGE')

AND OHORDT IN('RTR','INT','SAM')

AND OHREQD = replace(char(current date, iso), '-', '') AND OHHLDC = ' ' AND OHORD# in(SELECT a.TSORD# FROM ASTCCDTA.TRNSTAT2 a

WHERE a.tsstat IN('AEP','BGE','EAS','REL','STP'))

) AS PROGR1TOD,

(

etc..

UNION ALL

SELECT

count(*) as PROGR2R2PST,

(SELECT COALESCE(SUM(OdQty#),0) FROM ASTCCDTA.OEORH48,ASTCCDTA.TRNSTAT2,ASTDTA.OEORD1

WHERE OHCOM# = TSCOM# AND OHORD# = TSORD# AND OHCOM# = ODCOM# AND OHORD# = ODORD#

AND TSSTAT IN('AEP','BGE')

AND OHORDT IN('CUS','CIN','SMC','COC','DON')

AND OHREQD < replace(char(current date, iso), '-', '')

AND OHHLDC = ' '

AND ODPRLC = 'ENG'

AND substr(odprt#,1,5) <> 'NOENG' AND OHORD# in(SELECT a.TSORD# FROM ASTCCDTA.TRNSTAT2 a

WHERE a.tsstat IN('AEP','BGE','EAS','REL','STP'))

) AS PROGR2PUN,