IBM Mainframe Forum

SELECT A.A1
,A.A2
,B.B1
,B.B2
FROM
(SELECT X.A1
,Y.A2
FROM TABLE1 X
,TABLE2 Y
WHERE X.A3=Y.A4)
AS A
LEFT OUTER JOIN
TABLE3 B
ON B.B2=A.A2
Here A1, A2 and B2 are NULLable columns and B1 in NOT NULL

Resultant Data Set
A1 A2 B1 B2
1 2 1 2
2 10 5 10
3 12 -- --
7 11 -- --

However due to left outer join...For non matching rows from left side table i.e. TABLE1 and TABLE2, its making values of B1 and B2 as NULL (Though B1 is defined as NOT NULL)

While fetching its giving me -305 for column B1

I tried applying NULL indicator to column B1...Gave me Sql Code -303...Incomparable data items.

Please help
suhaas02

This should completely explain your problem and indicate the solution.

Read the question properly...Don't take it too easily...

check your code properly
-305 has nothing to do with outer join or nulls.
check column definitions ,hostvariable declarations and number of columns in open cursor vs fetch cursor.

I already checked all the possibilities...Please think again...

I don't have to think again, my left joins and fetches (almost identical to the ones you posted) are working perfectly.
-303 is very clear in it's explanation.
There are two possibilities : or IBM DB2 is wrong or your code is.
Maybe if you post your declarations, your open cursor and your fetch , there's a slim chance I might believe you .

GuyC wrote:-305 has nothing to do with outer join or nulls.

should have said :
-303 has nothing to do with outer join or nulls.

Ok Boss...Dnt be angry...Just tell me do you agree...There can be NULLs in resultant data set for LEFT OUTER JOIN...(i.e. for columns from RIGHT side table)...If yes, my question how to handle it for a column which is defined as NOT NULL
Waiting

suhaas02 wrote:I tried applying NULL indicator to column B1...Gave me Sql Code -303...Incomparable data items.

show me what you tried, because "applying NULL indicator" should not result in -303

PS.:
I'm not your Boss,
I'm not angry and
wether you are "waiting" or doing your job while "waiting" is none of my concern

Okie...GuyC...Thank you for your help...