tags:

views:

7192

answers:

2

I have a main table that I must get data from. I have a left outer join where the fields will match 40% of the time. And then I have another join where I need to match the data from table A with.

This is the SQL in pseudo code. This query won't work.

-- This is the part I want to do but doesn't work. AND H.COL3 = A.STATE????

I am working with IBM DB2.

SELECT DISTINCT
  APP_NO as app_no,
  A.STATE as state
  ...
  ... Fields
  ...
FROM 
  TABLE_A A
LEFT OUTER JOIN  
  TABLE_B HIST
ON
  HIST.COL1 = A.COL1
, TABLE_C B  
LEFT OUTER JOIN
  TABLE_D H  
ON
  H.COL2 = B.COL2
-- This is the part I want to do but doesn't work.
AND
  H.COL3 = A.STATE????
WHERE
  A.BRANCH = 'Data'
A: 

What happens if you put the "AND H.COL3 = A.STATE" in your WHERE clause?

Murat Ayfer
In DB2, I get the following error. If I remove the offending line, then it works. Error: SQL0338N An ON clause associated with a JOIN operator or in a MERGE statement is not valid. SQLSTATE=42972 (State:42972, Native Code: FFFFFEAE)If I put it anywhere, I get this error.
Berlin Brown
Is H.COL3 in the SELECT field list? Some databases are fussy like that.
staticsan
+3  A: 

I think you could re-write it like this (but I could be reading your statement wrong)

FROM 
  TABLE_A A LEFT OUTER JOIN TABLE_B HIST ON
      HIST.COL1 = A.COL1
  LEFT OUTER JOIN TABLE_D H ON 
      H.COL3 = A.STATE
  LEFT OUTER JOIN TABLE_C B ON H.COL2 = B.COL2
WHERE
  A.BRANCH = 'Data'

Also, the IBM doco on this error states:

An ON clause associated with a JOIN operator or in a MERGE statement is not valid. Explanation:

Column references in an ON clause must only reference columns of tables that are in the scope of the ON clause.

So I could be mistaken, it just looks like the erronous ON clause when outer joining "H.COL3 = A.STATE" is out of scope of the On clause because the table A is not in that scope.

Mark
"So I could be mistaken, it just looks like the erronous ON clause when outer joining "H.COL3 = A.STATE" is out of scope of the On clause because the table A is not in that scope."That is probably right, but how would I add it in scope. Let me look at your example.
Berlin Brown
That code seems to work, but my result set doesn't return the correct results.I wish there was some way to just use the state 'alias' somehow:ON H.COL2 = B.COL2AND H.COL3 = stateI want to use that state that is returned for that particular row which comes from table 'A'
Berlin Brown
Why doesnt it return the correct results? From what I have read, you orig. query wants rows from table_d that dont HAVE to match from table_c (col2) and table_a (state). Which I think my query should produce.
Mark
My original query is probably wrong. You are doing good.Here is what I have in my database, in theory.Table_A is my main report query. 123, NY 123, TX I want to return rows where the State column A matches with the state column from Table_D but also preserving the join on Table C.
Berlin Brown
Ok just change the "left outer join", to an "inner join" on the table_d join in example: INNER JOIN TABLE_D H ON H.COL3 = A.STATE. That way you wil get macthing states all the time, and also rows that do not necessarily have matching rows between COL2 on tables C and D. Please correct me if im wrong
Mark
Also, my advice for solving this would be to built your query peice by peice. Just do one join at a time, adding in further joins to filter the result set. Take out the joins that are easy, and work out the tough stuff. Then add them all together once you have to down pat.
Mark
Thanks, it worked with the inner join. I thought I was speaking gibberish there for a second.
Berlin Brown
Thats good! I was wondering if what I said actually made sense too.
Mark
P.S. dont forget to mark as answer ;)
Mark