If..then with math and field result

[Former Member]

Hi Fellows,

As you can see, I'm giving you a bit of work.
Definitely I could not find a way to do this on the web, I'd googleit a lot.
I have a query on multiple tables, there is a factor multiplied by the quantity that will return a final value.
This factor could be in the standard table or can be negotiated by the seller.
If it is negotiated, has precedence over the value on standard table.
I tried something like this above but does not work.

---

select codProduct,
quantity,
if negociatedValue > 0 then (or is not null)
   negociatedValue * quantity
else
   standardValue * quantity
from sales ...

[Former Member]

I just found a way to do it, but with case and using alias, like ...

select codProduct, quantity, case when negociatedValueis not null then negociatedValue * quantity else standardValue * quantity end from sales ...

[VolkerBarth]

I would prefer the isnull() function - or coalesce(), which works the same here but is standard SQL:

select codProduct, quantity, isnull(negociatedValue, standardValue) * quantity
from sales ...

[Former Member]

This might work:

select codProduct,
quantity,
if negociatedValue > 0 then (or is not null)
   negociatedValue * quantity
else
   standardValue * quantity
endif
from sales ...