Converting DB2 query to Microsoft Database

[ms901Boss]

I have a query that I created in IBM DB2 database that display all report that are within the parameter 202010201 to 20210228 which is the Promise Date. I need to convert this code to Microsoft server.

Here is the IBM DB2 code:

(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

Here is the Microsoft code that I tried to convert:

((select a.*
from 
(
select
                ltrim(rtrim(job_number)) as job,
                max(ltrim(rtrim(paint_code))) as paint_code,
                max(ltrim(rtrim(d31.ALPHAVALUE))) as shipped_day,
                max(company_code) as plant,
                max(ltrim(rtrim(customer_name))) as customer,
                max(date_promised) as promise_dt,
                max(date_requested) as requested_dt,
max(case when coalesce((SELECT MAX(date_shipped) FROM JobscopeMeyer.dbo.PPSHIPM WHERE ((carrier like '%KIT%') and ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment)))) = ltrim(rtrim(job_number)))), 0) = 0 then 99999999
                    else
(SELECT MAX(date_shipped) FROM JobscopeMeyer.dbo.PPSHIPM WHERE ((carrier NOT like '%KIT%') and ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment)))) = ltrim(rtrim(job_number))))
                end) as ship_dt,
                              max(price_curr) as sales_amount
            from JobscopeMeyer.dbo.ipjobm 
            inner join JobscopeMeyer.dbo.ppjobd on 
                job_number = release 
            left outer join (
                select
                    max(date_shipped) as ship_dt,
                    ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment)))) as job
                from
                    JobscopeMeyer.dbo.PPSHIPM
                where
                    carrier like '%KIT%'
                group by
                   ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment))))
            ) sm on 
                                                            sm.job = ltrim(rtrim(job_number))
            left outer join JobscopeMeyer.dbo.AJ_D31 d31 on 
                ltrim(rtrim(job_number)) = ltrim(rtrim(d31.SEARCHKEY))
          where company_code  = '06'

            group by 
                job_number
  --          order by 
    --            job_number
) 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

When I run this code I get an errors:
Msg 130, Level 15, State 1, Line 12
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Msg 130, Level 15, State 1, Line 12
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Msg 130, Level 15, State 1, Line 15
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I'm new to Microsoft query writing and not sure how to correct this issue.

 

[fredericofonseca]

thats a big of a mess query.

get a bit of good practice there and ensure that all tables have an alias and that ALL columns from any table are then named with that alias. I look at this query and would not know which columns are from what table - and in some cases not having the alias may mean you use the WRONG column by mistake and your results are not quite what you expect.

Second - the left outer join you have as a alias of SM is not currently being used in both DB2 and SQL query.

third - do not use 3 part naming convention - if you need to reference a table on another database use a synonym with a generic name that is not tied to the database name itself. Makes code a lot easier to change should the database name change in the future e.g. only synonym needs to be changed without affecting any query/code.


regarding the error - this MAY~ fix it - untested
there may be better ways to do this but without table definitions (including indexes) and row counts its hard to identify possible alternatives.

select a.*
from ( select ltrim(rtrim(job_number)) as job
            , max(ltrim(rtrim(paint_code))) as paint_code
            , max(ltrim(rtrim(d31.ALPHAVALUE))) as shipped_day
            , max(company_code) as plant
            , max(ltrim(rtrim(customer_name))) as customer
            , max(date_promised) as promise_dt
            , max(date_requested) as requested_dt
            , max(case
                  when sm.ship_dt = 0 then 99999999
                  else sm_2_ship_dt
                  end
                 ) as ship_dt
            , max(price_curr) as sales_amount
     from JobscopeMeyer.dbo.ipjobm 
     inner join JobscopeMeyer.dbo.ppjobd
        on job_number = release 
     left outer join (select max(date_shipped) as ship_dt
                           , ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment)))) as job
                      from JobscopeMeyer.dbo.PPSHIPM
                      where carrier like '%KIT%'
                      group by ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment))))
                     ) sm
     on sm.job = ltrim(rtrim(job_number))
     left outer join (select max(date_shipped) as ship_dt
                           , ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment)))) as job
                      from JobscopeMeyer.dbo.PPSHIPM
                      where carrier not like '%KIT%'
                      group by ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment))))
                     ) sm_2
     on sm_2.job = ltrim(rtrim(job_number))
     left outer join JobscopeMeyer.dbo.AJ_D31 d31
     on ltrim(rtrim(job_number)) = ltrim(rtrim(d31.SEARCHKEY))
     where company_code  = '06'
     group by job_number
     --          order by 
     --            job_number
) 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 a.ship_dt
       , a.job

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886