Posted: Sat May 17, 2008 2:21 pm Post subject: Outer Join
I have three tables to join one of them involves outer join with another. I am hibernate and spring user. We use DB2 on z/OS. I have the following SQL.
SELECT DISTINCT FORM.FORNAME,
FORM.FORPKGNM,
FORM.FORPKGUM,
FORM.FOODMH,
FORM.FOUDIND,
ADMKEY
FROM IDVPCS01.ITDRUGS DRUG
JOIN IDVPCS01.ITFORM FORM ON DRUG.DMUMNEM = FORM.FORMNEM
LEFT OUTER JOIN (SELECT ADMKEY, HOSPNO, WARDNO
FROM IDVPCS01.ITADMINV WHERE HOSPNO = '0455' AND WARDNO = 'A') TEMP
ON DRUG.DMADMKEY = ADMKEY
WHERE FORM.FORNAME LIKE 'TYL%'
My question to fellow members here is,
Is there anyway in DB2 to form the join queries with out using ON reserverd word in SQL. The clause "ON" not accptable in hibernate.
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
Posted: Mon May 19, 2008 2:53 pm Post subject:
As far as I know, DB2 requires the 'ON' syntax for outer joins. To work around that, I suggest that you code a stored procedure on the z/OS side that will accept the raw SQL as one of the parms. The stored proc would then PREPARE the SQL and OPEN a cursor on it to return the results set. Then you don't have to worry about the SQL dialect differences.
I'll mention this next alternative, but I don't recommend it. You could split the single SELECT into 2 separate SELECTs coupled with a UNION. The first SELECT you would code with an inner join to the TEMP table. The second SELECT, you would code with a not exists for the TEMP table. The rows returned by each SELECT would be mutually exclusive, and you would wind up with the same results as the outer join. I only mention this if you need something immediately and you're backed into a corner with no other options.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
