Hello all,
I am relatively new to FlexPLM and I apologize if this is not the right place for this question. I need to write a query that will bring back Colorway information for each product in each season. I have created a view with the following JOINs for the Season to Product tables:
CREATE VIEW XXXXXX AS
SELECT A.seasonLinkType AS [SeasonLinkType],
B.att17 AS [SAPStyleNumber]
,B.att1 AS [ProductName]
,D.att5 AS [Season]
,D.branchIditerationInfo AS [SeasonBranchId]
,B.branchIditerationInfo AS [ProductBranchId]
,B.att7 AS [ConceptNumber]
,A.att5 AS [SellingCollection]
,A.att4 AS [Style]
,A.date30 AS [RetailIntroDate]
,A.num34 AS [LifecycleWeeks]
FROM
F91M10ProdECV.F91M10Prod.LCSSeasonProductLink A
INNER JOIN LatestIterLCSProduct B on A.productARevId=B.branchIditerationInfo
INNER JOIN WTPartMaster C on B.idA3masterReference=C.idA2A2
INNER JOIN LatestIterLCSSeason D on A.seasonRevId=D.branchIditerationInfo
INNER JOIN WTPartMaster E on D.idA3masterReference=E.idA2A2
WHERE(A.seasonRemoved = 0 OR A.seasonRemoved IS NULL)
AND (A.effectOutDate = '' OR A.effectOutDate IS NULL)
This view is then JOINED to the SKU and Color tables as below:
SELECT
C.VBProdSeasonRefNo
,C.Season
,C.ProductName
,B.colorName AS [ColorName]
,B.num7 AS [VBColorCode]
,A.idA2A2 AS [SkuId]
,A.idA3masterReference AS [SkuMasterId]
,A.branchIditerationInfo AS [SkuBranchId]
,B.idA2A2 AS [ColorId]
,B.colorHexidecimalValue AS [ColorHexidecimalValue]
,C.ProductId
,C.ProductMasterId
,C.ProductBranchId
,C.SeasonId
,C.SeasonMasterId
,C.SeasonBranchId
FROM
LatestIterLCSSKU A
INNER JOIN LCSColor B ON A.num1=B.idA2A2
INNER JOIN XXXXXX C ON A.productARevId=C.ProductBranchId
AND A.seasonRevId=C.SeasonBranchId
There is an issue with this part as I am getting duplicate colorway information back. Does anyone know what other tables need to be included in the JOIN to prevent the duplicates?
Thank you,
Bryan
