DB2 v5r3 FULL Join?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lee - 22 Feb 2005 17:14 GMT

I have two identical schema tables (one in a production library and
another in a development library) and I need to be able to easily
compare the data in them for errors and omissions. With another
relational database (can you guess which?) I was able to do the
following:

SELECT * FROM tableA A FULL OUTER JOIN tableB B ON A.KEY=B.KEY
WHERE A.KEY IS NULL OR B.KEY IS NULL OR A.DATA<>B.DATA

I am trying to do the same thing on DB2 v5r3 and I am unable to find
anything that can give me the results I need - can anyone point me to a
reference which states what the valid JOIN syntax is or post an example
of a full outer join?
Thanks in advance,
Lee

Reply to this Message

Rhino - 23 Feb 2005 04:58 GMT

> I have two identical schema tables (one in a production library and
> another in a development library) and I need to be able to easily
[quoted text clipped - 11 lines]
> Thanks in advance,
> Lee

I assume that you mean DB2 V5R3 on AS/400? You should be able to find the
V5R3 manuals somewhere on the IBM website - http://ibm.com - via the search
engine on the home page. Those manuals should include an SQL Reference that
includes examples of join syntax for that version.

Unfortunately for you, most of the people who post to this newsgroup - and
the IBM staff who monitor it - are working with DB2 for Linux, Unix, and
Windows and may not feel qualified to talk about AS/400 issues. There are
also some mainframe users from time to time but AS/400 people don't drop in
here very often so there's not a lot of expertise.

I had thought that by now the SQL on the various DB2 platforms would be
pretty much the same except for whatever differences might be necessitated
by the differences in the operating systems. Frankly, the SQL in your post
looks fine to me; I expect it would work on Linux/Unix/Windows and the
mainframe if you tried it there.

Are you getting an error or are you just not getting the result you want? If
you are getting an error can you post it? Maybe someone here can figure out
what's wrong from that. If you are simply not getting the right result but
no error, could you describe what you are getting versus what you expected?
Maybe someone here can see what is wrong then.

Rhino

Reply to this Message

Lee - 23 Feb 2005 14:32 GMT

Thanks Rhino, yes, I mean't to say I am working on an AS/400 - I'll
look again for the manuals.

> Are you getting an error or are you just not getting the result you want? If
> you are getting an error can you post it?

In the SQL clients I use (STRSQL in green-screen and the "Run SQL
Scripts" GUI) I get the following error message when attempting the
FULL OUTER JOIN clause above:

[SQL0104] Token FULL was not valid. Valid tokens: FOR WITH FETCH ORDER
UNION OPTIMIZE. Cause . . . . . : A syntax error was detected at
token FULL. Token FULL is not a valid token. A partial list of valid
tokens is FOR WITH FETCH ORDER UNION OPTIMIZE. This list assumes that
the statement is correct up to the token. The error may be earlier in
the statement, but the syntax of the statement appears to be valid up
to this point. Recovery . . . : Do one or more of the following and
try the request again: -- Verify the SQL statement in the area of the
token FULL. Correct the statement. The error could be a missing comma
or quotation mark, it could be a misspelled word, or it could be
related to the order of clauses. -- If the error token is
<END-OF-STATEMENT>, correct the SQL statement because it does not end
with a valid clause.

After removing the word 'FULL', I get similar messages about the
keyword 'OUTER'. And then when I remove both of them, all I get is an
INNER join - not the results I am looking for.

If I get a resolution I'll post it here for posterity.
Regards,
Lee

Reply to this Message