Migrate Sybase outer join query to UDB DB2 8.2

[kumar_rangan1976]

I need the below sybase code to be migrated in UDB :

select distinct
c.partnumber as I_PART,
case
when d.IntegratorID = 'DCX05' then 'U'
when d.IntegratorID = 'DCX04' then 'M'
when d.IntegratorID = 'DCX03' then 'E'
else ' ' end as 'C_LU_SRCE_PCHS NG',
o.SupplierCode as I_SUPLR_LOC,
c.ecn as I_PCHNG,
c.Revision as I_PART_CHNG_LVL ,
a.ProjectNumber as I_PROJ,
q.N_SUPLR,
a.CustomerManag er as N_MGR_CUST,
a.SupplierManag er as N_MGR_SUPLR,
a.IndustryDescr iption as X_INDUS_DESC,
a.Description as X_PROJ_DESC,
a.Title as X_TI,
a.DesignRespons ibility as X_RESPLTY_DSGN,
a.MRD as D_MATL_REDQ,
a.CreationDate as D_CREATN,
a.RiskDescripti on as C_RISK,
a.ProjectType as C_PROJ_TYP,
a.CustomerStatu s as C_PROJ_STAT,
a.CustomerOpini on as C_PROJ_CUST_OP,
a.SupplierStatu s as C_SUPLR_STAT,
a.SupplierOpini on as C_PROJ_SUPLR_OP ,
a.RiskToPending Date as D_PEND_RISKTO,
a.MethodologyNa me as N_METHD,
a.MethodologyRe visionNumber as A_REV_METH,
c.Name as N_PART,
c.Status as C_PART_STAT,
c.Catagory as C_PART_CATGY,
c.effectivedate as D_EFF_IN,
c.expirationdat e as D_EFF_OUT,
"X_PART_DES C" = rtrim(c.Name) + ' - ' +
rtrim(c.Descrip tion),
c.weight as Q_PART_WGT,
c.weightUOM as C_WGT_UM,
c.ChangeReason as C_PART_CHNG_REA S,
c.ModelYearOrde red as I_MOD_YR_1,
c.ModelYearRequ ired as I_MOD_YR_2,
c.PartType as C_PART_TYP,
h.ExpectedDate as D_DIM_EXPCTD,
h.PromiseDate as D_DIM_PROM,
h.ActualDate as D_DIM_ACTL,
h.Disposition as C_DIM_DISP,
h.ThirdPartyLab as C_DIM_PTY_THRD,
i.ExpectedDate as D_LAB_EXPCTD,
i.PromiseDate as D_LAB_PROM,
i.ActualDate as D_LAB_ACTL,
i.Disposition as C_LAB_DISP,
i.ThirdPartyLab as C_LAB_PTY_THRD,
j.ExpectedDate as D_MATL_EXPCTD,
j.PromiseDate as D_MATL_PROM,
j.ActualDate as D_MATL_ATCL,
j.Disposition as C_MATL_DISP,
j.ThirdPartyLab as C_MATL_PTY_THRD ,
k.ExpectedDate as D_PART_EXPCTD,
k.PromiseDate as D_PART_PROM,
k.ActualDate as D_PART_ACTL,
k.Disposition as C_PART_DISP,
k.ThirdPartyLab as C_PART_PTY_THRD ,
m.ExpectedDate as D_APPRNC_EXPCTD ,
m.PromiseDate as D_APPRNC_PROM,
m.ActualDate as D_APPRNC_ACTL,
m.Disposition as C_APPRNC_DISP,
m.ThirdPartyLab as C_APPRNC_PTY_TH RD,
"L_DIML_LAB_REQ D" = ' '
from E12WS..Project a,
E12WS..ProjectP art2 b,
E12WS..Part c,
E12WS..Tenant d,
E12WS..PPAP h,
E12WS..PPAP i,
E12WS..PPAP j,
E12WS..PWTMP1 l,
E12WS..PPAP k,
E12WS..PPAP m,
E125S..EPQPTSP_ TBL n,
E12WS..ProjectS upplier o,
E127S..EPQSUPL_ TBL q
where a.ProjectNumber = b.ProjectNumber
and b.PartID = c.PartID
and b.PartNumberID = c.PartNumberID
and b.PartNumberID *= h.PartNumberID
and b.PartNumberID *= i.PartNumberID
and b.PartNumberID *= j.PartNumberID
and b.PartNumberID *= k.PartNumberID
and b.PartNumberID *= m.PartNumberID
and a.CustomerID = d.TenantID
and c.partnumber = n.I_PART
and o.SupplierCode = n.I_SUPLR_LOC
and o.SupplierCode = q.I_SUPLR_LOC
and a.ProjectNumber *= h.ProjectNumber
and a.ProjectNumber *= i.ProjectNumber
and a.ProjectNumber *= j.ProjectNumber
and a.ProjectNumber *= k.ProjectNumber
and a.ProjectNumber *= m.ProjectNumber
and a.ProjectNumber = o.ProjectNumber
and l.Dim_Test *= h.TestType
and l.Lab_Test *= i.TestType
and l.Matl_Test *= j.TestType
and l.Part_Test *= k.TestType
and l.App_Test *= m.TestType

