BookmarkSubscribeRSS Feed
BSL
Calcite | Level 5 BSL
Calcite | Level 5

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

1 REPLY 1
PGStats
Opal | Level 21

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 787 views
  • 0 likes
  • 2 in conversation