×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

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!
*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

Left Outer join returning "Unknown Error -19"

thread318-1805967

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:

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]

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"

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

RE: Left Outer join returning "Unknown Error -19"

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
http://www.mirtheil.com

RE: Left Outer join returning "Unknown Error -19"

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

RE: Left Outer join returning "Unknown Error -19"

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
http://www.mirtheil.com

RE: Left Outer join returning "Unknown Error -19"

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


RE: Left Outer join returning "Unknown Error -19"

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
http://www.mirtheil.com

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! Already a Member? Login
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