Use a union SQL Query in Crystal Report

[former_member736866]

Hello everyone,

I have created a query in our DB2 database that includes a "UNION". I need to use this query in my Crystal report. Is there a way to create the query in Crystal Report with the union or should I just use Command?

Select *
from 
(select a.*
from
(
select 
                      ltrim(rtrim(substr(ppshipm.job_shipment_sm, 1, position(' ', ppshipm.job_shipment_sm)))) as job, 
                      max(ltrim(rtrim(paint_code_jobdet))) as paint_code,
                      max(ltrim(rtrim(d31.alpha_value_uf))) as Shipped_Day,
                      max(ppshipm.location_code_sm) as plant,
                      max(ltrim(rtrim(customer_name_jbmstr))) as customer, 
                      max(date_promised_jobdet) as promise_dt,
                      max(date_requested_jobdet) as requested_dt,
                      max(case when ppshipm2.date_shipped_sm is null
                      then
                      case when ppshipm3.date_shipped_sm is null
                      then ppshipm.date_shipped_sm
                      else ppshipm3.date_shipped_sm
                      end
                      else ppshipm2.date_shipped_sm
                       end) as ship_dt,  
                      max(price_curr_jbmstr) as sales_amount


               from 
                    jobscopedb.ppshipm as ppshipm
               left join jobscopedb.ppshipm ppshipm2 on
                      ppshipm2.carrier_sm not like '%KIT%' and
                      substr(ppshipm2.job_shipment_sm,1,position(' ', ppshipm2.job_shipment_sm)) = 
                              substr(ppshipm.job_shipment_sm,1, position(' ', ppshipm.job_shipment_sm))
                left join jobscopedb.ppshipm ppshipm3 on
                 ppshipm3.carrier_sm like '%KIT%' and
                 substr(ppshipm3.job_shipment_sm,1,position(' ', ppshipm3.job_shipment_sm)) =
                  substr(ppshipm.job_shipment_sm,1, position(' ', ppshipm.job_shipment_sm))
               join jobscopedb.ipjobm on 
                      substr(ppshipm.job_shipment_sm,1, position(' ', ppshipm.job_shipment_sm)) = 
                              job_number_jbmstr and
                      tax_exempt_1_jbmstr like '%/%' and
                      percent_jbmstr = 100
               join jbsmod.ssacrpdrl1 on 
                      acrjob = job_number_jbmstr
               join jobscopedb.ppjobd on 
                      substr(ppshipm.job_shipment_sm,1, position(' ', ppshipm.job_shipment_sm)) = 
                              release_jobdet
               left join jbsmod.aj_d31t d31 on 
                      substr(ppshipm.job_shipment_sm,1, position(' ', ppshipm.job_shipment_sm)) = 
                              ltrim(rtrim(d31.search_key_uf))
where ppshipm.location_code_sm ='06'
group by 
                      ltrim(rtrim(substr(ppshipm.job_shipment_sm, 1, position(' ', ppshipm.job_shipment_sm))))
               order by 
                      ship_dt)
as a


where a.ship_dt between  20210201 and 20210231

UNION

select a.*
from 
(
select
                ltrim(rtrim(job_number_jbmstr)) as job,
                max(ltrim(rtrim(paint_code_jobdet))) as paint_code,
                max(ltrim(rtrim(d31.alpha_value_uf))) as shipped_day,
                max(company_code_jbmstr) as plant,
                max(ltrim(rtrim(customer_name_jbmstr))) as customer,
                max(date_promised_jobdet) as promise_dt,
                max(date_requested_jobdet) as requested_dt,
max(case when coalesce((SELECT MAX(date_shipped_sm) FROM jobscopedb.ppshipm WHERE ((carrier_sm like '%KIT%') and ltrim(rtrim(substr(job_shipment_sm,1, position(' ', job_shipment_sm)))) = ltrim(rtrim(job_number_jbmstr)))), 0) = 0 then 99999999
                    else
(SELECT MAX(date_shipped_sm) FROM jobscopedb.ppshipm WHERE ((carrier_sm NOT like '%KIT%') and ltrim(rtrim(substr(job_shipment_sm,1, position(' ', job_shipment_sm)))) = ltrim(rtrim(job_number_jbmstr))))
                end) as ship_dt,
                              max(price_curr_jbmstr) as sales_amount
            from jobscopedb.ipjobm 
            inner join jobscopedb.ppjobd on 
                job_number_jbmstr = release_jobdet 
            left outer join (
                select
                    max(date_shipped_sm) as ship_dt,
                    ltrim(rtrim(substr(job_shipment_sm,1, position(' ', job_shipment_sm)))) as job
                from
                    jobscopedb.ppshipm
                where
                    carrier_sm like '%KIT%'
                group by
                   ltrim(rtrim(substr(job_shipment_sm,1, position(' ', job_shipment_sm))))
            ) sm on 
                                                            sm.job = ltrim(rtrim(job_number_jbmstr))
            left outer join jbsmod.aj_d31t d31 on 
                ltrim(rtrim(job_number_jbmstr)) = ltrim(rtrim(d31.search_key_uf))
          where company_code_jbmstr  = '06'


            group by 
                job_number_jbmstr
            order by 
                job_number_jbmstr
) as a
where a.promise_dt between 20210201 and 20210231
and a.ship_dt = '99999999'
and a.shipped_day != ' '
and a.requested_dt != '0'
)
order by ship_dt, job  

[ido_millet]

Use a Command in Crystal or expose the query as a VIEW or SP in the DBMS.

Side note: unless you need to remove duplicates you can get a big performance boost by changing 'UNION' to 'UNION ALL'.