×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Pervasive Query Issue

thread318-1277320

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.

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

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

RE: Pervasive Query Issue

(OP)
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:

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'), 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

RE: Pervasive Query Issue

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

RE: Pervasive Query Issue

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

Mighty

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close