sql sum statement

[sagarh]

I have a table with the following values:-

order value currency
0001 100
0002 100
0003 100 1.47
0004 150 1.58
0005 100 1.47
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/currency 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.
Any help is very much appreciated!

[YuckFou]

declare @tab table ( ord char(4) not null, value int not null, currency decimal(3,2) null )
insert into @tab values ('0001' , 100 , null)
insert into @tab values ('0002' , 100 , null)
insert into @tab values ('0003' , 100 , 1.47)
insert into @tab values ('0004' , 150 , 1.58)
insert into @tab values ('0005' , 100 , 1.47)
insert into @tab values ('0006' , 100 , null)
-- first way with isnull() , for oracle use nvl()
select ord , str(value/isnull(currency,1.0),10,2) as total from @tab
-- second way with case
select ord , str(value/case when currency is null then 1.0 else currency end,10,2) as total from @tab
-- and maybe more

[sagarh]

worked a treat!