locked
case when to return float RRS feed

  • Question

  • User-804681621 posted
    I can't use 'case when' to return different format as below sql.

    Select case when ccy='hkd' then cast(price as decimal(18,2)) else round(price,0) from table.

    How can I return float data type but with 2 decimal for currency hkd, otherwise, return float data type with no decimal.
    How to solve it?
    Friday, February 26, 2016 6:50 AM

Answers

  • User-1716253493 posted

    format is a string that represent a number value.

    try this

    Select case when ccy='hkd' then CATS(cast(price as decimal(18,2)) as varchar) else cast(cast(price as int) as varchar) end from table.

    or use FORMAT()

    FORMAT ... ELSE FORMAT ...

    https://msdn.microsoft.com/en-us/library/hh213505.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 26, 2016 7:17 AM

All replies

  • User-1716253493 posted

    format is a string that represent a number value.

    try this

    Select case when ccy='hkd' then CATS(cast(price as decimal(18,2)) as varchar) else cast(cast(price as int) as varchar) end from table.

    or use FORMAT()

    FORMAT ... ELSE FORMAT ...

    https://msdn.microsoft.com/en-us/library/hh213505.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 26, 2016 7:17 AM
  • User364663285 posted

    Try

    Select case when ccy='hkd' then cast(price as decimal(18,2)) else cast(round(price,0) as decimal(18,0)) from table

    Friday, February 26, 2016 7:19 AM
  • User-804681621 posted

    Try

    Select case when ccy='hkd' then cast(price as decimal(18,2)) else cast(round(price,0) as decimal(18,0)) from table

    when i use "case when" it can't return different decimal in the result set. it can only return same decimal place for all data.

    Sunday, February 28, 2016 8:06 AM
  • User364663285 posted

    wmec

    Try

    Select case when ccy='hkd' then cast(price as decimal(18,2)) else cast(round(price,0) as decimal(18,0)) from table

    when i use "case when" it can't return different decimal in the result set. it can only return same decimal place for all data.

    It means such functionality better be performed through other Report designer like Crystal report.

    Monday, February 29, 2016 2:02 AM