problem with GROUP BY using CASE with Subqueries 1

[RustyAfro]

On the tail of my last question here, now I have one final problem I'm scratching my head about. I built a Case statement with two EXISTS subqueries. However, Oracle doesn't allow this CASE statement to be in the Group By because it has subqueries.

I actually can get this to work just fine if I wrap the case in a "MAX" statement, which allows me to not put it in the GROUP BY, but that presents other problems of it's own in my actual report using the data.

Is there anyway that I can use this CASE statement without wrapping it in an aggregate function?

SELECT
  PLH300_PH_DIMSN.PH_NBR,
  
SUM(( MBR304_TM_PH_SUMM.ELG_CN )),
CASE
WHEN  
( 
  SELECT
     1
  FROM
    dual
  WHERE 
 (EXISTS
   (
    SELECT *  
    FROM PLH300_PH_DIMSN p2,
    MBR304_TM_PH_SUMM m2

    WHERE
      ( 
             ( p2.PH_NBR  =  PLH300_PH_DIMSN.PH_NBR )
        AND  ( p2.PH_DIMSN_KEY = m2.PH_DIMSN_KEY)
        AND  ( m2.DLY_TM_DIMSN_KEY= TME300_DLY_TM_DIMSN_Incurred.DLY_TM_DIMSN_KEY)
        AND  ( p2.PH_CAT_RMC_CD  =  '9 ')
        AND  ( p2.PH_CAT_MED_TYP_CD LIKE 'S%' )
        AND  ( p2.PH_CAT_MED_TYP_CD NOT IN ('SE ','SH ','S3A','S7A','SRA','STA','SXA','SYA') )
      )
   )
 )
AND 
 (EXISTS
   (
    SELECT *  
    FROM PLH300_PH_DIMSN p1,
    MBR304_TM_PH_SUMM m1

    WHERE
      ( 
             ( p1.PH_NBR  =  PLH300_PH_DIMSN.PH_NBR )
        AND  ( p1.PH_DIMSN_KEY = m1.PH_DIMSN_KEY)
        AND  ( m1.DLY_TM_DIMSN_KEY= TME300_DLY_TM_DIMSN_Incurred.DLY_TM_DIMSN_KEY)
        AND  ( p1.PH_CAT_RMC_CD  =  '9 ')
        AND  ( p1.PH_CAT_MED_TYP_CD NOT LIKE 'S%' 
                 OR
               p1.PH_CAT_MED_TYP_CD IN ('SE ','SH ','S3A','S7A','SRA','STA','SXA','SYA') 
                 )
      )
   )
 )
) = 1 Then 'Dual Network'
Else 'None'
END As MktStrat
FROM
  TME300_DLY_TM_DIMSN,
  TME300_DLY_TM_DIMSN  TME300_DLY_TM_DIMSN_Incurred,
  PLH300_PH_DIMSN,
  MBR304_TM_PH_SUMM
WHERE
  ( MBR304_TM_PH_SUMM.PH_DIMSN_KEY=PLH300_PH_DIMSN.PH_DIMSN_KEY  )
  AND  ( MBR304_TM_PH_SUMM.DLY_TM_DIMSN_KEY=TME300_DLY_TM_DIMSN_Incurred.DLY_TM_DIMSN_KEY  )
  AND  ( TME300_DLY_TM_DIMSN_Incurred.DLY_TM_DIMSN_KEY=TME300_DLY_TM_DIMSN.DLY_TM_DIMSN_KEY  )
  AND  (
  PLH300_PH_DIMSN.PH_NBR  =  '00003022'
  AND  ( TME300_DLY_TM_DIMSN.CLNDR_MTHLY_DT )  =  '2007/02'
  )
GROUP BY
  PLH300_PH_DIMSN.PH_NBR

Thanks again for any help!! ;-)

 

[SantaMufasa]

Yes, Rusty, you can do what you want by using the technique that I posted in your other thread: placing your query with the CASE statement in a "virtual VIEW" in the "FROM" clause:

