Using CONVERT in CASE WHEN

[dEX2340]

I have a problem with below SQL code. Error message: "Error converting data type varchar to float."

CASE WHEN ... THEN volatility else 'NA' end) as volatility

So the field 'volatility' is of datatype 'varchar', while 'NA' is datatype 'float', right?

I guess it can be solved by using CONVERT in the ELSE part. But how to do is not clear to me. It should be something like this:

CASE WHEN ... THEN volatility else CONVERT(float,volatility) end) as volatility

But I need to have the value 'NA' in the field volatility if the when conditions are not met.

Many thanks for your help.

 

[PHV]

I guess that volatility is float.
I'd try this:

CASE WHEN ... THEN CAST(volatility AS VARCHAR) ELSE 'NA' END AS volatility

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dEX2340]

Thanks for the prompt reply PH. However, if I apply your code I get the value 'NA' for all the fields where I expect a volatility value (e.g. 31.3223501286548).

 

[PHV]

Why not posting the WHOLE case statement

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dEX2340]

I found it in the meantime.

THEN CONVERT(VARCHAR(MAX),volatility,128) else 'NA' end