BI Publisher (MOSC)

MOSC Banner

Group by function when query includes an embedded Select statement

edited Jan 23, 2015 10:05AM in BI Publisher (MOSC) 2 commentsAnswered


This is more of a SQL question than specific to BI Publisher.  I am trying to summarize cost by month to use in a pivot table.  I'm using the following query:

select  "PROJECT"."NAME" as "PROJNAME",

           "ACTIVITYEXPENSE"."EXPENSEITEM" as "EXPENSE",

           (select to_char("UDFVALUE"."UDFDATE", 'YYYY-MM') as "MONTH"

               from  "PXRPTUSER"."UDFVALUE" "UDFVALUE"

               where "ACTIVITYEXPENSE"."OBJECTID" = "UDFVALUE"."FOREIGNOBJECTID" ) as "EXPYYYYMM",

           sum ("ACTIVITYEXPENSE"."PLANNEDCOST") as "PLANNED"

from  "PXRPTUSER"."ACTIVITYEXPENSE" "ACTIVITYEXPENSE",

          "PXRPTUSER"."PROJECT" "PROJECT"

where   "PROJECT"."OBJECTID"="ACTIVITYEXPENSE"."PROJECTOBJECTID"

group by "PROJECT"."NAME", "ACTIVITYEXPENSE"."EXPENSEITEM", "?????"

The ????? at the end is where my question begins.  Can I group by the "EXPYYYYMM" field created by the select to_char function?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center