Query with Exact Max

I am working with an ERP software call Exact Max.  The database is Pervasive.SQL V8, which i'm not too familiar with.  I can create simple queries, but need to extend it a bit
 
I need to pull data into excel, but it's extremely goofy with the query syntax.  Anyone familiar with this type of product?  I need to select Max transaction date for each part.  Below is my query, but Max doesn't seem to be a function that I can use.  Seems I can't use "MAX" and can't use "AS" for alias.


SELECT DISTINCT MAX("Transaction History".TRXDATE_15
), "Part Master".PRTNUM_01, "Part Master".PMDES1_01
FROM "Part Master" "Part Master", "Part Vendor" "Part Vendor",
 "Transaction History" "Transaction History", "Vendor Master" "Vendor Master"
WHERE "Part Master".PRTNUM_01 = "Part Vendor".PRTNUM_07 
AND "Part Vendor".PRTNUM_07 = "Transaction History".PRTNUM_15 
AND "Part Vendor".VENID_07 = "Transaction History".VENID_15 
AND "Transaction History".VENID_15 = "Vendor Master".VENID_08
ORDER BY "Part Master".PRTNUM_01

Open in new window

holemaniaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
Before giving up on the MAX() aggregate function, try removing the DISTINCT keyword.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
holemaniaAuthor Commented:
Seems like that worked.  Now can alias be use and can I do nested query?

Example:

SELECT ID, DESCRIPTION
FROM PART LEFT OUTER JOIN (SELECT MAX(DATE) AS TRANS_DATE FROM TRANSACTION) TR ON TR.PART_ID = ID
0
dqmqCommented:
Try omitting the "AS" keyword, also.  

Note, your syntax (if working) returns the max date of ALL parts! Seems like this may be what you want:

SELECT ID, DESCRIPTION, MAX(DATE) TRANS_DATE
FROM PART LEFT OUTER JOIN TRANSACTION TR ON TR.PART_ID = PART.ID
0
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

holemaniaAuthor Commented:
Ah okay.  Alias is still not working.  Tried the following:

MAX("Transaction History".TNXDTE_15) TRANS_DATE

MAX("Transaction History".TNXDTE_15) "TRANS_DATE"

MAX("Transaction History".TNXDTE_15) AS TRANS_DATE
0
dqmqCommented:
All of those are good syntax.  What is the complete SQL statement and what is the error message?
0
holemaniaAuthor Commented:
Complete SQL statement is what I posted in my original question.  I am having issue giving these field headers alias.  It is giving me an error.  See error
MAX-ERROR.jpg
0
SharathData EngineerCommented:
try this
SELECT MAX("Transaction History".TRXDATE_15), 
       "Part Master".PRTNUM_01, 
       "Part Master".PMDES1_01
  FROM "Part Master" "Part Master", 
       "Part Vendor" "Part Vendor",
       "Transaction History" "Transaction History", 
       "Vendor Master" "Vendor Master"
 WHERE "Part Master".PRTNUM_01 = "Part Vendor".PRTNUM_07 
   AND "Part Vendor".PRTNUM_07 = "Transaction History".PRTNUM_15 
   AND "Part Vendor".VENID_07 = "Transaction History".VENID_15 
   AND "Transaction History".VENID_15 = "Vendor Master".VENID_08
 GROUP BY "Part Master".PRTNUM_01, "Part Master".PMDES1_01
 ORDER BY "Part Master".PRTNUM_01

Open in new window

0
holemaniaAuthor Commented:
Sharath_123, that work, but the issue i'm encoutering now is giving alias naming to those fields. Normally a SQL Query I can just do a SELECT MAX("Transaction History".TRXDATE_15) AS Transaction_date.....

With the query from Pervasive, when doing that I get an error.
0
holemaniaAuthor Commented:
I guess my frustration is that since I am refreshing the data from Excel, each time I do that, the header name changes to the default header or field name of the database which make no sense to my user.  So now that I got the Max record to display, I also want to fix the alias.
0
SharathData EngineerCommented:
So did you try like this? In your actual query, you missed the GROUP BY.
SELECT MAX("Transaction History".TRXDATE_15) AS TRANS_DATE, 
       "Part Master".PRTNUM_01, 
       "Part Master".PMDES1_01
  FROM "Part Master" "Part Master", 
       "Part Vendor" "Part Vendor",
       "Transaction History" "Transaction History", 
       "Vendor Master" "Vendor Master"
 WHERE "Part Master".PRTNUM_01 = "Part Vendor".PRTNUM_07 
   AND "Part Vendor".PRTNUM_07 = "Transaction History".PRTNUM_15 
   AND "Part Vendor".VENID_07 = "Transaction History".VENID_15 
   AND "Transaction History".VENID_15 = "Vendor Master".VENID_08
 GROUP BY "Part Master".PRTNUM_01, "Part Master".PMDES1_01
 ORDER BY "Part Master".PRTNUM_01

Open in new window

0
holemaniaAuthor Commented:
Yeah tried it like that and as long as I give it the alias, it error out.  If I leave out the alias, then it's all fine.  Only issue is that when I do a refresh data, the header labeling change to all the default field name.
0
SharathData EngineerCommented:
then i have no clue. by the way, what is your sql database?
0
Bill BachPresident and Btrieve GuruCommented:
Which version of PSQLV8 are you running?  I tested this simplye query int he DEMODATA table, and it works just fine:
 
SELECT MAX("Person".ID) AS TTT FROM "Person"

I ran this on PSQLV8.7.  If you have an older one, then you may wish to patch to the latest Service Pack 3 (v8.7) release and try again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.