Scalar functions in GROUP BY

pts.
Tags:
DB2
OS/390
UDB for mainframe
z/OS
Is there a reason why I cannot use a SCALAR function in a Group BY clause? I have a table with a column that is of type timestamp. I want to get a count of all dates for which there are multiple rows. So I devise a query of the sort SELECT COUNT(*), DAYS(TMSTP_COL) FROM MYTAB A GROUP BY DAYS(TMSTP_COL) HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC I get an error DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". I know it is complaining about the DAYS function since the moment I remove it, the error goes away Thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

this is db2, right?

no idea why it isn’t working, it looks okay to me, but i don’t have db2 myself, so i can’t test this

select thecount
, days(TMSTP_COL) as days_timestmp
from (SELECT COUNT(*) as thecount
, TMSTP_COL
FROM MYTAB A
GROUP BY TMSTP_COL
HAVING COUNT(*) > 1) as dt
order by thecount desc

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • ARWinner
    The ORDER BY clause is for sorting--after the result set is selected. That is why the function is not allowed, it is sorta meaningless to count again. Try this: SELECT COUNT(*) as quantity, DAYS(TMSTP_COL) FROM MYTAB A GROUP BY DAYS(TMSTP_COL) HAVING COUNT(*) > 1 ORDER BY quantity DESC or this: SELECT COUNT(*), DAYS(TMSTP_COL) FROM MYTAB A GROUP BY DAYS(TMSTP_COL) HAVING COUNT(*) > 1 ORDER BY 1 DESC HTH Andy
    0 pointsBadges:
    report
  • BigKat
    give the colums names and group by the column not the function. select .... AS NAME .... group by NAME....
    7,845 pointsBadges:
    report
  • Stanton
    Here is what you have to do, I've tested it to make sure it works. select count(*) THECOUNT, X.M_DAYS FROM (SELECT DAYS(TMSTP_COL) M_DAYS FROM MYTAB) X GROUP BY X.M_DAYS HAVING COUNT(*) > 1 ORDER BY THECOUNT DESC Stanton
    0 pointsBadges:
    report
  • TomLiotta
    I've always given my derived columns names because I've never worked out the rules. The SQL Reference (V5R3) has this to say: If GROUP BY or HAVING is used:
    • Each column-name in the select list must identify a grouping expression or be specified within a column function:
      • – If the grouping expression is a column name, the select list may apply additional operators to the column name. For example, if the grouping expression is a column C1, the select list may contain C1+1.
      • – If the grouping expression is not a column name, the select list may not apply additional operators to the expression. For example, if the grouping expression is C1+1, the select list may contain C1+1, but not (C1+1)/8.
    That last bit -- "If the grouping expression is not a column name" -- sure makes it sound like a column name is not required. And the example -- "the grouping expression is C1+1" -- sure seems to show that a column name isn't required. But nothing is said about a SCALAR function either way. I've never figured out the exact rules, so I just do like others have suggested -- [DAYS(TMSTP_COL) as NewCol] -- in the SELECT list and reference [NewCol] elsewhere. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    And as a test, this works in V5R3:
    SELECT
       count(*),days(SYSLCHG)
     FROM mytesttbl
       GROUP BY days(SYSLCHG)
       HAVING count(*)<>0
    
    As does this:
    SELECT
       count(*),days(SYSLCHG)
     FROM mytesttbl
       GROUP BY days(SYSLCHG)
       HAVING count(*)<>0
       ORDER BY  1  desc
    
    The first discussion point by ARWinner is on the mark. Of course, that assumes that TMSTP_COL is a timestamp. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following