Hi Folks,
I have am attempting to run the query below but unfortunately it is not working correctly.
There are two parts of the query that are causing the problems,namely the sections involving the CURTRXAM and ORTRXAMT fields. I have an IF condition that checks if the DOCABREV field is PMT, RTN or CR. If this is the case, I want to return the negative value of CURTRXAM and ORTRXAMT multiplied by the exchange rate (if the currency is not Euro)
Take one of these sections:
The problem arises when I add in the "0-" to the query. If I have the positve value in both sections of the IF command the query will return the correct positive value. However, when I try to get a negative value by setting it equal to 0 - "positive value", the query just returns zero. Any ideas how I can get this to return a negative value.
Mighty
I have am attempting to run the query below but unfortunately it is not working correctly.
Code:
SELECT RM20101.DOCNUMBR, RM20101.DOCDATE, RM40401.DOCABREV, IF(RTRIM(RM40401.DOCABREV) = 'SLS', 'PO: ' + RM20101.CSPORNBR, RM20101.TRXDSCRN) AS Description, IF(RTRIM(RM40401.DOCABREV) IN ('RTN', 'PMT', 'CR'), 0-Round(RM20101.CURTRXAM * IF(RTRIM(RM20101.CURNCYID) = 'EUR', 1, MC020102.XCHGRATE), 2), Round(RM20101.CURTRXAM * IF(RTRIM(RM20101.CURNCYID) = 'EUR', 1, MC020102.XCHGRATE), 2)), IF(RTRIM(RM40401.DOCABREV) IN ('RTN', 'PMT', 'CR'), 0-Round(RM20101.ORTRXAMT * IF(RTRIM(RM20101.CURNCYID) = 'EUR', 1, MC020102.XCHGRATE), 2), round(RM20101.ORTRXAMT * IF(RTRIM(RM20101.CURNCYID) = 'EUR', 1, MC020102.XCHGRATE), 2)) as dispAmt FROM (RM20101 INNER JOIN RM40401 ON RM20101.RMDTYPAL = RM40401.RMDTYPAL) LEFT JOIN MC020102 ON RM20101.DOCNUMBR = MC020102.DOCNUMBR WHERE RM20101.CUSTNMBR='IBS' ORDER BY RM20101.DOCNUMBRThere are two parts of the query that are causing the problems,namely the sections involving the CURTRXAM and ORTRXAMT fields. I have an IF condition that checks if the DOCABREV field is PMT, RTN or CR. If this is the case, I want to return the negative value of CURTRXAM and ORTRXAMT multiplied by the exchange rate (if the currency is not Euro)
Take one of these sections:
Code:
IF(RTRIM(RM40401.DOCABREV) IN ('RTN', 'PMT', 'CR'), 0-Round(RM20101.CURTRXAM * IF(RTRIM(RM20101.CURNCYID) = 'EUR', 1, MC020102.XCHGRATE), 2), Round(RM20101.CURTRXAM * IF(RTRIM(RM20101.CURNCYID) = 'EUR', 1, MC020102.XCHGRATE), 2))The problem arises when I add in the "0-" to the query. If I have the positve value in both sections of the IF command the query will return the correct positive value. However, when I try to get a negative value by setting it equal to 0 - "positive value", the query just returns zero. Any ideas how I can get this to return a negative value.
Mighty