[Tonkuma]

I'm sorry, if the following statements are difficult to understand
because of my poor English.
My experience of Sybase is only that I developed one system more than 5
years ago.
So, I may misundestand or not know some special function of join of
Sybase.
But, if meaning of "*=" is usual left outer join and there is no side
effect nor extra functionality, I have some questions for original
Sybase SQL.
1) Table h, i, j, k and m are actualy same table E12WS..PPAP and
conditions for these tables are same and columns in select list are
also same.
Why need repeat completely same things.
2) Table E12WS..PWTMP1 l has no relation with other tables except
outer table in left outer join with h, i, j, k and m.
All rows of outer table of outer join will be selected into result set,
if there is no condition in WHERE clause. So, all rows of table
E12WS..PWTMP1 l will be selected and make cartesian product with result
of joining tables a, b, c, d, n ,o and q.
Original SQL's select list use distinct and no column of table l
appears in the select list.
That means It is no need to join E12WS..PWTMP1 l.
3) If some rows in E12WS..PPAP will be selected for one row of
combination a, c, d, o and q, all permutation of the selected rows of
E12WS..PPAP for each row of combination of a, c, d, o and q will appear
in result set. Is it really neccesary results?

[Tonkuma]

Put aside my previous questions.

It may be not necessary to modify select list on DB2 except two.

Sybase: "X_PART_DES C" = rtrim(c.Name) + ' - ' +
rtrim(c.Descrip tion),
DB2: rtrim(c.Name) || ' - ' || rtrim(c.Descrip tion) AS "X_PART_DES C",

Sybase: "L_DIML_LAB_REQ D" = ' '
DB2: ' ' AS "L_DIML_LAB_REQ D"

My idea for FROM clause is here:

FROM E12WS.Project a
INNER JOIN
E12WS.ProjectPa rt2 b
ON a.ProjectNumber = b.ProjectNumber
INNER JOIN
E12WS.Part c
ON b.PartID = c.PartID
AND b.PartNumberID = c.PartNumberID
INNER JOIN
E12WS.Tenant d
ON a.CustomerID = d.TenantID
INNER JOIN
E12WS.ProjectSu pplier o
ON a.ProjectNumber = o.ProjectNumber
INNER JOIN
E125S.EPQPTSP_T BL n
ON c.partnumber = n.I_PART
AND o.SupplierCode = n.I_SUPLR_LOC
INNER JOIN
E127S.EPQSUPL_T BL q
ON o.SupplierCode = q.I_SUPLR_LOC
INNER JOIN
E12WS.PWTMP1 l
ON 0=0
LEFT OUTER JOIN
E12WS.PPAP h
ON a.ProjectNumber = h.ProjectNumber
AND b.PartNumberID = h.PartNumberID
AND l.Dim_Test = h.TestType
LEFT OUTER JOIN
E12WS.PPAP i
ON a.ProjectNumber = i.ProjectNumber
AND b.PartNumberID = i.PartNumberID
AND l.Dim_Test = i.TestType
LEFT OUTER JOIN
E12WS.PPAP j
ON a.ProjectNumber = j.ProjectNumber
AND b.PartNumberID = j.PartNumberID
AND l.Dim_Test = j.TestType
LEFT OUTER JOIN
E12WS.PPAP k
ON a.ProjectNumber = k.ProjectNumber
AND b.PartNumberID = k.PartNumberID
AND l.Dim_Test = k.TestType
LEFT OUTER JOIN
E12WS.PPAP m
ON a.ProjectNumber = m.ProjectNumber
AND b.PartNumberID = m.PartNumberID
AND l.Dim_Test = m.TestType

