How to Create CPHSTTRX_VIEW with Left Join to CPINVHDR Due to Invoice Database Archive
Version: All
Q - I 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;
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