DB2 v5r3 FULL Join? - DB2 Database

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

Subscribe Reply

5088

Rhino

"Lee" <gr*******@hotm ail.com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.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

Similar topics

11695

full outer join question

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 char(10), primary key (a,b)) create table C (a number, b number, c char(10), priamry key (a,b)) create table D (a number, b number, c char(10), priamry key (a,b)) In oracle 9i, the following query returns correct results set, if I were to

Oracle Database

5160

FULL OUTER JOIN

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

Microsoft SQL Server

6234

SQL Full Outer Join

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 = dbo.tSymGrp.sgexID FULL OUTER JOIN dbo.tSymMain ON dbo.tSymGrp.sgID = dbo.tSymMain.smsgID Which produces:

Microsoft SQL Server

2369

db2 connect 7.2 on Linux with OS400 V5R3

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 will be compatible with V5R3 ???? Thanks for your help. Pascal.

DB2 Database

7469

full outer join on two tables

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 join on contactID so i can get all the contactID's in the query. i know access doesn't support full outer joins so there must be a way to do it without using one. how do you do it in access 2000?

Microsoft Access / VBA

3542

FULL JOIN and mergjoinable conditions...

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 do a left join and a right join and merge them? Is it just that no-one has come up with a way to code this efficiently? Maybe someone has a better way to express this. The problem is I have

PostgreSQL Database

2698

iSeries: JDBC problem v5r3

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 application while running it on my local window machine (IBM WSAD) but still connecting to the AS400. For the local debugging I use "com.ibm.as400.access.AS400JDBCDriver"

DB2 Database

4063

Creating MQT's on v5r3

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 SQLLIB.INVMAST) DATA INITIALLY DEFERRED REFRESH DEFERRED; CREATE TABLE INVMAST_MQTS FOR SQLLIB.INVMAST_MQT PROPOGATE IMMEDIATE;

DB2 Database

22562

Difference between Full Join and Inner Join in SQl Server 2005

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 only where they match. Also sugest me which one is optimal in Inner Join and Where clause.

Microsoft SQL Server

8830

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

9370

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9247

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

8242

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6796

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6074

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4874

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3312

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2215

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General