[kumar_rangan1976]

Thanks a lot... Could you please provide DB2 code the below query.
select .....
from
E126S.dbo.EPQST RU_TBL T1,
E126S.dbo.EPQPA TT_TBL T2,
E126S.dbo.EPQPO SP_TBL T3,
E126S.dbo.EPQSU PL_TBL T4 ,
E126S.dbo.EPQPS CF_TBL T5 ,
E126S.dbo.EPQPT CY_TBL T8,
E126S.dbo.EPQCO MP_TBL T7,
E126S.dbo.EPQTO OL_TBL Tpo ,
E126S.dbo.EPQRE PT_TBL T10,
E126S.dbo.EPQTL DT_TBL Tt
where T1.I_PART *= Tt.I_PART and
T1.I_PART = T2.I_PART and
T1.I_PART = T3.I_PART and
T1.I_PART *= T7.I_PART and
T3.C_LU_SRCE_PC HSNG *= T7.C_LU_SRCE_PC HSNG and
T1.I_PART = T8.I_PART and
T1.I_PART = T10.I_PART and
T3.I_PART = T10.I_PART and
T3.C_LU_SRCE_PC HSNG = T8.C_LU_SRCE_PC HSNG and
T3.C_LU_SRCE_PC HSNG = T10.C_LU_SRCE_P CHSNG and
substring(T1.I_ PART,1,8) *= Tpo.I_PART_1_8 and
T1.I_MOD_YR *= Tpo.I_MOD_YR and
T1.C_FAM *= Tpo.C_FAM and
T1.I_PART *= T5.I_PART and
T1.I_MOD_YR *= T5.I_MOD_YR and
T3.I_MOD_YR = (select min(T6.I_MOD_YR ) from E126S.dbo.EPQPO SP_TBL T6
where T6.I_PART = T1.I_PART) and
T3.I_SUPLR_MFG = T4.I_SUPLR_LOC and
T1.I_MOD_YR > 2004 and
T1.L_DEASGD = ' ' and
T1.C_STAGE_DVLP ='3' and
(T1.C_LU_PROC_1 in ('00','06','07' ,'21','35','14' ,'01','28','15' ) or
T1.C_LU_PROC_2 in ('00','06','07' ,'21','35','14' ,'01','28','15' )) and
((T1.C_LU_SRCE_ 1 like 'V%' and T1.C_LU_SRCE_1 not like 'VR%' ) or
(T1.C_LU_SRCE_2 like 'V%' and T1.C_LU_SRCE_2 not like 'VR%') ) and
T10.C_LU_PROC + T10.C_LU_SRCE + T10.C_LU_DEST <> '00VAMR' and
(T1.I_PART not in (select T12.I_PART from E126S.dbo.EPQBU YR_TBL T12
where
T12.C_LU_SRCE_P CHSNG = T3.C_LU_SRCE_PC HSNG ) or
T1.I_PART in (select T12.I_PART from E126S.dbo.EPQBU YR_TBL T12
where T12.C_LU_SRCE_P CHSNG = T3.C_LU_SRCE_PC HSNG and
T12.I_DECK_1_2 in ('AB','BB','EB' ,'MB','UB','VB' ) ) ) and
T1.I_PART in (select T11.I_PART from E126S.dbo.EPQPG PP_TBL T11
where T11.I_MOD_YR = T1.I_MOD_YR and T11.C_FAM = T1.C_FAM) and
T10.L_ACTV = 'Y'

