× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. February 2019

RE: SQL Cast Question

That is SQL Standard.
Generated field names cannot be used within the same sub-select.
A sub-select can consist of:

SELECT
FROM
WHERE
GROUP BY
HAVING

Order by is NOT Part of a sub-select, because multiple sub-selects can be merged together with an UNION, EXCEPT or INTERSECT clause. (Named FULL-Select)
The result of the merged sub-selects can be sorted over all information returned by all sub-selects.

Since the ORDER BY is outside the sub-select, the generated name can be used within the ORDER BY but NOT within the GROUP BY.

BTW a Common Table Expression or a nested sub-select are not part of the same sub-select either, so names generated within a CTE or a nested Sub-Select can be used within other CTEs or other (nested) Sub-selects or the final select.

You may open an RFE, but I assume it will be denied or not realized, because it doesnot go conform with the SQL Standard.

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 Rob Berendt
Sent: Montag, 25. Februar 2019 14:21
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL Cast Question

I did look closely at Birgitta's reply. I still do not see why you would do a group by on count 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;

Let's look at it this way
Select ItemNumber, ItemDescription, count(*) From itemWarehouse Group by count(*); This wouldn't work because first, you want a count at each control break by itemnumber. So you have to have that count first.
The HAVING makes perfect sense.
If you really want this in a descending order by count you would have to use a common table expression like this:
With T1 as (
Select ItemNumber, ItemDescription, count(*) as rowCount From itemWarehouse Group by ItemNumber, ItemDescription) Select * from T1 order by rowCount desc;

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jay Vaughn
Sent: Monday, February 25, 2019 8:12 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Cast Question

Rob. See hausers response to not allowed in group by.

Jay

On Feb 25, 2019, at 7:12 AM, Rob Berendt <rob@xxxxxxxxx> wrote:

Jay,

Let's make it simple.
Select count(*) as TheCount
From mytable
Group by TheCount;

Does that make sense?

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Jay Vaughn
Sent: Monday, February 25, 2019 6:57 AM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Cast Question

Yuk Birgitta, why?

Why wasn’t it made allowable in the group by? Do you know?

Jay

On Feb 25, 2019, at 1:05 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

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

--
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
--
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
--
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
--
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
--
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


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.