[b]SELECT a PH_NBR, sum(b) Total, MktStrat -- See "GROUP BY", below
FROM ([/b]SELECT PLH300_PH_DIMSN.PH_NBR a,
             MBR304_TM_PH_SUMM.ELG_CN b,
             CASE WHEN  
( 
  SELECT
     1
  FROM
    dual
  WHERE 
 (EXISTS
   (
    SELECT *  
    FROM PLH300_PH_DIMSN p2,
    MBR304_TM_PH_SUMM m2

    WHERE
      ( 
             ( p2.PH_NBR  =  PLH300_PH_DIMSN.PH_NBR )
        AND  ( p2.PH_DIMSN_KEY = m2.PH_DIMSN_KEY)
        AND  ( m2.DLY_TM_DIMSN_KEY= TME300_DLY_TM_DIMSN_Incurred.DLY_TM_DIMSN_KEY)
        AND  ( p2.PH_CAT_RMC_CD  =  '9 ')
        AND  ( p2.PH_CAT_MED_TYP_CD LIKE 'S%' )
        AND  ( p2.PH_CAT_MED_TYP_CD NOT IN ('SE ','SH ','S3A','S7A','SRA','STA','SXA','SYA') )
      )
   )
 )
AND 
 (EXISTS
   (
    SELECT *  
    FROM PLH300_PH_DIMSN p1,
    MBR304_TM_PH_SUMM m1

    WHERE
      ( 
             ( p1.PH_NBR  =  PLH300_PH_DIMSN.PH_NBR )
        AND  ( p1.PH_DIMSN_KEY = m1.PH_DIMSN_KEY)
        AND  ( m1.DLY_TM_DIMSN_KEY= TME300_DLY_TM_DIMSN_Incurred.DLY_TM_DIMSN_KEY)
        AND  ( p1.PH_CAT_RMC_CD  =  '9 ')
        AND  ( p1.PH_CAT_MED_TYP_CD NOT LIKE 'S%' 
                 OR
               p1.PH_CAT_MED_TYP_CD IN ('SE ','SH ','S3A','S7A','SRA','STA','SXA','SYA') 
                 )
      )
   )
 )
) = 1 Then 'Dual Network'
Else 'None'
END As MktStrat
FROM
  TME300_DLY_TM_DIMSN,
  TME300_DLY_TM_DIMSN  TME300_DLY_TM_DIMSN_Incurred,
  PLH300_PH_DIMSN,
  MBR304_TM_PH_SUMM
WHERE
  ( MBR304_TM_PH_SUMM.PH_DIMSN_KEY=PLH300_PH_DIMSN.PH_DIMSN_KEY  )
  AND  ( MBR304_TM_PH_SUMM.DLY_TM_DIMSN_KEY=TME300_DLY_TM_DIMSN_Incurred.DLY_TM_DIMSN_KEY  )
  AND  ( TME300_DLY_TM_DIMSN_Incurred.DLY_TM_DIMSN_KEY=TME300_DLY_TM_DIMSN.DLY_TM_DIMSN_KEY  )
  AND  (
  PLH300_PH_DIMSN.PH_NBR  =  '00003022'
  AND  ( TME300_DLY_TM_DIMSN.CLNDR_MTHLY_DT )  =  '2007/02'
  )
) -- End paren for Virtual VIEW
GROUP BY a, MktStrat

Let us know if this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RustyAfro]

Ah yes, an inline subquery would work, then just sum the numerical field in the outer query. Thanks again Mufasa!!

 

[RustyAfro]

Dave, sorry to bring this up again, but I want one more thing.

I have a situation just like above, but I can't use Inline views (or basically change anything in the FROM section).

Is there anyway to make Oracle allow a GROUP BY with a subquery? I'm assuming no, but I just want to make sure.

 

[SantaMufasa]

Rusty,

Yes, you can certainly do a GROUP BY in a subquery.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RustyAfro]

Sorry, I meant "With" a subquery, not "in" a subquery.

Basically this is the same question as the first post in this thread.

Is there anyway to get that Case statement to work without making it an line view?

 

[SantaMufasa]

Sorry that I didn't interpret properly.

You can do the CASE statement with making it an in-line VIEW...it just makes it a royal pain to have to restate the entire CASE statement.

I can't use Inline views (or basically change anything in the FROM section).

Is it that you cannot (i.e., do not have ability to) use in-line VIEWs or that you may not (i.e., do not have permission to) use in-line VIEWs?


There should be nothing preventing you (technically) from using in-line VIEWs. If some in your organization is taking issue with your using in-line VIEWs (simply a scripting technique), then I would be very interested in knowing their business (or technical) justification for that proscription against using such.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RustyAfro]

Well, that is complicated...but since your interested, I am not limited technically nor policy. We use Business Objects as our reporting tool. If your not familiar with it, there are ways to create objects (or fields) at the datawarehouse level (which requires heavy IT and long turnarounds) and objects at a higher "universe level" (which I can do).

Basically, I can write a SQL statement that gets saved at that universe level as an object and when its dragged into the report designers GUI interface, the interface inserts that objects SQL in the SELECT, GROUP BY (If non-aggregated), and HAVING (If Aggregated such as SUM) sections of the SQL dynamically.

Therefore, I have to write the SQL in such a way that the "object" will work without having to mess with the FROM section as Universe objects can't change that.

Normally, even the most complex SQL statements work, but because Subquery's cannot be in the GROUP BY, I run into problems. If I wrap that case statement above with "MAX" in key areas, then Business Objects places the same CASE statement in HAVING if I apply criteria on it or just leaves in SELECT only if no criteria. But the MAX is fairly inneficient and causes a couple other problems.

SO that's the long story - I just wish Oracle supported GROUP BYs on correlated sub-query's (where the data returned is non-numerical).