[Tonkuma]

select .....
from
E126S.dbo.EPQST RU_TBL T1
INNER JOIN
E126S.dbo.EPQPA TT_TBL T2
ON T1.I_PART = T2.I_PART
INNER JOIN
E126S.dbo.EPQPO SP_TBL T3
ON T1.I_PART = T3.I_PART
INNER JOIN
E126S.dbo.EPQSU PL_TBL T4
ON T3.I_SUPLR_MFG = T4.I_SUPLR_LOC
INNER JOIN
E126S.dbo.EPQPT CY_TBL T8
ON T1.I_PART = T8.I_PART
AND T3.C_LU_SRCE_PC HSNG = T8.C_LU_SRCE_PC HSNG
INNER JOIN
E126S.dbo.EPQRE PT_TBL T10
ON T1.I_PART = T10.I_PART
AND T3.I_PART = T10.I_PART
AND T3.C_LU_SRCE_PC HSNG = T10.C_LU_SRCE_P CHSNG
LEFT OUTER JOIN
E126S.dbo.EPQPS CF_TBL T5
ON T1.I_PART = T5.I_PART
AND T1.I_MOD_YR = T5.I_MOD_YR
LEFT OUTER JOIN
E126S.dbo.EPQCO MP_TBL T7
ON T1.I_PART = T7.I_PART
AND T3.C_LU_SRCE_PC HSNG = T7.C_LU_SRCE_PC HSNG
LEFT OUTER JOIN
E126S.dbo.EPQTO OL_TBL Tpo
ON substr(T1.I_PAR T,1,8) = Tpo.I_PART_1_8
AND T1.I_MOD_YR = Tpo.I_MOD_YR
AND T1.C_FAM = Tpo.C_FAM
LEFT OUTER JOIN
E126S.dbo.EPQTL DT_TBL Tt
ON T1.I_PART = Tt.I_PART
where
T3.I_MOD_YR
= (select min(T6.I_MOD_YR )
from E126S.dbo.EPQPO SP_TBL T6
where T6.I_PART = T1.I_PART
)
and T1.I_MOD_YR > 2004
and T1.L_DEASGD = ' '
and T1.C_STAGE_DVLP ='3'
and (T1.C_LU_PROC_1 in
('00','06','07' ,'21','35','14' ,'01','28','15' )
or
T1.C_LU_PROC_2 in
('00','06','07' ,'21','35','14' ,'01','28','15' )
)
and (SUBSTR(T1.C_LU _SRCE_1,1,1) = 'V' and SUBSTR(T1.C_LU_ SRCE_1,1,2)
<> 'VR'
or
SUBSTR(T1.C_LU_ SRCE_2,1,1) = 'V' and SUBSTR(T1.C_LU_ SRCE_2,1,2)
<> 'VR'
)
and T10.C_LU_PROC || T10.C_LU_SRCE || T10.C_LU_DEST <> '00VAMR'
and (T1.I_PART not in
(select T12.I_PART
from E126S.dbo.EPQBU YR_TBL T12
where T12.C_LU_SRCE_P CHSNG = T3.C_LU_SRCE_PC HSNG
)
or
T1.I_PART in
(select T12.I_PART
from E126S.dbo.EPQBU YR_TBL T12
where T12.C_LU_SRCE_P CHSNG = T3.C_LU_SRCE_PC HSNG
and T12.I_DECK_1_2 in ('AB','BB','EB' ,'MB','UB','VB' )
)
)
and T1.I_PART in
(select T11.I_PART
from E126S.dbo.EPQPG PP_TBL T11
where T11.I_MOD_YR = T1.I_MOD_YR
and T11.C_FAM = T1.C_FAM
)
and T10.L_ACTV = 'Y'

[kumar_rangan1976]

It works. Thanks for your help.