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

[Former Member]

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,

[Former Member · Accepted solution]

Hi Paul ,

I'm not sure about the importance of   " count(*) as PROGR1PST '

You can do the below approach . Add one more field Group and put some value for each union all to distinguish the groups .

select  field1, field 2,... 'first' as Group_field 

union all

select  field1, field 2,... 'Second' as Group_field 

---------

You can achieve your report by grouping on this group field and some more suppression logic using this field .

If you can provide me the front end sample copy ,I can advise .

Thanks,

Durga

[JWiseman]

hi Paul,

a union combines record sets like this

field1     field2

1          2

union

field3     field4

2          8

will produce

field1     field2

1          2

2          8

note that there are no more field3 & field4 names. that's the way a union works.

if you need separate fields for your other values then you would need to use sub-queries instead. to do that delete "UNION ALL " and create sub-queries with anything that is beneath that statement.