SQL Code -305 in JOIN Query



IBM's flagship relational database management system

SQL Code -305 in JOIN Query

Postby suhaas02 » Wed Nov 09, 2011 9:04 pm

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
suhaas02
 
Posts: 5
Joined: Wed Nov 09, 2011 8:26 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SQL Code -305 in JOIN Query

 

Re: SQL Code -305 in JOIN Query

Postby Akatsukami » Wed Nov 09, 2011 9:49 pm

This should completely explain your problem and indicate the solution.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: SQL Code -305 in JOIN Query

Postby suhaas02 » Thu Nov 10, 2011 12:05 pm

Read the question properly...Don't take it too easily...
suhaas02
 
Posts: 5
Joined: Wed Nov 09, 2011 8:26 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SQL Code -305 in JOIN Query

Postby GuyC » Thu Nov 10, 2011 2:13 pm

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 can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: SQL Code -305 in JOIN Query

Postby suhaas02 » Thu Nov 10, 2011 2:17 pm

I already checked all the possibilities...Please think again...
suhaas02
 
Posts: 5
Joined: Wed Nov 09, 2011 8:26 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SQL Code -305 in JOIN Query

Postby GuyC » Thu Nov 10, 2011 2:45 pm

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 .
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: SQL Code -305 in JOIN Query

Postby GuyC » Thu Nov 10, 2011 2:51 pm

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.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: SQL Code -305 in JOIN Query

Postby suhaas02 » Thu Nov 10, 2011 3:17 pm

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
 
Posts: 5
Joined: Wed Nov 09, 2011 8:26 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SQL Code -305 in JOIN Query

Postby GuyC » Thu Nov 10, 2011 3:26 pm

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
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: SQL Code -305 in JOIN Query

Postby suhaas02 » Thu Nov 10, 2011 3:33 pm

Okie...GuyC...Thank you for your help...
suhaas02
 
Posts: 5
Joined: Wed Nov 09, 2011 8:26 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post