How to Create CPHSTTRX_VIEW with Left Join to CPINVHDR Due to Invoice Database Archive

Release Date: 12/6/2019
Version: All

QI had to run Archive Invoices in the old fashioned way as the company ran out of invoice numbers and I didn’t have time to go to 8.5 (modifications). I ran the Archive procedure as outlined in the KB article below with an additional step that kept all of the 2019 invoices in the production data folder. 

Everything is working well on the operation side.

There is an issue with about 10 Crystal reports that compare 2018 and 2019 sales numbers. After I was notified about these reports, I planned to add the CPHSTTRX from the database of the Data_50 (the archive folder) with a Left Outer Join so that both 2018 and 2019 data can be accessed. Unfortunately, the CPHSTTRX_VIEW was used on the original report, which makes things extremely difficult.

My Question: Can you suggest how we can change the CPHSTTRX_VIEW to be Left Outer Join (as in all CPHSTTRX and any CPINVHDR if they exist)? If it is too difficult, the 10 reports will have to be rewritten…

A - Here is the original SQL script to create the view CPHSTTRX_VIEW: 

Create View CPHSTTRX_VIEW As
Select CPHSTTRX.*, ARCUSFIL.*, CPINVHDR.*, IMITMFIL.*
FROM CPHSTTRX, ARCUSFIL, CPINVHDR, IMITMFIL
WHERE HIST_TRX_CUST_NO = CUS_NO
AND HIST_TRX_INV_NO = INV_NO
AND HIST_TRX_ITEM_NO = ITEM_NO;  

You could create a new left join view CPHSTTRX_VIEWL by using the following:

Create View CPHSTTRX_VIEWL As
Select CPHSTTRX.*, ARCUSFIL.*, CPINVHDR.*, IMITMFIL.*
FROM CPHSTTRX INNER JOIN ARCUSFIL
ON HIST_TRX_CUST_NO = CUS_NO
LEFT JOIN CPINVHDR
ON HIST_TRX_INV_NO = INV_NO
INNER JOIN IMITMFIL
ON HIST_TRX_ITEM_NO = ITEM_NO;  

If you want to use the same view name (easier with your Crystal Report,) then you need to drop the existing view first before creating the view again:

Drop View CPHSTTRX_VIEW;

Create View CPHSTTRX_VIEW As
Select CPHSTTRX.*, ARCUSFIL.*, CPINVHDR.*, IMITMFIL.*
FROM CPHSTTRX INNER JOIN ARCUSFIL
ON HIST_TRX_CUST_NO = CUS_NO
LEFT JOIN CPINVHDR
ON HIST_TRX_INV_NO = INV_NO
INNER JOIN IMITMFIL
ON HIST_TRX_ITEM_NO = ITEM_NO;  

In either case, you need to save this script so that in the future, when the DDF is updated, you can re-apply this script.

EMK

Pervasive PSQL

  1. Btrieve Error Codes 001 - 199
  2. Btrieve Error Codes 3000 - 3099
  3. Btrieve Error Codes 3100 - 3199
  4. PSQL Version Required by Each Elliott Version
  5. Do I Need to Change PSQL Server Engine Default Parameters After Installing It?
  6. New Elliott PSQL Server Processor and RAM Suggestions
  7. Can I Dynamically Adjust Elliott / PSQL 11 Server Memory?
  8. Received "Your Computer Does Not Have PSQL 10 or 11 Client " Even though PSQL Client Is Just Installed
  9. Btrieve Error 161 on Password File When Starting Up Elliott
  10. Problems with Using Pervasive Rebuild Utility on APOPNFIL and AROPNFIL Tables
  11. Security Issue with Installing PSQL Client Remotely on User's Workstation
  12. PSQL and Distributed File System (DFS)
  13. How Do I Turn on PSQL Relational Engine Security?
  14. An Example of Debugging NOTE_ORD_VIEW PSQL Expression Evaluation Error
  15. Btrieve Error 025 on COP Open Order by Salesman Report
  16. What Is the *.^01 File for My PSQL Btrieve Table?
  17. Suggested Files to be Monitored by Audit Master
  18. Pervasive Backup Agent Is Not Compatible with Creating Work Files
  19. Hardware Recommendations for Your PSQL Database Server
  20. How to Optimize SQL SELECT Statement When Retrieving Data from Invoice History
  21. New User-Defined Functions in Elliott DDF
  22. How to Improve Query Performance When Retrieving Data from Notes & Invoice History
  23. How to Retrieve Tracking Number for an Order from Notes
  24. Actian PSQL Not Started Automatically After Server Reboot
  25. Create a New Database in the PCC for Relational Engine Access
  26. Slow PSQL Relational Engine Performance
  27. IPV6 May Cause Problem for PSQL 11 Relational Query
  28. DDF Files in DATA Folder May Confuse PSQL
  29. What to Do When PSQL 11 License Is Disabled
  30. Quick Installation Guide for Audit Master
  31. Quick User Guide for Audit Master
  32. PSQL 13, Micrsoft SQL Integration Service & Pervasive PSQL OLE DB Provider
  33. Your Firewall Needs to Allow Outbound Traffic to the Netherlands for PSQL Licensing Server Purposes
  34. A Case of Btrieve Error 046 on ARCRCLOG A/R Credit Card Log File
  35. A Support Case of Migrating to Different Version of DDF
  36. How to Clear the Message "Unable to Read your Users record (9/068)"
  37. Setup of the PSQL 13 Report Engine
  38. How to Create CPHSTTRX_VIEW with Left Join to CPINVHDR Due to Invoice Database Archive
  39. How to Access Elliott's Data by Using Query in Microsoft Office Excel 2019
  40. Elliott Database Naming Convention
  41. What Does Btrieve Error 080 Mean?

Feedback and Knowledge Base