ARWinner   0 pts.  |   Dec 31 2004  3:18AM GMT

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

BigKat   2540 pts.  |   Jan 2 2005  10:45AM GMT

give the colums names and group by the column not the function.

select …. AS NAME …. group by NAME….

Stanton   0 pts.  |   Jan 3 2005  10:48AM GMT

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

TomLiotta   8000 pts.  |   Oct 19 2009  8:58PM GMT

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

TomLiotta   8000 pts.  |   Oct 21 2009  1:33AM GMT

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