Re: SQL5001 error -- JAVA400-L

Ok.  Thanks to you and Jim.
Is there a way to select only distinct rows from the right table?  I tried 
using DISTINCT but it seems it only worked on the left table.  Do I need 
the DISTINCT in there twice?  Basically, what I'm looking for is every row 
in the left table, matched to distinct rows in the right table, which may 
have more than one match coming from the right table, but I only want 
those with distinct civic numbers.

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




NGay@xxxxxxxxxxxxx 
Sent by: java400-l-bounces@xxxxxxxxxxxx
25/05/2005 11:21 AM
Please respond to
Java Programming on and around the iSeries / AS400 
<java400-l@xxxxxxxxxxxx>


To
Java Programming on and around the iSeries / AS400 
<java400-l@xxxxxxxxxxxx>
cc

Subject
Re: SQL5001 error







Ron,

Because you've specified the library name as part of your table (
PUBWRK.CGARAREA), this is then the way SQL expects you to refer to it.  So
you cannot qualify field names like CGARAREA.CGSTREET#, you must put
PUBWRK.CGARAREA.CGSTREET#.

Alternatively you can alias each table, along the lines of
SELECT * FROM PUBWRK.CGARAREA as A
LEFT OUTER JOIN PUBWRK.WCUSTOMER as B
ON A.CGSTREET# = B.WCSTREET#

which makes it look a little neater.

HTH,

Nigel Gay,
Computer Patent Annuities.



|---------+------------------------------>
|         |           RPower@xxxxxxxxxx  |
|         |           Sent by:           |
|         |           java400-l-bounces@m|
|         |           idrange.com        |
|         |                              |
|         |                              |
|         |           25/05/2005 07:36 AM|
|         |           Please respond to  |
|         |           Java Programming on|
|         |           and around the     |
|         |           iSeries / AS400    |
|         |                              |
|---------+------------------------------>
 
>--------------------------------------------------------------------------------------------------------------|
  |                                       |
  |       To:       "Java Programming on and around the iSeries / AS400" 
<java400-l@xxxxxxxxxxxx>                |
  |       cc:                                       |
  |       Subject:  SQL5001 error                                       |
 
>--------------------------------------------------------------------------------------------------------------|




I'm using this statement in my java program and I'm getting a SQL5001
error.  Here's the statement:
getProps = conn.prepareStatement("SELECT * FROM PUBWRK.CGARAREA LEFT OUTER
JOIN PUBWRK.WCUSTOMER ON CGARAREA.CGSTREET# = WCUSTOMER.WCSTREET#");
The error I get is :java.sql.SQLException: [SQL5001] Column qualifier or
table CGARAREA undefined..
What am I doing wrong?  This is my first attempt at a JOIN SQL statement.

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
--
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.








********************************************************************************
The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee; access to this
email by anyone else is unauthorised.

If you are not the intended recipient: (1) you are kindly requested
to return a copy of this message to the sender indicating that you
have received it in error, and to destroy the received copy; and (2)
any disclosure or distribution of this message, as well as any action
taken or omitted to be taken in reliance on its content, is prohibited
and may be unlawful.
********************************************************************************