Puzzled in query

Reply
Contributor BSL
Contributor
Posts: 25

Puzzled in query

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

Respected Advisor
Posts: 4,606

Re: Puzzled in query

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;

PG

PG
Ask a Question
Discussion stats
  • 1 reply
  • 192 views
  • 0 likes
  • 2 in conversation