RE: Left Join Problem

Check if values from the right rows are null before you use them.

If they are null then there were no values. Int columns will return zero
if there is no data... but that could also be a valid value.

I don't know of another way to check that... one may exist, but I just
check for null(s).

Dan Feather
Silhouette/PinPoint R&D Programmer
Jack Henry & Associates, Inc.
dfeather@xxxxxxxxxxxxx
-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of RPower@xxxxxxxxxx
Sent: Friday, June 10, 2005 11:43 AM
To: Java Programming on and around the iSeries / AS400
Subject: Left Join Problem

If I'm doing a left join using JDBC how can I tell if I got only rows
from 
the left table and none from the right?  I'm getting a null pointer 
exception when I have only left table rows?  In the following code, 
WCCIVC# is part of the right table, and there are no rows returned for
it. 
 Thanks, code follows:

                        getProps =
                                conn.prepareStatement(
                                        "SELECT A.CGSTREET#, A.CGSTNAME,

B.WCCIVIC# FROM "
                                                + collectionName
                                                + 
dmd.getCatalogSeparator()
                                                + areaTable
                                                + " AS A LEFT JOIN "
                                                + collectionName
                                                + 
dmd.getCatalogSeparator()
                                                + custTable
                                                + " AS B ON A.CGSTREET#
= 
B.WCSTREET# ORDER BY CGSTREET#, WCCIVIC#");
                        rs = getProps.executeQuery();
                        Vector vStreet = new Vector();
                        Vector vCivic = new Vector();
                        vStreet.clear();
                        vCivic.clear();
                        int tempStreet = 0;
                        String tempStName = new String();
                        while (rs.next()) {
                                if (tempStreet == 0) {
                                        tempStreet =
rs.getInt("CGSTREET#"
);
                                        tempStName = rs.getString(
"CGSTNAME");
                                }
                                if (tempStreet !=
rs.getInt("CGSTREET#")) 
{
                                        vCivic.trimToSize();
                                        StreetInfo st =
                                                new
StreetInfo(tempStreet, 
tempStName, vCivic);
                                        vStreet.addElement(st);
                                        tempStreet =
rs.getInt("CGSTREET#"
);
                                        tempStName = rs.getString(
"CGSTNAME");
                                        vCivic = new Vector();
                                        vCivic.clear();
                                }
                                Integer c =
                                        new Integer(
 Integer.parseInt(rs.getString("WCCIVIC#").trim()));
                                if (!vCivic.contains(c)) {
                                        vCivic.addElement(c);
                                }
                        }

Ron Power
Programmer
Information Services
City Of St. John's, NL
P.O. Box 908
St. John's, NL
A1C 5M2
Tel: 709-576-8132
Email: rpower@xxxxxxxxxx
Website: http://www.stjohns.ca/
________________________________________________________________________
___
Success is going from failure to failure without a loss of enthusiasm. -

Sir Winston Churchill