SQL question

[Murty]

Hi,

Can anyone help me in making the following SQL work ?
Basically I am trying to use a computed filed (CASE statement) in group by
clause and struggling
with syntax (new to DB2)

select A, CASE B WHEN 1 THEN 'AAA' WHEN 2 THEN 'BBB' ELSE 'ZZZ' END AS
D, sum(C)
from TEST group by A, D

-Thanks

[Murty]

I forgot to mention..... I am having problems on m/f (OS/390) DB2 ver
7.x

The following syntax is working fine on Linux DB2 but not on m/f
DB2.......
select A,
CASE B WHEN 1 THEN 'AAA' WHEN 2 THEN 'BBB' ELSE 'ZZZ' END AS
D,
sum(C)
from TEST
group by A, CASE B WHEN 1 THEN 'AAA' WHEN 2 THEN 'BBB' ELSE 'ZZZ' END

[Knut Stolze]

Murty <ad***@comcast.net> wrote:

Hi,

Can anyone help me in making the following SQL work ?
Basically I am trying to use a computed filed (CASE statement) in group
by clause and struggling
with syntax (new to DB2)

select A, CASE B WHEN 1 THEN 'AAA' WHEN 2 THEN 'BBB' ELSE 'ZZZ' END
AS D, sum(C)
from TEST group by A, D

You must put the same expression in the GROUP BY clause and can't just refer
to the alias given to the column. Two options that I see right away:

SELECT a,
CASE b
WHEN 1 THEN 'AAA'
WHEN 2 THEN 'BBB'
ELSE 'ZZZ'
END AS d, SUM(c)
FROM test
GROUP BY a,
CASE b
WHEN 1 THEN 'AAA'
WHEN 2 THEN 'BBB'
ELSE 'ZZZ'
END AS d

SELECT a, d, SUM(c)
FROM ( SELECT a,
CASE b
WHEN 1 THEN 'AAA'
WHEN 2 THEN 'BBB'
ELSE 'ZZZ'
END AS d,
c
FROM test ) AS t(a, d, c)
GROUP BY a, d

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Murty]

The first one is not working. The second option is working but somehow
it doesn't
seem to be a cleaner solution. We have tons of very lengthy & complex
queries
(converting from Oracle) and this syntax will further coplicate them.

Is there any way of tweaking the first option and making it work ?

Thanks

You must put the same expression in the GROUP BY clause and can't just refer to the alias given to the column. Two options that I see right away:

SELECT a,
CASE b
WHEN 1 THEN 'AAA'
WHEN 2 THEN 'BBB'
ELSE 'ZZZ'
END AS d, SUM(c)
FROM test
GROUP BY a,
CASE b
WHEN 1 THEN 'AAA'
WHEN 2 THEN 'BBB'
ELSE 'ZZZ'
END AS d

SELECT a, d, SUM(c)
FROM ( SELECT a,
CASE b
WHEN 1 THEN 'AAA'
WHEN 2 THEN 'BBB'
ELSE 'ZZZ'
END AS d,
c
FROM test ) AS t(a, d, c)
GROUP BY a, d

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Murty]

The first one is not working. The second option is working but somehow
it doesn't
seem to be a cleaner solution. We have tons of very lengthy & complex
queries
(converting from Oracle) and this syntax will further coplicate them.

Is there any way of tweaking the first option and making it work ?

Thanks

You must put the same expression in the GROUP BY clause and can't just refer to the alias given to the column. Two options that I see right away:

SELECT a,
CASE b
WHEN 1 THEN 'AAA'
WHEN 2 THEN 'BBB'
ELSE 'ZZZ'
END AS d, SUM(c)
FROM test
GROUP BY a,
CASE b
WHEN 1 THEN 'AAA'
WHEN 2 THEN 'BBB'
ELSE 'ZZZ'
END AS d

SELECT a, d, SUM(c)
FROM ( SELECT a,
CASE b
WHEN 1 THEN 'AAA'
WHEN 2 THEN 'BBB'
ELSE 'ZZZ'
END AS d,
c
FROM test ) AS t(a, d, c)
GROUP BY a, d

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[AK]

have you tried wrapping option 2 in a view?

[Tokunaga T.]

This is the restriction od DB2 UDB for z/OS Version 7.
Please see IBM Redbook "DB2 UDB for z/OS Version 8 Technical Preview"
http://w3.itso.ibm.com/itsoapps/Redb...light=0,DB2,V8

5.10 Expressions in GROUP BY
DB2 V7 does not allow expressions to be specified in the GROUP BY
clause which is supported by DB2 on the UNIX, Windows, and AS/400
platforms.
The current work-around is for the applications to code nested table
expressions or a view to first provide a result table with the
expression as a column of the result, then specify the column in the
GROUP BY clause.
The GROUP BY clause specifies an intermediate result table that
consists of a grouping of the rows of R. R is the result of the
previous clause of the subselect.

The first one is not working. The second option is working but somehow
it doesn't
seem to be a cleaner solution. We have tons of very lengthy & complex
queries
(converting from Oracle) and this syntax will further coplicate them.

Is there any way of tweaking the first option and making it work ?

Thanks

You must put the same expression in the GROUP BY clause and can't just

refer

to the alias given to the column. Two options that I see right away:

SELECT a,
CASE b
WHEN 1 THEN 'AAA'
WHEN 2 THEN 'BBB'
ELSE 'ZZZ'
END AS d, SUM(c)
FROM test
GROUP BY a,
CASE b
WHEN 1 THEN 'AAA'
WHEN 2 THEN 'BBB'
ELSE 'ZZZ'
END AS d

SELECT a, d, SUM(c)
FROM ( SELECT a,
CASE b
WHEN 1 THEN 'AAA'
WHEN 2 THEN 'BBB'
ELSE 'ZZZ'
END AS d,
c
FROM test ) AS t(a, d, c)
GROUP BY a, d

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Rolf Loeben]

> This is the restriction od DB2 UDB for z/OS Version 7.

Please see IBM Redbook "DB2 UDB for z/OS Version 8 Technical Preview"

http://w3.itso.ibm.com/itsoapps/Redb...light=0,DB2,V8

The w3-address is quite useless outside IBM.

Regards Rolf

[Mark A]

> > This is the restriction od DB2 UDB for z/OS Version 7.

Please see IBM Redbook "DB2 UDB for z/OS Version 8 Technical Preview"

http://w3.itso.ibm.com/itsoapps/Redb...light=0,DB2,V8
The w3-address is quite useless outside IBM.

Regards Rolf

Link to Redbooks:
http://www.redbooks.ibm.com/

Most DB2 manuals and Redbooks are available on the IBM website.

[Tokunaga T.]

> > This is the restriction od DB2 UDB for z/OS Version 7.

Please see IBM Redbook "DB2 UDB for z/OS Version 8 Technical Preview"

The w3-address is quite useless outside IBM.

Sorry. Please try this URL:
http://publib-b.boulder.ibm.com/Redb...6871.html?Open