Left Outer join returning "Unknown Error -19"
Left Outer join returning "Unknown Error -19"
(OP)
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:
To avoid confusion, I have named my tables in the same manner. And this is the expression I wrote:
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]
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
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:
CODE --> 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:
CODE --> 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
[/link]FROM Emp e
LEFT OUTER JOIN
(
SELECT * FROM Dept
) d
ON e.Ref = d.MatchRef
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
RE: Left Outer join returning "Unknown Error -19"
- 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
http://www.mirtheil.com
RE: Left Outer join returning "Unknown Error -19"
I still got an error when I run a simple
CODE
You might want to check the DEPT table and make sure that it has an AMT field and you're using the
Mirtheil
http://www.mirtheil.com
RE: Left Outer join returning "Unknown Error -19"
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)
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
RE: Left Outer join returning "Unknown Error -19"
So:
CODE
CODE
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
Mirtheil
http://www.mirtheil.com
RE: Left Outer join returning "Unknown Error -19"
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
RE: Left Outer join returning "Unknown Error -19"
Do you see an AmountMatched field if you issue the following:
CODE
Mirtheil
http://www.mirtheil.com