Re: Implicit cast on ORDER BY

[Rob Berendt <robertowenberendt@xxxxxxxxx>]

I seem to recall there are situations where we'd do stuff like
when :H<>:H
but I'd like to know why you're doing it in this situation.
I'm wondering if you stopped using this, and only used
order by CharCol
if it would stop the casting.


On Thu, Oct 24, 2024 at 8:15 AM Justin Taylor <jtaylor.0ab@xxxxxxxxx> 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
> --
> 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@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.