Pervasive Query Issue 1

[Mighty]

Hi Folks,

I have am attempting to run the query below but unfortunately it is not working correctly.

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:

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

 

[mirtheil]

What happens if you hard code some of the values (CURTRXAM, CURNCYID, XCHGRATE) for a test? I did a simple test with some hard coded values and got a negative number when subtracted from zero. You could also try multiplying by -1.
I'm using PSQL 9.5.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Mighty]

Hi Mirtheil,

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:

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'), Round(RM20101.CURTRXAM * IF(RTRIM(RM20101.CURNCYID) = 'EUR', 1, MC020102.XCHGRATE) * -1, 2), Round(RM20101.CURTRXAM * IF(RTRIM(RM20101.CURNCYID) = 'EUR', 1, MC020102.XCHGRATE), 2)) as calcAmt, IF(RTRIM(RM40401.DOCABREV) IN ('RTN', 'PMT', 'CR'), Round(RM20101.ORTRXAMT * IF(RTRIM(RM20101.CURNCYID) = 'EUR', 1, MC020102.XCHGRATE) * -1, 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

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

 

[mirtheil]

Thanks for the star. Glad it's working for you. I understand about app compatibility. One thing about upgrading, you might consider V9 rather than V8 because V8 is going unsupported in December of this year.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Mighty]

Unfortunately the latest version of my ERP application which I am upgrading to next month is only compatible with V8.

Mighty