Re: Implicit cast on ORDER BY

[VERNON HAMBERG Owner <vhamberg@xxxxxxxxxxxxxxx>]

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