Union ALL not giving the column\ names for second group

Question

I am using the tool SQLDBx. which i highly like btw. 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?

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,

Answer 1

I don't think you can get the result you want by "nesting" the SQL statements and joining them with UNION. This structure may work, depending upon your requirements:

 SELECT 'Name1' AS Label , COUNT(*) AS The_Count
   FROM table1
   WHERE ...
 UNION ALL
 SELECT 'PROGR2R2PST', COUNT(*)
   FROM table2
   WHERE ...
 UNION ALL
 SELECT 'Name3', COUNT(*)
   FROM table3
   WHERE ...

This will give you back one row per select:

 Label      The_Count
 --------------------
 Name1          45867
 PROGR2R2PST       22
 Name3           1234

Note that the column names come from the first select. If this format doesn't match your requirements, please be more explicit in the question or in comments and I will try to help.