Left Outer join returning "Unknown Error -19"

[michellepace]

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:

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:

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

 

[mirtheil]

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

http://www.mirtheil.com

 

[mirtheil]

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

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

http://www.mirtheil.com

 

[michellepace]

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)

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

 

[mirtheil]

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:

SELECT Emp.Ref, Emp.amt, Dept.amt FROM Emp LEFT OUTER JOIN Dept ON Emp.Ref = Dept.MatchRef

Should be:

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:

SELECT Emp.Ref, Emp.amount EmpAMT, Dept.amt DeptAMT FROM DebtorReceivables Emp LEFT OUTER JOIN DebtorMatchesOnRec dept ON Emp.Ref = Dept.MatchRef

Mirtheil

http://www.mirtheil.com

 

[michellepace]

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

 

[mirtheil]

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:

SELECT * FROM DebtorMatchesOnRec

Mirtheil

http://www.mirtheil.com