Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Using Left Outer Join in a Dynamic SQL Query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
mushreyas

New User


Joined: 18 Jul 2008
Posts: 59
Location: Bangalore

PostPosted: Fri May 25, 2012 9:53 pm    Post subject: Using Left Outer Join in a Dynamic SQL Query
Reply with quote

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.

Code:

SELECT A.CLIENT_ID, A.CLIENT_REF, B.GROUP_REF
FROM CLIENT_ACCOUNT A
LEFT OUTER JOIN CLIENT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID AND
       B.SOFT_DEL_IN = 'N')
WHERE A.SOFT_DEL_IN = 'N'
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri May 25, 2012 10:52 pm    Post subject:
Reply with quote

have you tried changing this:
Code:
ON (A.GROUP_ID = B.GROUP_ID AND
       B.SOFT_DEL_IN = 'N')
WHERE A.SOFT_DEL_IN = 'N'


to this:
Code:

ON  A.GROUP_ID    = B.GROUP_ID
AND A.SOFT_DEL_IN = B.SOFT_DEL_IN
WHERE A.SOFT_DEL_IN = 'N'
Back to top
View user's profile Send private message
mushreyas

New User


Joined: 18 Jul 2008
Posts: 59
Location: Bangalore

PostPosted: Sat May 26, 2012 9:00 am    Post subject: Reply to: Using Left Outer Join in a Dynamic SQL Query
Reply with quote

No I haven't tried. But am confused how does that make a difference.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat May 26, 2012 3:53 pm    Post subject:
Reply with quote

well i am not going to explain it,
so you can ignore my post.
Back to top
View user's profile Send private message
mushreyas

New User


Joined: 18 Jul 2008
Posts: 59
Location: Bangalore

PostPosted: Sat May 26, 2012 7:30 pm    Post subject: Reply to: Using Left Outer Join in a Dynamic SQL Query
Reply with quote

I will try with the query you suggested... But will have to wait till monday.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Sat May 26, 2012 8:22 pm    Post subject:
Reply with quote

First DB2 Filters(where) then joins(on)

Regards,
Sushanth
Back to top
View user's profile Send private message
mushreyas

New User


Joined: 18 Jul 2008
Posts: 59
Location: Bangalore

PostPosted: Sun May 27, 2012 7:05 pm    Post subject: Reply to: Using Left Outer Join in a Dynamic SQL Query
Reply with quote

Thanks Sushanth for clarifying. But how will that help in resolving +354 error.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun May 27, 2012 8:15 pm    Post subject: Re: Reply to: Using Left Outer Join in a Dynamic SQL Query
Reply with quote

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...........
Back to top
View user's profile Send private message
mushreyas

New User


Joined: 18 Jul 2008
Posts: 59
Location: Bangalore

PostPosted: Mon May 28, 2012 9:54 am    Post subject: Reply to: Using Left Outer Join in a Dynamic SQL Query
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon May 28, 2012 10:24 am    Post subject:
Reply with quote

maybe 3.2.13 COALESCE instead of null indicators
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue May 29, 2012 12:49 pm    Post subject:
Reply with quote

+354 doesn't tell you much, I suggest you do as IBM tells you :
Quote:
+354
A ROWSET FETCH STATEMENT MAY HAVE RETURNED ONE OR MORE ROWS OF DATA. HOWEVER,
ONE OR MORE WARNING CONDITIONS WERE ALSO ENCOUNTERED. USE THE GET DIAGNOSTICS
STATEMENT FOR MORE INFORMATION REGARDING THE CONDITIONS
THAT WERE ENCOUNTERED
Back to top
View user's profile Send private message
mushreyas

New User


Joined: 18 Jul 2008
Posts: 59
Location: Bangalore

PostPosted: Fri Jun 01, 2012 2:25 pm    Post subject: Reply to: Using Left Outer Join in a Dynamic SQL Query
Reply with quote

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.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Fri Jun 01, 2012 2:31 pm    Post subject: Reply to: Using Left Outer Join in a Dynamic SQL Query
Reply with quote

It has been suggested to you, twice, that you use GET DIAGNOSTICS. What has that told you for any problems you have encountered?
Back to top
View user's profile Send private message
mushreyas

New User


Joined: 18 Jul 2008
Posts: 59
Location: Bangalore

PostPosted: Tue Jun 05, 2012 10:22 am    Post subject: Reply to: Using Left Outer Join in a Dynamic SQL Query
Reply with quote

Thanks all. GET DIAGNOSTICS helped me in finding the error which is now fixed.
Back to top
View user's profile Send private message
Santhosh Menon

New User


Joined: 19 Jun 2012
Posts: 4
Location: USA

PostPosted: Wed Jun 20, 2012 6:52 am    Post subject:
Reply with quote

Just curious... What was the error ?
Back to top
View user's profile Send private message
mushreyas

New User


Joined: 18 Jul 2008
Posts: 59
Location: Bangalore

PostPosted: Thu Jun 21, 2012 8:26 pm    Post subject: Reply to: Using Left Outer Join in a Dynamic SQL Query
Reply with quote

I missed a null indicator check for one of the column being retrieved...icon_sad.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us