× 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: [EXT] Re: SQL Cast Question

Art,

Given your statement:
'cast(count(fdflat#) as decimal(7,0)) '

You can simplify as:
Dec(count(distinct fdflat#),7,0) as my_name

Ok, I threw in the distinct clause, which you did not specify, but it matches MY normal intent when I’m counting a named field... anyway, the “as my_name” clause defines the result set field name. As Vern said, those names are not in scope for the query as it executed. They only apply to the final result set.

Sometimes, to work around this and to make intent more clear, I’ll compose my queries as:

With sample_qry (fields, as_I, want, them, named)
as (select f1, f2, f3, f4, f5 from file_a)

Select *
From sample_qry
Where as_I = 123 and them = ‘abc’

The “with” clause just renames the columns so that the rest of the query can be composed with better field names.

-Eric DeLong


Sent from my iPhone

On Feb 24, 2019, at 10:38 PM, Art Tostaine, Jr. <atostaine@xxxxxxxxx> wrote:

CAUTION: EXTERNAL EMAIL

**********************************************************************
Thanks. I’ll post the whole statement tomorrow

On Sun, Feb 24, 2019 at 11:15 PM Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

Art, you need to look up the meaning of 42703 - there might be a message
in the job log that says more, but it means there is a name that is not
known in some context. I suspct it is myCount.

I started "casting" a response (heh) but confused myself. We just don't
know enough of what you are doing.

BTW, I do not believe that you need the CAST for anything.

Also, you can't use the "AS" name in other parts of the SELECT,
especially the GROUP BY or ORDER BY - at least, not usually - although
the LATERAL keyword might help out here, I've not yet used the latter.

So if you can present more of the framework of the statement, we can
help better.

Regards
Vern

On 2/24/2019 8:28 PM, Art Tostaine, Jr. wrote:
It's SQLSTT 42703


On Sun, Feb 24, 2019 at 9:23 PM Art Tostaine, Jr. <atostaine@xxxxxxxxx>
wrote:

Thank you. I recall seeing how to name fields with CAST but my googling
was turning up nothing.

I'm still seeing a syntax error. does myCount go in double quotes?

On Sun, Feb 24, 2019 at 9:14 PM Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=bYD378JhhOBj8GMNpx8_lU53M1HovGQ2lx_ILJd0FMU&s=asTRqcmQxeQDlwwulJKr3ZH4YV-OkJORtjBxlIBofnU&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=bYD378JhhOBj8GMNpx8_lU53M1HovGQ2lx_ILJd0FMU&s=6IBRVb_jMedQdsfsTr3WI3Dz97yVnSEGqSJnFmCGlEM&e=.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=bYD378JhhOBj8GMNpx8_lU53M1HovGQ2lx_ILJd0FMU&s=Q-x_2Eyrk69bfp8RuKt7vhBzeqhXX7bIIct-AB_EcAM&e=


--
Art Tostaine



--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=bYD378JhhOBj8GMNpx8_lU53M1HovGQ2lx_ILJd0FMU&s=asTRqcmQxeQDlwwulJKr3ZH4YV-OkJORtjBxlIBofnU&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=bYD378JhhOBj8GMNpx8_lU53M1HovGQ2lx_ILJd0FMU&s=6IBRVb_jMedQdsfsTr3WI3Dz97yVnSEGqSJnFmCGlEM&e=.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=bYD378JhhOBj8GMNpx8_lU53M1HovGQ2lx_ILJd0FMU&s=Q-x_2Eyrk69bfp8RuKt7vhBzeqhXX7bIIct-AB_EcAM&e=

--
Art Tostaine
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=bYD378JhhOBj8GMNpx8_lU53M1HovGQ2lx_ILJd0FMU&s=asTRqcmQxeQDlwwulJKr3ZH4YV-OkJORtjBxlIBofnU&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=bYD378JhhOBj8GMNpx8_lU53M1HovGQ2lx_ILJd0FMU&s=6IBRVb_jMedQdsfsTr3WI3Dz97yVnSEGqSJnFmCGlEM&e=.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=bYD378JhhOBj8GMNpx8_lU53M1HovGQ2lx_ILJd0FMU&s=Q-x_2Eyrk69bfp8RuKt7vhBzeqhXX7bIIct-AB_EcAM&e=

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.