View previous topic :: View next topic
|
Author |
Message |
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.
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
well i am not going to explain it,
so you can ignore my post. |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
First DB2 Filters(where) then joins(on)
Regards,
Sushanth |
|
Back to top |
|
|
mushreyas
New User
Joined: 18 Jul 2008 Posts: 59 Location: Bangalore
|
|
|
|
Thanks Sushanth for clarifying. But how will that help in resolving +354 error. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
+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 |
|
|
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. |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
Santhosh Menon
New User
Joined: 19 Jun 2012 Posts: 4 Location: USA
|
|
|
|
Just curious... What was the error ? |
|
Back to top |
|
|
mushreyas
New User
Joined: 18 Jul 2008 Posts: 59 Location: Bangalore
|
|
|
|
I missed a null indicator check for one of the column being retrieved... |
|
Back to top |
|
|
|