Using Left Outer Join in a Dynamic SQL Query

mushreyas · New User Joined: 18 Jul 2008 Posts: 59 Location: Bangalore

Hi,

Am using a Left OUTER JOIN in a dynamic SQL query and the problem is that when the data is not present in the 2nd table am getting SQLCODE +354. Have tried using null indicator wherein all the column values are not retrieved even though they are present. However when the same dynamic query works when the data is present in both the tables. Am unable to rectify this problem. Need your help on this.

dbzTHEdinosauer · Posted: Fri May 25, 2012 10:52 pm

have you tried changing this:

mushreyas · New User Joined: 18 Jul 2008 Posts: 59 Location: Bangalore

No I haven't tried. But am confused how does that make a difference.

dbzTHEdinosauer · Posted: Sat May 26, 2012 3:53 pm

well i am not going to explain it,
so you can ignore my post.

mushreyas · New User Joined: 18 Jul 2008 Posts: 59 Location: Bangalore

I will try with the query you suggested... But will have to wait till monday.

sushanth bobby · Posted: Sat May 26, 2012 8:22 pm

First DB2 Filters(where) then joins(on)

Regards,
Sushanth

mushreyas · New User Joined: 18 Jul 2008 Posts: 59 Location: Bangalore

Thanks Sushanth for clarifying. But how will that help in resolving +354 error.

dbzTHEdinosauer · Posted: Sun May 27, 2012 8:15 pm

why don't you tell us what the +354 indicates?

besides, do you really want a LEFT outer join
as apposed to a simple JOIN?

LEFT OJ will always return all rows from the 'left table',
whether there are matches or not.

JOIN will only return rows that match.

had you bothered to GET DIAGNOSTICS,
you could have answered your own question...........

mushreyas · New User Joined: 18 Jul 2008 Posts: 59 Location: Bangalore

Yes i need all the rows from left table irrespective of records in right table. But when the matching record is present in right table it should retrieve the selected value or else null value.

When i run the same query in SPUFI it works fine returning null/blank values for the non-matching rows in right table.

Its like a client maybe a part of the group or not but when he is part of the group i need to retrieve the corresponding group reference.

dbzTHEdinosauer · Posted: Mon May 28, 2012 10:24 am

maybe 3.2.13 COALESCE instead of null indicators

GuyC · Posted: Tue May 29, 2012 12:49 pm

+354 doesn't tell you much, I suggest you do as IBM tells you :

mushreyas · New User Joined: 18 Jul 2008 Posts: 59 Location: Bangalore

I tried using COALESCE statment, am getting -206 in PREPARE.

Also the problem is occuring when trying to retrieve the column values of numeric types.

Bill Woodger · Posted: Fri Jun 01, 2012 2:31 pm

It has been suggested to you, twice, that you use GET DIAGNOSTICS. What has that told you for any problems you have encountered?

mushreyas · New User Joined: 18 Jul 2008 Posts: 59 Location: Bangalore

Thanks all. GET DIAGNOSTICS helped me in finding the error which is now fixed.

Santhosh Menon · New User Joined: 19 Jun 2012 Posts: 4 Location: USA

Just curious... What was the error ?

mushreyas · New User Joined: 18 Jul 2008 Posts: 59 Location: Bangalore

I missed a null indicator check for one of the column being retrieved...