3 Replies Latest reply: Jun 13, 2014 4:56 AM by Robert Angel RSS

    SubQuery in From Clause

    SYedurur

      How can i model the RPD with the below query which has the subquery in the from Clause.

       

      SELECT

        o948938.CONSOLIDATED_NAME,

        ( SUM(o948992.YTD_COMPLETED) )/( SUM(TOTAL_OCC_AP) ) AS C_1,

        SUM(TOTAL_OCC_AP)                                    AS TOTAL_OCC_AP,

      FROM

      ORG_DIM o948938,

      TIME_MONTHLY_DIM o948963,

      INSPECTION_FACT o948992,

        (SELECT TDS_NUM ,

          MONTH_ID ,

          SUM(TOTAL_APTS) TOTAL_AP,

        FROM SUMMARY_FACT

        GROUP BY TDS_NUM,

          MONTH_ID

        ) O949126

      WHERE (o949126.MONTH_ID = o948992.MONTH_ID(+)

      AND o949126.TDS_NUM     = o948992.TDS_NUM(+)

      AND (o948938.TDS_NUM    = o949126.TDS_NUM)

      AND (O948963.MONTH_ID   = O949126.MONTH_ID))

      group by

        O948938.NEW_BOROUGH_GROUPING

        • 1. Re: SubQuery in From Clause
          Robert Angel

          Hi,

           

           

          you can do this via an opaque view.

           

          you can also do this by modelling the summed value as a LOGICAL calculation in the group by to aggregate 'pinned' to a specific dimension hierarchy which reflects the grouping in the inline view.

           

           

          Hope this helps,

           

          Robert.

          • 2. Re: SubQuery in From Clause
            SYedurur

            Hi Robert,

             

            Thanks for the response,

             

            I am trying to achieve this without Opaque View.Can you please elobrate the second procedure or pass on the link how to achieve this.

             

            you can also do this by modelling the summed value as a LOGICAL calculation in the group by to aggregate 'pinned' to a specific dimension hierarchy which reflects the grouping in the inline view.

            • 3. Re: SubQuery in From Clause
              Robert Angel

              Hi,

               

              what I describe would require you to have a key at the appropriate level of granularity.

               

              To take the classic Time hierarchy imagine levels created as below as below; -

               

              Total¬

                       Year

                              ¬

                                Quarter

                                          ¬

                                            Month

                                                     ¬

                                                       Detail

               

               

              Each level (apart from Total) having appropriate granularity key, i.e. Year Key (2014), Quarter Key (201402), Month Key (201406), Detail Key  (20140613)

               

               

              Now imagine you want to have your Fact 'Amount' measure expressed 'fixed' to correspond to these levels.

               

              1. Create a copy of the 'Amount' rename to Total Year Amount.

               

              Drag the measure to Year hierarchy level as above

               

              To get then the amount expressed as a percentage of the total year you then create a new LOGICAL formula based measure which is Amount / 'Total Year Amount'

               

               

              Make sense?

               

               

              regards,

               

              Robert.