Pervasive Query Issue
Pervasive Query Issue
(OP)
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.
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.DOCNUMBR
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:
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
RE: Pervasive Query Issue
I'm using PSQL 9.5.
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com
RE: Pervasive Query Issue
It never even dawned on me to try multiplying by -1. How stupid is that!!! Even so, it only worked when I put the multiplication inside the Round function within the query. This worked fine:
CODE
As usual you have come up trumps. I am still using Pervasive SQL 200i. Will be upgrading to V8 next month - unfortunately I am limited by compatability with my ERP application.
Mighty
RE: Pervasive Query Issue
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com
RE: Pervasive Query Issue
Mighty