Menu
I have a query that is returning the exchange rate value set up in our system. Not every order will have an exchange rate (currate.currentrate) so it is returning null values.
Can I get it to return 1 instead of null?
Something like an if statement maybe:
if isnull(currate.currentrate) then 1 else currate.currentrate Here is my query below. I greatly appreciate all your help!
SELECT orderhed.ordernum, orderhed.orderdate, currrate.currencycode, currrate.currentrate FROM orderhed LEFT OUTER JOIN currrate ON orderhed.company = currrate.company AND orderhed.orderdate = currrate.effectivedate 
388
The IS NULL condition is used in SQL to test for a NULL value. It returns TRUE if a NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
SQL Bit data type can only have value either 0, 1 or NULL , if you insert other value, it's considered to 1 (Exception : If you insert ' False ' it will became 0, ' True ' will became 1).
Because the result of any arithmetic comparison with NULL is also NULL , you cannot obtain any meaningful results from such comparisons. In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1 .
You can use a CASE statement.
SELECT CASE WHEN currate.currentrate IS NULL THEN 1 ELSE currate.currentrate END FROM ... If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
