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

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;

CodePudding user response：

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;