Print different sets of data from different tables in one SQR

Question

I have a requirement where in if a PO_ID range is given then I need to do the following:

1. If a PO_ID from the PO_ID range is present in PS_DISTRIB_LINE then print PO_ID and Voucher ID and if not then print PI_ID and PO_DT from PS_PO_HDR.

How to achieve this.

Union is not working and I am not able to use break logic because if data is present in PS_DISTRIB_LINE then I am printing Sum of PO_AMT Total below the PO_IDs but in other case i am not.

Answer 1

UNION is possible in SQR, but the syntax is difficult. You will need to put the UNION in parenthesis and make sure to put commas after the selected items. In the example below, I put commas after a.name, a.first_name, and a.last_name. Repeat this for b.name, b.first_name, and b.last_name.

I've run this example and this works for me:

begin-SELECT
employee_name.name
employee_name.first_name
employee_name.last_name

    Show 'employee_name.name: ' &employee_name.name
    Show 'employee_name.first_name: ' &employee_name.first_name
    Show 'employee_name.last_name: ' &employee_name.last_name

from
(
select
a.name,
a.first_name,
a.last_name
from ps_names a
where name_type = 'PRI'
and emplid = '000000001'
union
select
b.name,
b.first_name,
b.last_name
from ps_personal_data b
where emplid = '000000001'
) employee_name
end-SELECT