Pervasive 9 SQL Query

This query returns nothing in the Pervasive control centre, but works fine in Access based on the same Pervasive linked tables.  Why?

SELECT SALES_HISTORY_DETAIL.WHSE, SALES_HISTORY_DETAIL.CODE, Max(SALES_HISTORY_HEADER.IN_DATE) AS MaxOfIN_DATE
FROM SALES_HISTORY_HEADER INNER JOIN SALES_HISTORY_DETAIL ON SALES_HISTORY_HEADER.NUMBER = SALES_HISTORY_DETAIL.NUMBER
GROUP BY SALES_HISTORY_DETAIL.WHSE, SALES_HISTORY_DETAIL.CODE
having (((SALES_HISTORY_DETAIL.WHSE)<>'') AND ((SALES_HISTORY_DETAIL.CODE)<>''))

Open in new window

CitigirlgemAsked:
Who is Participating?
 
BrandonGalderisiCommented:
HAVING us used for applying filter criteria to field used in aggregates (ex. MAX).  You should use the standard WHERE clause.


SELECT SALES_HISTORY_DETAIL.WHSE, SALES_HISTORY_DETAIL.CODE, Max(SALES_HISTORY_HEADER.IN_DATE) AS MaxOfIN_DATE
FROM SALES_HISTORY_HEADER INNER JOIN SALES_HISTORY_DETAIL ON SALES_HISTORY_HEADER.NUMBER = SALES_HISTORY_DETAIL.NUMBER
WHERE (((SALES_HISTORY_DETAIL.WHSE)<>'') AND ((SALES_HISTORY_DETAIL.CODE)<>''))
GROUP BY SALES_HISTORY_DETAIL.WHSE, SALES_HISTORY_DETAIL.CODE

Open in new window

0
 
CitigirlgemAuthor Commented:
I tried that and it still gives me nothing.  Any other ideas?
0
 
BrandonGalderisiCommented:
What happens if you remove the WHERE clause?  
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
CitigirlgemAuthor Commented:
It still returns nothing.
0
 
TextReportCommented:
Agreed about the WHERE rather than HAVING, try using IS NOT NULL rather than <> ''
Cheers, Andrew
SELECT SALES_HISTORY_DETAIL.WHSE, SALES_HISTORY_DETAIL.CODE, Max(SALES_HISTORY_HEADER.IN_DATE) AS MaxOfIN_DATE
FROM SALES_HISTORY_HEADER INNER JOIN SALES_HISTORY_DETAIL ON SALES_HISTORY_HEADER.NUMBER = SALES_HISTORY_DETAIL.NUMBER
WHERE SALES_HISTORY_DETAIL.WHSE IS NOT NULL AND SALES_HISTORY_DETAIL.CODE IS NOT NULL
GROUP BY SALES_HISTORY_DETAIL.WHSE, SALES_HISTORY_DETAIL.CODE

Open in new window

0
 
CitigirlgemAuthor Commented:
OK I figured it out. I needed and extra criteria in the where clause: "sales_history_Detail.RecNo>0".
Thanks anyway.
0
 
CitigirlgemAuthor Commented:
Thanks for the quick response.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.