SQLTeam.com | Weblogs | Forums

Convert SQL from DB2

sql2014

#1

I hope this is the right category. I need to convert this SQL script from DB2 to SQL 2016.

The dummy system table I am thinking of just declaring variables and selecting them, but
I am having trouble with all of the nested LATERAL clauses. Not certain to use CROSS APPLY or a regular join etc. DB2 query is below:

with pltbrk as
( SELECT MainSQL.* ,L1.*
FROM
(SELECT PLCD, LINCTG,PRCSID, PRCSNM,
BRK1ST1,BRK1TM1,BRK1ST2,BRK1TM2,BRK1ST3,BRK1TM3,BRK1ST4,BRK1TM4,BRK1ST5,BRK1TM5,BRK1ST6,BRK1TM6,
BRK1ST7,BRK1TM7,BRK2ST1,BRK2TM1,BRK2ST2,BRK2TM2,BRK2ST3,BRK2TM3,BRK2ST4,BRK2TM4,BRK2ST5,
BRK2TM5,BRK2ST6,BRK2TM6,BRK2ST7,BRK2TM7,BRK3ST1,BRK3TM1,BRK3ST2,BRK3TM2,BRK3ST3,BRK3TM3,
BRK3ST4,BRK3TM4,BRK3ST5,BRK3TM5,BRK3ST6,BRK3TM6,BRK3ST7,BRK3TM7
FROM c01og_P )MainSQL,
Lateral(Select coalesce(
(Select EXTYP
from M14Exdp b
where b.PLCD = mainSQL.PLCD and b.LINCTG = MainSQL.LINCTG and b.Exdat = ?),0 ) as EXTYP
from sysibm.sysdummy1)L1
),
dummyrec as (
Select MainSQL.PLCD, MainSQL.PRCSID, L1.*
from (Select PLCD, PRCSID
from c01og_P
GROUP BY PLCD, PRCSID) mainSQL,
Lateral(Select cast(0 as smallint) as sortseq,cast('209912' as char(6)) as KDYM, cast('0000' as char(4)) as KDRRN,
cast(' ' as char(2)) as KDSFX,cast(' ' as char(1)) as YEAR,cast(' ' as char(3)) as MODEL,
cast(' ' as char(3)) as TYPE,cast(' ' as char(3)) as OPTION,cast('001' as char(3)) as LONSEQ,
cast(' ' as char(10)) as EXTCLR,cast(' ' as char(2)) as INTCLR,cast(1 as numeric(5,0)) as PRDQTY,
cast(20991231 as numeric(8,0)) as CRTDAT, cast(235959 as numeric(6,0)) as CRTTIM
FROM sysibm.sysdummy1)L1 ),
maxRec as (
Select L2.*
from (
Select PLCD, PRCSID,
max(substr(digits(crtdat)||digits(CRTTIM),1,4)||'-'||
substr(digits(crtdat)||digits(CRTTIM),5,2)||'-'||
substr(digits(crtdat)||digits(CRTTIM),7,2)||'-'||
substr(digits(crtdat)||digits(CRTTIM),9,2)||'.'||
substr(digits(crtdat)||digits(CRTTIM),11,2)||
'.00.000000'||LonSeq ) as sltrec
FROM t09ALCA6
WHERE PLCD in ({processCodes})
group by PLCD, PRCSID
)MainSQL,
Lateral(Select cast(max(rrn(a)) as integer) as sltrrn
from t09ALCA6 a
where a.PLCD = mainSQL.PLCD
and a.PRCSID = mainSQL.PRCSID
and substr(digits(a.crtdat)||digits(a.CRTTIM),1,4)||'-'||
substr(digits(a.crtdat)||digits(a.CRTTIM),5,2)||'-'||
substr(digits(a.crtdat)||digits(a.CRTTIM),7,2)||'-'||
substr(digits(a.crtdat)||digits(a.CRTTIM),9,2)||'.'||
substr(digits(a.crtdat)||digits(a.CRTTIM),11,2)||
'.00.000000'||a.LonSeq = sltrec
fetch first 1 row only)L1,
lateral(SELECT PLCD, PRCSID, cast(1 as smallint) as sortseq,
KDYM, KDRRN, KDSFX, YEAR, MODEL, TYPE,
OPTION, LONSEQ, EXTCLR, INTCLR, PRDQTY,
CRTDAT, CRTTIM
FROM t09ALCA6 b
where rrn(b) = sltrrn)l2 )
Select LX., MainSQL.
from (
Select * from pltbrk) mainSQL,
Lateral(
SELECT max(sortseq) as mxSeq
from (Select * from maxrec
union
Select * from dummyrec )a
where PLCD = MainSQL.PLCD
and PRCSID = MainSQL.PRCSID)L1,
lateral(Select *
from (Select * from maxrec
union
Select * from dummyrec )b
where b.PLCD = MainSQL.PLCD
and b.PRCSID = mainSQL.PRCSID
and b.sortSeq = L1.mxSeq)LX
ORDER BY MainSQL.PLCD, mainSQL.PRCSID

Does this seem overly complicated? Any advice?

Thanks,
Mike