Re: Implicit cast on ORDER BY

[Peter Dow <petercdow@xxxxxxxxx>]

Hi Vern,

Re the original question, it does seem odd that SQL would choose to try 
and make both numeric.  Converting to numeric is more prone to errors 
than converting numeric to character.

Tomorrow is opera for me :)  Taking my wife to see The Marriage of 
Figaro; neither of us have ever been to a live opera. First time for 
everything!

Happy homecoming!
Peter


On 10/25/2024 3:21 PM, VERNON HAMBERG Owner via MIDRANGE-L wrote:
> Hi Peter
>
>
> I don't remember Justin's original statement as to the values for the host variable. I think I have the same question you have, with the same conclusion.
>
>
> Now back to Justin's original question - why he gets a conversion error - the reason is that in a CASE expression the result values have to be the same data type.
>
> Time for me to head home - college football homecoming tomorrow, will be on the field for halftime as part of the alumni band - woohoo!
>
>
> Cheers
> Vern
>
>
> On Fri, 25 Oct, 2024 at 12:31 PM, Peter Dow<petercdow@xxxxxxxxx> wrote:
>   
>
> To:midrange-l@xxxxxxxxxxxxxxxxxx
>
> Hi Justin,
>
> Like Rob, I'm curious why this is necessary:
>
> order by case when :H <> :H
>    then NumericCol     --decimal(7)  TableA
>    else CharCol end ;  --char(6)   TableA
>
> Since :H will never be unequal to :H, it's apparent you only want to use CharCol as the sort key.
>
>
> If it's a design requirement, I would think there should be a way to actually select the sort order, something like
>
> order by case when :H = 'numeric'
>    then NumericCol     --decimal(7)  TableA
>    else CharCol end ;  --char(6)   TableA
>
>
> If you just want to document the choices you could simply make one of
> them a comment
>
> order by
>    -- NumericCol  --decimal(7)  TableA
>       CharCol;    --char(6)     TableA
>
> --
> *Peter Dow* /
> Dow Software Services, Inc.
> 909 793-9050
> petercdow@xxxxxxxxx<mailto:petercdow@xxxxxxxxx>
> pdow@xxxxxxxxxxxxxx<mailto:pdow@xxxxxxxxxxxxxx> /
>
> On 10/24/2024 10:24 AM, Justin Taylor wrote:
> >    Rob:
> > The statement runs fine with just "ORDER BY CharCol".
> > sele
> > I put the case in the ORDER BY because there's a design requirement that
> > specifies those two different sort orders for the results.
> >
> >
> > Vern:
> > That explains it.
> >
> >
> > Thank you