Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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

0
Citigirlgem
Asked:
Citigirlgem
  • 4
  • 2
2 Solutions
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now