Pervasive SQL Update with Joins - Error: Unknown table or correlation name

[soocallmebrady]

Hello,

I am in the process of updating an Order History Table (T_ORDER_HIST_LINE) using 3 other tables (T_CUSTOMER_MASTER, BRANCH_XREF and AREA_XREF).

Tables and Fields Involved
Table Name: T_ORDER_HIST_LINE
Fields: ORDER_NO, CUSTOMER, AREA, BRANCH

Table Name: T_CUSTOMER_MASTER
Fields: CUSTOMER, CODE_AREA, TERRITORY

Table Name: AREA_XREF
Fields: AREA_CODE, AREA_DESCRIPTION

Table Name: BRANCH_XREF
Fields: BRANCH_CODE, BRANCH_DESCRIPTION

I need to update two fields in the T_ORDER_HIST_LINE table. Those two fields are T_ORDER_HIST_LINE.BRANCH and T_ORDER_HIST_LINE.AREA. My initial thought is that two separate queries are needed to perform this. One query to update each field (BRANCH and AREA). Note: Branch = Territory. The two are same just different field names.

The reason why these need to be updated is that T_CUSTOMER_MASTER has the correct AREA and BRANCH/TERRITORY but the T_ORDER_HIST_LINE table doesn't have the correct AREA and BRANCH/TERRITORY.

The idea is that both update queries would look at T_ORDER_HIST_LINE and T_CUSTOMER_MASTER and compare customers (CUSTOMER = Customer number). It will be a join that includes all records from T_ORDER_HIST_LINE and only those records from T_CUSTOMER_MASTER where the joined fields are equal.

The next join needed is based on what query we are running. The UPDATE_AREA query will include a join that will include all records from T_ORDER_HIST_LINE and only those records from AREA_XREF where the joined fields are equal. The UPDATE_BRANCH query will include a join that will include all records from T_ORDER_HIST_LINE and only those records from BRANCH_XREF where the joined fields are equal.

The reason why we have a the cross reference tables (AREA_XREF and BRANCH_XREF) is because we want to compare the data in T_ORDER_HIST_LINE.AREA and T_ORDER_HIST_LINE.BRANCH to the XREF tables to make sure the value entered into those fields is valid and correct. Some of our customers are two branches but each of their orders can be classified as the correct branch and area for that specific order based on what they are buying.

The idea is that if the value in T_ORDER_HIST_LINE.AREA and T_ORDER_HIST_LINE.BRANCH match one of the values in AREA_XREF and BRANCH_XREF, the fields will be left alone. If the value in T_ORDER_HIST_LINE.AREA and T_ORDER_HIST_LINE.BRANCH don't match one of the values in AREA_XREF and BRANCH_XREF, then T_ORDER_HIST_LINE.AREA and T_ORDER_HIST_LINE.BRANCH will be changed for that customer order based on the value listed in T_CUSTOMER_MASTER.TERRITORY and T_CUSTOMER_MASTER.CODE_AREA.

I did a test update query in MS Access and it worked. Here is the SQL used:

UPDATE_AREA
UPDATE ([Customer Master]
RIGHT JOIN [Order History] ON [Customer Master].[Customer #] = [Order History].[Customer #])
LEFT JOIN Area_Xref ON [Order History].Area = Area_Xref.AREA_CODE
SET [Order History].Area = [Customer Master].[Area]
WHERE (((Area_Xref.AREA_CODE) Is Null));

UPDATE_BRANCH
UPDATE ([Customer Master]
RIGHT JOIN [Order History] ON [Customer Master].[Customer #] = [Order History].[Customer #])
LEFT JOIN Branch_Xref ON [Order History].Branch = Branch_Xref.BRANCH_CODE
SET [Order History].Branch = [Customer Master].[Branch]
WHERE ((([Branch_Xref].[BRANCH_CODE]) Is Null));

I then tried to turn that Access code into Pervasive SQL after looking around on other forums. Here is the Pervasive SQL code used:

UPDATE_BRANCH
UPDATE T_ORDER_HIST_LINE
SET T_ORDER_HIST_LINE.BRANCH = T_CUSTOMER_MASTER.TERRITORY
WHERE EXISTS
(SELECT *
FROM T_ORDER_HIST_LINE
RIGHT JOIN T_ORDER_HIST_LINE ON T_CUSTOMER_MASTER.CUSTOMER = T_ORDER_HIST_LINE.CUSTOMER
LEFT JOIN BRANCH_XREF ON T_ORDER_HIST_LINE.BRANCH = BRANCH_XREF.BRANCH_CODE
WHERE (BRANCH_XREF.BRANCH_CODE IS NULL));

This code is returing this error:
Execution Error = [LNA][PSQL][SQL Engine]Unknown table or correlation name [T_CUSTOMER_MASTER].

UPDATE_AREA
UPDATE T_ORDER_HIST_LINE
SET T_ORDER_HIST_LINE.BRANCH = T_CUSTOMER_MASTER.TERRITORY
WHERE EXISTS
(SELECT *
FROM T_ORDER_HIST_LINE
RIGHT JOIN T_ORDER_HIST_LINE ON T_CUSTOMER_MASTER.CUSTOMER = T_ORDER_HIST_LINE.CUSTOMER
LEFT JOIN AREA_XREF ON T_ORDER_HIST_LINE.BRANCH = AREA_XREF.AREA_CODE
WHERE (AREA_XREF.AREA_CODE IS NULL));

This code is returing this error:
Execution Error = [LNA][PSQL][SQL Engine]Unknown table or correlation name [T_CUSTOMER_MASTER].

NOTE: I am on PSQL Install Version 12.01.052

Any advice on how to correct the UPDATE Queries in pervasive SQL? If you need more information, please let me know. Any help is greatly appreciated as I have been staring at this problem for about a week or two now.