I have a table with the following values:-

order_no val spare
0001 100
0002 100
0003 100 /1.4700000
0004 150 /1.5800000
0005 100 /1.4700000
0006 100
0007 100
0008 100
etc.

I need to do a select statement that gives me the local currency value of the above therefore value/spare will do except when there is no value in the currency field it obviously returns an error as you cannot divide by zero - is there a way round this so that the statement only divides by the value in the currency column when it is greater than 0? I need the output to show as follows:

order total
0001 100
0002 100
0003 68.02
0004 94.93
0005 68.02
0006 100
0007 100
0008 100
etc.
when I generate create table statement, for the table this data is in, I get the following (truncated)
CREATE TABLE scheme.orders(
order_no char(10) NOT NULL,
val float,
spare char(34) NOT NULL
)
GO
I have tried select str(val/isnull((substring(spare,16,10)),1.0),10,2) as total from orders this does not work because spare is not null because of the table definition (i think...)
Any help is very much appreciated :>

I don't know if you have constraints that require your fields to be the types that they are, but I have something that works for me, with a few changes to the field declarations. This was tested in MS SQL, let me know if it doesn't work. I can also test this in mySQL.

You have to make the rate field some sort of numeric type (float is best) and you have to do the math in the query. You can leave val and orderID the way you have them, although I would expect an ID field to be bigint or something of that sort, and if this is the top_level file for orders, it would also be auto_increment.

the fields data types cannot be changed - they are from our accounts database.

scheme is the name of the DB? What RDBMS are you using?

Also, how is spare defined as a NOT NULL field when you have NULL values in it?

I'm also assuming that the text string is comprised of the mathematical operator and the value with which to operate on VAL.

Am I clear?

select val / (CASE spare WHEN 0 THEN 1 ELSE spare END)
from table;

using MS Sql2000 - scheme is the owner of the table. NOT NULL is how the field is defined as out of the box for the accounts package that we have installed.

The text string has the following format /1.47000000 which I use substring(spare,16,10) to get 1.47000000 then i try the calculation but unforunately it fails because not all fields have a value.

this nearly works! however having slightly amended the select statement to read as follows:-

select val / (CASE (substring(spare,16,10)) WHEN 0 THEN 1 ELSE (substring(spare,16,6)) END)
from scheme.orders

I get some rows return data then the following error:-

Error: Syntax error converting the varchar value '1.59540000' to a column of data type int. (State:22005, Native Code: F5)
34 Row(s) affected
Syntax error converting the varchar value '1.59540000' to a column of data type int.

any ideas?

To return the results with greater precision, replace REAL with FLOAT.

select val /
ISNULL((CASE CAST(spare As REAL) WHEN 0 THEN 1 ELSE CAST(spare As REAL) END),1)
from table

This did the trick! Thankyou very much for your help!