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

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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