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 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: 6968
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: 6968
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: 6968
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: 6968
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: 1281
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: 7315

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
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 21 Sun Aug 27, 2017 10:35 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts REXX - Dynamic file Creation d_sarlie CLIST & REXX 7 Tue Jun 27, 2017 7:30 pm
No new posts Dynamic output file creation in cobol... smileheal COBOL Programming 7 Thu Jun 15, 2017 10:53 pm

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