Re: Cross Tab Help

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 11 Dec 2009 15:15:12 -0800 (PST)
Message-ID: <269cfab1-47fb-490c-921a-efaaf9ec1440_at_d20g2000yqh.googlegroups.com>

On Dec 11, 1:52 pm, kou vang <gqko..._at_gmail.com> wrote:
> On Dec 11, 10:29 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > On Dec 11, 10:20 am, kou vang <gqko..._at_gmail.com> wrote:
>
> > > I can get this query to put the dataset together, but would love to
> > > get it into a cross tab.  I don't know how to work with multiple CASE
> > > statements to do this?  I want the columns to be the CASE results:
> > > Aquatic C, Aquatic R, Other and the Value to be ATTAINMENT_Desc.  I
> > > think I'm pretty close.  Thanks.
>
> > > SELECT
> > > ASSESS.LAKE_AUID.LAKE_ID,
> > > ADB.ASSESSMENT_UNITS.CYCLE,
> > > ADB.ATTAINMENT_LUT.ATTAINMENT_DESC,
> > > (CASE ADB.Use_Lut.USE_ID
> > >  WHEN 523 THEN 'Aquatic Consumption'
> > >  WHEN 552 THEN 'Aquatic Consumption'
> > >  WHEN 555 THEN 'Aquatic Consumption'
> > >  WHEN 565 THEN 'Aquatic Consumption'
> > >  WHEN 553 THEN 'Aquatic Recreation'
> > >  WHEN 556 THEN 'Aquatic Recreation'
> > >  WHEN 566 THEN 'Aquatic Recreation'
> > >  ELSE 'Other'
> > > END) AS ATT_DESC
>
> > > Kou
>
> > There are certainly other solutions, but I would be tempted to do
> > something like this using DECODE:
> > SELECT
> > ASSESS.LAKE_AUID.LAKE_ID,
> > ADB.ASSESSMENT_UNITS.CYCLE,
> > ADB.ATTAINMENT_LUT.ATTAINMENT_DESC,
> > DECODE(ADB.Use_Lut.USE_ID,
> >        523,ATTAINMENT_Desc,
> >        552,ATTAINMENT_Desc,
> >        555,ATTAINMENT_Desc,
> >        565,ATTAINMENT_Desc,
> >        NULL) AS CONSUMPTION,
> > DECODE(ADB.Use_Lut.USE_ID,
> >        553,ATTAINMENT_Desc,
> >        556,ATTAINMENT_Desc,
> >        555,ATTAINMENT_Desc,
> >        566,ATTAINMENT_Desc,
> >        NULL) AS RECREATION,
> > DECODE(ADB.Use_Lut.USE_ID,
> >        523,NULL,
> >        552,NULL,
> >        555,NULL,
> >        565,NULL,
> >        553,NULL,
> >        556,NULL,
> >        555,NULL,
> >        566,NULL,
> >        ATTAINMENT_Desc) AS OTHER
>
> > You can see how DECODE works by reviewing the following:http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>
> > Note that the DECODE syntax above may be re-written using CASE syntax.
>
> I almost got everything to work with the DECODE help, except how do I
> do a GROUP BY or DISTINCT?  When I try a GB it gives me an error,
> saying not a GB clause?
>
> GROUP BY ASSESS.LAKE_AUID.LAKE_ID, ADB.ASSESSMENT_UNITS.CYCLE
Without seeing the entire SQL statement, I would say that Oracle is also expecting you to group on the column ADB.Use_Lut.USE_ID and possibly ATTAINMENT_Desc.

To work around this, slide your SQL statement into an inline view and then perform the group outside the inline view. For example, if your SQL statement currently looks like this: SELECT
ASSESS.LAKE_AUID.LAKE_ID,

ADB.ASSESSMENT_UNITS.CYCLE,
ADB.ATTAINMENT_LUT.ATTAINMENT_DESC,
DECODE(ADB.Use_Lut.USE_ID,
       523,ATTAINMENT_Desc,
       552,ATTAINMENT_Desc,
       555,ATTAINMENT_Desc,
       565,ATTAINMENT_Desc,
       NULL) AS CONSUMPTION,
DECODE(ADB.Use_Lut.USE_ID,
       553,ATTAINMENT_Desc,
       556,ATTAINMENT_Desc,
       555,ATTAINMENT_Desc,
       566,ATTAINMENT_Desc,
       NULL) AS RECREATION,
DECODE(ADB.Use_Lut.USE_ID,
       523,NULL,
       552,NULL,
       555,NULL,
       565,NULL,
       553,NULL,
       556,NULL,
       555,NULL,
       566,NULL,
       ATTAINMENT_Desc) AS OTHER

FROM
  T1;

You would change it like this:
SELECT
  LAKE_ID,
  CYCLE,
  ATTAINMENT_DESC,

  SUM(CONSUMPTION) CONSUMPTION,
  SUM(RECREATION) RECREATION,
  SUM(OTHER) OTHER

FROM
(SELECT
ASSESS.LAKE_AUID.LAKE_ID,

ADB.ASSESSMENT_UNITS.CYCLE,
ADB.ATTAINMENT_LUT.ATTAINMENT_DESC,
DECODE(ADB.Use_Lut.USE_ID,
       523,ATTAINMENT_Desc,
       552,ATTAINMENT_Desc,
       555,ATTAINMENT_Desc,
       565,ATTAINMENT_Desc,
       NULL) AS CONSUMPTION,
DECODE(ADB.Use_Lut.USE_ID,
       553,ATTAINMENT_Desc,
       556,ATTAINMENT_Desc,
       555,ATTAINMENT_Desc,
       566,ATTAINMENT_Desc,
       NULL) AS RECREATION,
DECODE(ADB.Use_Lut.USE_ID,
       523,NULL,
       552,NULL,
       555,NULL,
       565,NULL,
       553,NULL,
       556,NULL,
       555,NULL,
       566,NULL,
       ATTAINMENT_Desc) AS OTHER

FROM
  T1)
GROUP BY
  LAKE_ID,
  CYCLE,
  ATTAINMENT_DESC; Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Dec 11 2009 - 17:15:12 CST