473,241 Members | 1,441 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

DB2 v5r3 FULL Join?

Lee
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

Nov 12 '05 #1
2 5050

"Lee" <gr*******@hotmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...
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

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


Nov 12 '05 #2
Lee
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

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Dave | last post by:
Hi I have the following 4 tables and I need to do a fully outerjoin on them. create table A (a number, b number, c char(10), primary key (a,b)) create table B (a number, b number, c ...
0
by: reneecccwest | last post by:
SELECT d.code, d.description, v.code AS divCode, v.descripton AS divDescript, b.code AS brhCode, b.description AS brhDescript FROM Department d FULL OUTER JOIN Division v
7
by: alexcn | last post by:
I have the following query: SELECT dbo.tSymExch.exCode, dbo.tSymGrp.sgCode, dbo.tSymMain.smCode FROM dbo.tSymExch FULL OUTER JOIN dbo.tSymGrp ON dbo.tSymExch.exID =...
2
by: Pascal PEYRE | last post by:
hi, I have an 820 iseries on V5R1. I use DB2 connect V7.2 on a linux server. This week I must migrate the 820 iseries toward an 620 i5 on V5R3. I would to know if my DB2 connect V7.2 on linux...
2
by: Cory | last post by:
i have two tables, Event1 and Event2. They both have fields called contactID and eventID in them. Some of the contactID's between the tables are equal and some are not. I want to do a full outer...
1
by: Martijn van Oosterhout | last post by:
Today I got the error: ERROR: FULL JOIN is only supported with mergejoinable join conditions Which is really annoying since a full join is exactly what I wanted. I guess the alternative is to...
1
by: tom.eeraerts | last post by:
Hello, I have a problem migrating an application from v5r2 to v5r3. The problem is with the prepared statements. To see what the problem is, i extracted a small piece of code and debugged the...
3
by: kellyj00 | last post by:
I am trying to create a incrementally updatable Materialized Query Table on our as400 v5r3 per examples found on the internet. Shown below. create tABLE SQLLIB.INVMAST_MQT AS (SELECT * FROM...
3
by: Smita Kashyap | last post by:
What is the main difference between Full Join and Inner Join in SQl Server 2005 ? As inner join retrive all records that match certain condition, similiary in full join will rreturn all records...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.