Using the given name will work for the ORDER BY clause, but NOT for the group by clause.
In the group by you need to repeat the expression used in the SELECT ... row.
... and aggregate functions cannot be used within the WHERE clause, but must be specified within the HAVING clause.
Example:
Select Fld1, Fld2, Cast(Count(Fldat#) as Dec(7, 0)) as Counter
From YourTable ....
Where Fldx = 'A' and FLDy = 12345
Group By Fld1, Fld2
Having Cast(Count(Flat#) as Dec(7, 0)) > 1000,0
Order By Counter Desc;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jay Vaughn
Sent: Montag, 25. Februar 2019 03:15
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Cast Question
cast(count(fdflat#) as decimal(7,0)) as “myCount”
Group by myCount
Order by myCount.
I think this works.
Jay
On Feb 24, 2019, at 9:01 PM, Art Tostaine, Jr. <atostaine@xxxxxxxxx> wrote:
cast(count(fdflat#) as decimal(7,0)) '
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
