Puzzled in query

BSL — Sat, 15 Nov 2014 14:00:14 GMT

Hi Experts,

I am stuck in the following query as it has so many inner joins in from clause could anybady explain it to me so that I would be able to move further....

SELECT SAS_V_PrdMst.PrdGrpCode, SAS_V_PrdMst.PrdSubGrpCode, MONTH(SAS_V_FgTData.Iddate) AS M, YEAR(SAS_V_FgTData.Iddate) AS Y,

SUM(SAS_V_FgTData.IdPrdQty) AS SaleQ, SUM(SAS_V_FgTData.IdPrdQty * 0) AS TargetQ, SAS_V_SeMst.SEName

FROM SAS_V_GrpMst INNER JOIN

SAS_V_FgTData INNER JOIN

SAS_V_DealerMst ON SAS_V_FgTData.IdDealerCode = SAS_V_DealerMst.dealerCode INNER JOIN

SAS_V_PrdMst ON SAS_V_FgTData.IdPrdCode = SAS_V_PrdMst.PrdCode ON SAS_V_GrpMst.GrpCode = SAS_V_PrdMst.PrdGrpCode AND

SAS_V_GrpMst.GrpSubCode = SAS_V_PrdMst.PrdSubGrpCode INNER JOIN

SAS_V_SeMst ON SAS_V_DealerMst.DealerSECode = SAS_V_SeMst.SECode

WHERE (MONTH(SAS_V_FgTData.Iddate) = 4) AND (YEAR(SAS_V_FgTData.Iddate) = 2014) AND (SAS_V_FgTData.IdType IN ('S', 'US'))

GROUP BY SAS_V_PrdMst.PrdGrpCode, SAS_V_PrdMst.PrdSubGrpCode, MONTH(SAS_V_FgTData.Iddate), YEAR(SAS_V_FgTData.Iddate), SAS_V_SeMst.SEName

Regards

Rahul

Re: Puzzled in query

PGStats — Sat, 15 Nov 2014 18:56:52 GMT

I don't know what it does (I would have to guess what the fields actually represent) but reordering, using aliasses, and indenting properly might help:

SELECT

Prd.PrdGrpCode,

Prd.PrdSubGrpCode,

MONTH(FgT.Iddate) AS M,

YEAR(FgT.Iddate) AS Y,

Se.SEName,

SUM(FgT.IdPrdQty) AS SaleQ,

SUM(FgT.IdPrdQty * 0) AS TargetQ

FROM

SAS_V_GrpMst as Grp INNER JOIN

SAS_V_PrdMst as Prd

ON Grp.GrpCode = Prd.PrdGrpCode AND

Grp.GrpSubCode = Prd.PrdSubGrpCode INNER JOIN

SAS_V_FgTData as FgT

ON FgT.IdPrdCode = Prd.PrdCode INNER JOIN

SAS_V_DealerMst as Dealer

ON FgT.IdDealerCode = Dealer.dealerCode INNER JOIN

SAS_V_SeMst as Se

ON Dealer.DealerSECode = Se.SECode

WHERE

MONTH(FgT.Iddate) = 4 AND

YEAR(FgT.Iddate) = 2014 AND

FgT.IdType IN ('S', 'US')

GROUP BY

Prd.PrdGrpCode,

Prd.PrdSubGrpCode,

MONTH(FgT.Iddate),

YEAR(FgT.Iddate),

Se.SEName;

topic Puzzled in query in SAS Programming

Puzzled in query

Re: Puzzled in query