how can I use case and cast function together(please find error in this syntax )

Question

IN this I am getting error in CASE bracket like int should not be here.

 with temp as
            (select name,sex,cast(case when age = 'NA' then '0' else age end as int) as age
              ,team,games,city,sport, event, medal
            from olympics_history),
        ranking as
            (select *, rank() over(order by age desc) as rnk
            from temp
            where medal='Gold')
    select *
    from ranking
    where rnk = 1;

Casting as int is not an option in mysql use either signed or unsigned and review the manual for permitted type values https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html — P.Salmon, Aug 03 '22 at 06:48
Does this answer your question? [Cast from VARCHAR to INT - MySQL](https://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql) — P.Salmon, Aug 03 '22 at 06:50

score 0 · Accepted Answer · answered Aug 03 '22 at 07:29

0

Use SIGNED instead of int

with temp as
            (select name,sex,cast(case when age = 'NA' then '0' else age end as SIGNED) as age
              ,team,games,city,sport, event, medal
            from olympics_history),
        ranking as
            (select *, rank() over(order by age desc) as rnk
            from temp
            where medal='Gold')
    select *
    from ranking
    where rnk = 1;

answered Aug 03 '22 at 07:29

vinayak

67
3

It did solved the error but still i am not getting output , there is no error in syntax.But i can only see column heading no values in output – Dixit Aug 03 '22 at 12:26

how can I use case and cast function together(please find error in this syntax )

1 Answers1