Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Left Outer join returning "Unknown Error -19"

Status
Not open for further replies.
michellepace

michellepace

Technical User
Sep 12, 2020
7
ZA
Hello,

I am following the Left Outer Join example as demonstrated by Table_32 on page 154 of the pervasive v13 manual. Namely, the expression they give is:
SQL: 
SELECT * FROM Emp LEFT OUTER JOIN Dept ON Emp.DeptID = Dept.DeptID

To avoid confusion, I have named my tables in the same manner. And this is the expression I wrote:
SQL: 
SELECT Emp.Ref, Emp.amt, Dept.amt FROM Emp LEFT OUTER JOIN Dept ON Emp.Ref = Dept.MatchRef

The error I am getting back is: "Unknown error: -19"

I rewrote the expression as the below, I got the same error again.
[link SQL]
SELECT e.Ref, e.amt, d.amt
FROM Emp e
LEFT OUTER JOIN
(
SELECT * FROM Dept
) d
ON e.Ref = d.MatchRef​
[/url]

Could anyone please tell me how I go about joining my Dept. table to my Emp table? (keeping all Emp records)


Emp
CSCode PPeriod Ref amt rows
====== ======= ========================= ======================== ========================
MP30 1 IN105604 11500.0 1.0
MP30 1 IN105605 11500.0 1.0
MP30 1 IN105606 11500.0 1.0
MP30 1 IN105607 5753.4 2.0
MP30 1 IN105608 2300.0 1.0
MP30 1 NEDBANK 111.0 1.0
MP30 2 NEDBANK 3900.99 5.0
MP30 3 IN105609 22999.99 2.0

Dept
CSCode PPeriod MatchRef amt
====== ======= ========================= ========================
MP30 2 IN105604 -11500.0
MP30 2 IN105605 -11500.0
MP30 2 IN105606 -11500.0
MP30 2 IN105607 -1400.0
MP30 2 IN105608 -100.0
MP30 2 NEDBANK -1000.0

Thanks very much in advance,
Michelle

 
Sort by date Sort by votes
A couple of questions:
- What tool are you using to run the query?
- Is there anything else in the error?
- Does the sample query work against the DEMODATA database?
- What datatypes are Emp.Ref and Dept.MatchRef?


Mirtheil
 
Upvote 0 Downvote
So, I tested with my PSQL 11.30 and got a slightly different error. I got an "error in expression: dept . amt".
I still got an error when I run a simple
Code: 
SELECT amt FROM DEPT
Then I realized that I was using the DEMODATA database and it already had a DEPT table. I created a new DEPT table (called DEPT2) and changed the query to match the new table and it worked for me.

You might want to check the DEPT table and make sure that it has an AMT field and you're using the

Mirtheil
 
Upvote 0 Downvote
Hi Mirtheil,

Thank you very much for your replies. In answer to your questions:

- What tool are you using to run the query?
PSQL Control Centre 13.30.035
- Is there anything else in the error?
No, only the error message I give above (in a popup window)
- Does the sample query work against the DEMODATA database?
wow. is that what "demodata" is for! I've never even expanded it to see the tables. Mmm.. strangely enough, I see a list of tables (Billing, Class, Course, Dept, Enrolls, Faculty, Person, Rooom, Student, Tuition)... but when I run a select on any of the tables - its completely empty. So in answer - no, not for me.
- What datatypes are Emp.Ref and Dept.MatchRef?
From the tables where the data originated from - both are of type Char 25
Here you go, these are my actual tables (the equivalents of Emp and Dept)
1_z5rayb.png


You might want to check the DEPT table and make sure that it has an AMT field and you're using the
Sorry Mirtheil, please tell me again what I need to check? I'm not quite sure from your reply what you mean?

Thanks very much once again for your help.
Michelle
 
Upvote 0 Downvote
So, I think your problem is related to the SELECT list in your statement. First, you have two AMT fields in your select but you aren't naming them differently. You need to alias the field names otherwise the SQL parser won't know which field you are referencing. I personally like to alias all of the fields in a JOIN so I know which table each field is coming from.
So:
Code: 
SELECT Emp.Ref, Emp.amt, Dept.amt FROM Emp LEFT OUTER JOIN Dept ON Emp.Ref = Dept.MatchRef
Should be:
Code: 
SELECT Emp.Ref, Emp.amt EmpAMT, Dept.amt DeptAMT FROM Emp LEFT OUTER JOIN Dept ON Emp.Ref = Dept.MatchRef

Second, your post is a little confusing. In your DebtorReceivables (Emp) view, you have an Amount field but the JOIN statement is referencing an AMT field for that View . You need to make sure that any field you list in a statement actually exists in the Table / View definition.

Based on your VIEW definitions for "Emp" and "Dept," I changed your SQL to this and it did not return any errors:
Code: 
SELECT Emp.Ref, Emp.amount EmpAMT, Dept.amt DeptAMT FROM DebtorReceivables Emp LEFT OUTER JOIN DebtorMatchesOnRec dept ON Emp.Ref = Dept.MatchRef



Mirtheil
 
Upvote 0 Downvote
Hi Mirtheil,

Goodness I am not sure what is going on. I tried implementing your solution, but still, I get "Unknown Error -19". If you get a moment, could I ask you to create the below and see if it works for you?

SELECT DR.Ref, DR.Amount, DM.AmountMatched
FROM DebtorReceivables DR LEFT OUTER JOIN DebtorMatchesOnRec DM ON DR.Ref = DM.MatchRef


DebtorReceivables
CSCode PPeriod dDate Ref Amount rows
====== ======= ========== ========================= ======================== ========================
MP31 1 1/1/2020 IN105610 5000.01 2.0
MP31 2 2/1/2020 IN105611 11500.0 1.0


DebtorMatchesOnRec
CSCode PPeriod MatchRef AmountMatched LastMatchDate
====== ======= ========================= ======================== =============
MP31 2 IN105610 -5000.01 2/29/2020


 
Upvote 0 Downvote
Nope, your query fails for me with an "Error in Expression: DM .AmountMatched" message. Which doesn't surprise me since there isn't an "AmountMatched" field in the DebtorMatchesOnRec View you posted. You can't use a field in a SELECT that doesn't exist in the table / view.
Do you see an AmountMatched field if you issue the following:
Code: 
SELECT * FROM DebtorMatchesOnRec

Mirtheil
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ianbtheog
  • Locked
  • Question
Error Code 7009: Data is unavailable when using DTO
Replies
3
Views
533
mirtheil
mirtheil
Doug Barger
  • Locked
  • Question
soundex error
Replies
1
Views
348
mirtheil
mirtheil
Papagovas
  • Locked
  • Question
Expression evaluation error 1
Replies
4
Views
586
Papagovas
Papagovas
soocallmebrady
  • Locked
  • Question
Pervasive SQL Update with Joins - Error: Unknown table or correlation name
Replies
0
Views
250
soocallmebrady
soocallmebrady
JRCharlie
  • Locked
  • Question
ERROR in Assigment 1
Replies
2
Views
291
JRCharlie
JRCharlie

Part and Inventory Search

Sponsor

Back
Top