×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close