Hi, I have the following Problem and I am not sure if it is a bug:
Goal is to cast a string of a negative number with '-' AT THE END to
decimal format without referencing to a table (without from).
Of course I could just use '3.' but I think '4.' should also be possible
since the else clause is actually dead code.
*SELECT* '1. positiv number and replace minus in both cases ->works' *AS*
description
, *CASE*
*WHEN* *RIGHT**(*'122345.34', *1**)* *=* '-'
*THEN* *CAST**(**REPLACE**(*'122345.34','-',''*)* *AS* *DECIMAL**(**31*,
*2**))* *** *(-**1**)*
*ELSE* *CAST**(**REPLACE**(*'122345.34','-',''*)* *AS* *DECIMAL**(**31*,
*2**))*
*END* *AS* result
*/*
*SELECT* '2. positiv number and replace minus in one case ->works' *AS*
description
, *CASE*
*WHEN* *RIGHT**(*'122345.34', *1**)* *=* '-'
*THEN* *CAST**(**REPLACE**(*'122345.34','-',''*)* *AS* *DECIMAL**(**31*,
*2**))* *** *(-**1**)*
*ELSE* *CAST**(*'122345.34' *AS* *DECIMAL**(**31*,*2**))*
*END* *AS* result
*/*
*SELECT* '3. negativ number and replace minus in both cases ->works' *AS*
description
, *CASE*
*WHEN* *RIGHT**(*'122345.34-', *1**)* *=* '-'
*THEN* *CAST**(**REPLACE**(*'122345.34-','-',''*)* *AS* *DECIMAL**(**31*,
*2**))* *** *(-**1**)*
*ELSE* *CAST**(**REPLACE**(*'122345.34-','-',''*)* *AS* *DECIMAL**(**31*,
*2**))*
*END* *AS* result
*/*
*--all the above work, shouldn't this too?*
*SELECT* '4. negativ number and replace minus in one case -> Data
conversion error' *AS* description
, *CASE*
*WHEN* *RIGHT**(*'122345.34-', *1**)* *=* '-'
*THEN* *CAST**(**REPLACE**(*'122345.34-','-',''*)* *AS* *DECIMAL**(**31*,
*2**))* *** *(-**1**)*
*ELSE* *CAST**(*'122345.34-' *AS* *DECIMAL**(**31*,*2**))*
*END* *AS* result
*/*
*SELECT* '5. negativ number and replace minus in one case with FROM ->works
again' *AS* description
, *CASE*
*WHEN* *RIGHT**(*value, *1**)* *=* '-'
*THEN* *CAST**(**REPLACE**(*value,'-',''*)* *AS* *DECIMAL**(**31*,*2**))*
*** *(-**1**)*
*ELSE* *CAST**(*value *AS* *DECIMAL**(**31*,*2**))*
*END* *AS* result
*FROM* *(**SELECT* '122345.34-' *AS* value
*)*Thanks for your help!
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
-- Goal is to cast a string of a negative number with '-' AT THE END to decimal format without referencing to a table (without from).
-- Of course I could just use 3. but I think 4. should also be possible since the else clause is actually dead code.
SELECT '1. positiv number and replace minus in both cases ->works' AS description
, CASE
WHEN RIGHT('122345.34', 1) = '-'
THEN CAST(REPLACE('122345.34','-','') AS DECIMAL(31,2)) * (-1)
ELSE CAST(REPLACE('122345.34','-','') AS DECIMAL(31,2))
END AS result
/
SELECT '2. positiv number and replace minus in one case ->works' AS description
, CASE
WHEN RIGHT('122345.34', 1) = '-'
THEN CAST(REPLACE('122345.34','-','') AS DECIMAL(31,2)) * (-1)
ELSE CAST('122345.34' AS DECIMAL(31,2))
END AS result
/
SELECT '3. negativ number and replace minus in both cases ->works' AS description
, CASE
WHEN RIGHT('122345.34-', 1) = '-'
THEN CAST(REPLACE('122345.34-','-','') AS DECIMAL(31,2)) * (-1)
ELSE CAST(REPLACE('122345.34-','-','') AS DECIMAL(31,2))
END AS result
/
--all the above work, shouldn't this too?
SELECT '4. negativ number and replace minus in one case -> Data conversion error' AS description
, CASE
WHEN RIGHT('122345.34-', 1) = '-'
THEN CAST(REPLACE('122345.34-','-','') AS DECIMAL(31,2)) * (-1)
ELSE CAST('122345.34-' AS DECIMAL(31,2))
END AS result
/
SELECT '5. negativ number and replace minus in one case with FROM ->works again' AS description
, CASE
WHEN RIGHT(value, 1) = '-'
THEN CAST(REPLACE(value,'-','') AS DECIMAL(31,2)) * (-1)
ELSE CAST(value AS DECIMAL(31,2))
END AS result
FROM (SELECT '122345.34-' AS value)
