Migrate Sybase outer join query to UDB DB2 8.2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kumar_rangan1976@yahoo.co.in - 26 Dec 2005 00:42 GMT

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_PCHSNG',
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.CustomerManager as N_MGR_CUST,
a.SupplierManager as N_MGR_SUPLR,
a.IndustryDescription as X_INDUS_DESC,
a.Description as X_PROJ_DESC,
a.Title as X_TI,
a.DesignResponsibility as X_RESPLTY_DSGN,
a.MRD as D_MATL_REDQ,
a.CreationDate as D_CREATN,
a.RiskDescription as C_RISK,
a.ProjectType as C_PROJ_TYP,
a.CustomerStatus as C_PROJ_STAT,
a.CustomerOpinion as C_PROJ_CUST_OP,
a.SupplierStatus as C_SUPLR_STAT,
a.SupplierOpinion as C_PROJ_SUPLR_OP,
a.RiskToPendingDate as D_PEND_RISKTO,
a.MethodologyName as N_METHD,
a.MethodologyRevisionNumber 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.expirationdate as D_EFF_OUT,
"X_PART_DESC" = rtrim(c.Name) + ' - ' +
rtrim(c.Description),
c.weight as Q_PART_WGT,
c.weightUOM as C_WGT_UM,
c.ChangeReason as C_PART_CHNG_REAS,
c.ModelYearOrdered as I_MOD_YR_1,
c.ModelYearRequired 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_THRD,
"L_DIML_LAB_REQD" = ' '
from E12WS..Project a,
E12WS..ProjectPart2 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..ProjectSupplier 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

Reply to this Message

Tonkuma - 26 Dec 2005 14:46 GMT

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?

Reply to this Message

Tonkuma - 26 Dec 2005 16:02 GMT

Put aside my previous questions.

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

Sybase: "X_PART_DESC" = rtrim(c.Name) + ' - ' +
rtrim(c.Description),
DB2: rtrim(c.Name) || ' - ' || rtrim(c.Description) AS "X_PART_DESC",

Sybase: "L_DIML_LAB_REQD" = ' '
DB2: ' ' AS "L_DIML_LAB_REQD"

My idea for FROM clause is here:

FROM E12WS.Project a
INNER JOIN
E12WS.ProjectPart2 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.ProjectSupplier o
ON a.ProjectNumber = o.ProjectNumber
INNER JOIN
E125S.EPQPTSP_TBL n
ON c.partnumber = n.I_PART
AND o.SupplierCode = n.I_SUPLR_LOC
INNER JOIN
E127S.EPQSUPL_TBL 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

Reply to this Message

kumar_rangan1976@yahoo.co.in - 26 Dec 2005 22:16 GMT

Thanks a lot... Could you please provide DB2 code the below query.
select .....
from
E126S.dbo.EPQSTRU_TBL T1,
E126S.dbo.EPQPATT_TBL T2,
E126S.dbo.EPQPOSP_TBL T3,
E126S.dbo.EPQSUPL_TBL T4 ,
E126S.dbo.EPQPSCF_TBL T5 ,
E126S.dbo.EPQPTCY_TBL T8,
E126S.dbo.EPQCOMP_TBL T7,
E126S.dbo.EPQTOOL_TBL Tpo ,
E126S.dbo.EPQREPT_TBL T10,
E126S.dbo.EPQTLDT_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_PCHSNG *= T7.C_LU_SRCE_PCHSNG 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_PCHSNG = T8.C_LU_SRCE_PCHSNG and
T3.C_LU_SRCE_PCHSNG = T10.C_LU_SRCE_PCHSNG 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.EPQPOSP_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.EPQBUYR_TBL T12
where
T12.C_LU_SRCE_PCHSNG = T3.C_LU_SRCE_PCHSNG ) or
T1.I_PART in (select T12.I_PART from E126S.dbo.EPQBUYR_TBL T12
where T12.C_LU_SRCE_PCHSNG = T3.C_LU_SRCE_PCHSNG 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.EPQPGPP_TBL T11
where T11.I_MOD_YR = T1.I_MOD_YR and T11.C_FAM = T1.C_FAM) and
T10.L_ACTV = 'Y'

Reply to this Message

Tonkuma - 27 Dec 2005 03:25 GMT

select .....
from
E126S.dbo.EPQSTRU_TBL T1
INNER JOIN
E126S.dbo.EPQPATT_TBL T2
ON T1.I_PART = T2.I_PART
INNER JOIN
E126S.dbo.EPQPOSP_TBL T3
ON T1.I_PART = T3.I_PART
INNER JOIN
E126S.dbo.EPQSUPL_TBL T4
ON T3.I_SUPLR_MFG = T4.I_SUPLR_LOC
INNER JOIN
E126S.dbo.EPQPTCY_TBL T8
ON T1.I_PART = T8.I_PART
AND T3.C_LU_SRCE_PCHSNG = T8.C_LU_SRCE_PCHSNG
INNER JOIN
E126S.dbo.EPQREPT_TBL T10
ON T1.I_PART = T10.I_PART
AND T3.I_PART = T10.I_PART
AND T3.C_LU_SRCE_PCHSNG = T10.C_LU_SRCE_PCHSNG
LEFT OUTER JOIN
E126S.dbo.EPQPSCF_TBL T5
ON T1.I_PART = T5.I_PART
AND T1.I_MOD_YR = T5.I_MOD_YR
LEFT OUTER JOIN
E126S.dbo.EPQCOMP_TBL T7
ON T1.I_PART = T7.I_PART
AND T3.C_LU_SRCE_PCHSNG = T7.C_LU_SRCE_PCHSNG
LEFT OUTER JOIN
E126S.dbo.EPQTOOL_TBL Tpo
ON substr(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
LEFT OUTER JOIN
E126S.dbo.EPQTLDT_TBL Tt
ON T1.I_PART = Tt.I_PART
where
T3.I_MOD_YR
= (select min(T6.I_MOD_YR)
from E126S.dbo.EPQPOSP_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.EPQBUYR_TBL T12
where T12.C_LU_SRCE_PCHSNG = T3.C_LU_SRCE_PCHSNG
)
or
T1.I_PART in
(select T12.I_PART
from E126S.dbo.EPQBUYR_TBL T12
where T12.C_LU_SRCE_PCHSNG = T3.C_LU_SRCE_PCHSNG
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.EPQPGPP_TBL T11
where T11.I_MOD_YR = T1.I_MOD_YR
and T11.C_FAM = T1.C_FAM
)
and T10.L_ACTV = 'Y'

Reply to this Message

kumar_rangan1976@yahoo.co.in - 28 Dec 2005 00:12 GMT

It works. Thanks for your help.

Reply to this Message