Hi, H2 will always evaluate constant expressions when parsing the statement, to avoid having to evaluate when executing. I don't think you should assume any specific time an expression is evaluated. Unlike in C, C++, Java, this is not specified in SQL.
Regards, Thomas On Thursday, April 7, 2016, Joshua Claßen <[email protected]> wrote: > 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] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>. > Visit this group at https://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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.
