Re: Implicit cast on ORDER BY

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hi Justin

I just googled for *conditional order by* and got an interesting hit 
that might help.

In a CASE, the results must be the same data type, I saw. It wasn't IBM 
i, but the rule still holds.

One solution was to use multiple CASE, something like this - I'm just 
taking a wild shot here - each CASE can have its own data type -

order by
(case :H <> :H then NumericCol end)
(case :H = :H then CharCol end)

Your USING clause would add a couple values.

Here's a link to the page - 
https://stackoverflow.com/questions/15621609/t-sql-conditional-order-by

Regards
Vern

On 10/24/2024 7:14 AM, Justin Taylor wrote:
> I have this SQL:
> select *
>    from TableA A left join TableB B
>       on A.ID = B.ID
>    where A.Column = :H
> order by case when :H <> :H
>    then NumericCol  --decimal(7)  TableA
>    else CharCol end ;  --char(6)   TableA
>
> It runs fine as long as the result set only includes numbers for CharCol,
> but it fails with a conversion error if CharCol contains letters.  I
> commented out the ORDER BY and it runs.  I put the ORDER BY back and it
> failed again, so I know the ORDER BY is the problem.  It's casting CharCol
> to decimal(7).  I tried flipping the condition in the ORDER BY, but that
> didn't make a difference.
>
> 1. I'm curious why it's doing the cast.
> 2. Is there any relatively painless way to stop it?
>
> Thx