RE: SQL0338 Error using CRTSQLRPGI Command on V7R1 System

["Mant, Phil" <pmant@xxxxxxxx>]

Thanks Sam, I changed the code to this and now it works!
 
 
C/EXEC SQL                                                        
C+    DECLARE INPUT   CURSOR FOR                                  
C+    SELECT                                                      
C+    PNUM60, PDES35, PTYP35, STRC60, SPHY60, SCSU60,             
C+    (SPHY60 * SCSU60) AS VALUE, USTP60, USTY60                  
C+    FROM INP60 LEFT JOIN INP20 ON CONO60 = CONO20               
C+               AND  STRC60 = STRC20                             
C+               LEFT OUTER JOIN INP35 ON                         
C+               CONO60 = CONO35 AND PNUM60 = PNUM35              
C+               WHERE CONO60 = :CONO  AND SPHY60 != 0            
C+               AND INSV20 = '1'                                 
C+    ORDER BY PNUM60, STRC60                                     
C+    FOR FETCH ONLY                                              
C/END-EXEC     
 
Phil.                                                   

________________________________

From: midrange-l-bounces@xxxxxxxxxxxx on behalf of Sam_L
Sent: Tue 5/10/2011 5:37 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL0338 Error using CRTSQLRPGI Command on V7R1 System



Now I've signed on to a machine I can see what SQL0338 is:

Cause . . . . . :   The JOIN predicate is not valid because a column is
specified that exists in a table that is outside the scope of the join
predicate.

You have 3 files specified, joining via a combination of WHERE and JOIN
ON.  I'd try getting rid of the WHERE joining logic and use JOIN ON for
both files:

FROM INP60 left join INP20 on field1 = field2
LEFT OUTER JOIN INP35 ON CONO60 = CONO35 AND PNUM60 = PNUM35

Sam

On 5/10/2011 7:44 PM, Sam_L wrote:
> 1) It would be nice if you told us what the error was.
> 2) Have you tried the SELECT statement in STRSQL or some other client
> and does it work?
> 3) The line
> AND CONO60 = CONO20 AND STRC60 = STRC20 AND INSV20 =
> seems to be incomplete.
> 4) It is possible that "Value" is a reserved word.
>
> Sam
>
> On 5/10/2011 7:27 PM, Mant, Phil wrote:
> > I am getting an SQL precompile failure when I compile the following
> > code. Can someone please advise me how I should revise the code to
> > correct the issue.
> >
> >
> >
> > C/EXEC SQL
> >
> > C+ DECLARE INPUT CURSOR FOR
> >
> > C+ SELECT
> >
> > C+ PNUM60, PDES35, PTYP35, STRC60, SPHY60, SCSU60,
> >
> > C+ (SPHY60 * SCSU60) AS VALUE, USTP60, USTY60
> >
> >
> > C+ FROM INP60, INP20 LEFT OUTER JOIN INP35 ON
> >
> > C+ CONO60 = CONO35 AND PNUM60 = PNUM35
> >
> > C+ WHERE CONO60 = :CONO AND SPHY60 != 0
> >
> > C+ AND CONO60 = CONO20 AND STRC60 = STRC20 AND INSV20 =
> >
> > C+ ORDER BY PNUM60, STRC60
> >
> > C+ FOR FETCH ONLY
> >
> > C/END-EXEC
> >
> >
> >
> > Thanks,
> >
> > Phil.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.