QMF - 'Group By' with substringed column

[tcurrier]

Is there a way to do a 'Group by' on a substringed column ?

SELECT SUBSTR(MY_FIELD,1,4),
COUNT(*)
FROM MYDATABASE.MYTABLE
WHERE STATE = '20'
AND STATUS = '1'
GROUP BY (MY_FIELD,1,4) <--- ?????

Thanks for any help !

 

[MarcLodge]

I think that there is, but I'm not near a DB2 system so can't experiment. Have you tried just using the MY_FIELD name in the GROUP BY, leaving off the 1,4 bit?

Marc

 

[tcurrier]

I found the answer on another board if anyone is interested:

SELECT Z.MY_ALIAS_NAME
,COUNT(*)
FROM (SELECT SUBSTR(MYFIELD,1,4) MY_ALIAS_NAME
FROM MYDATABASE_MYTABLE
WHERE STATE = '20'
AND GENERAL_STATUS = '1'
AND REASON_GENERAL = 'A') Z
GROUP BY Z.MY_ALIAS_NAME

 

[blom0344]

A 'group by' on a substringed column is allowed, but you need to repeat the expression LITERALLY in the group by part. Anyway, this is indicated for DB2 UDB.
For mainframe / AS400 it may be another story....

Ties Blom

 

[MarcLodge]

Having seen tcurrier's answer, this rings a bell that that is the way in which I've had to do it on a mainframe before.

Thanks for letting us know you have a solution.