Re: SQL0338 Error using CRTSQLRPGI Command on V7R1 System

[Jim Oberholtzer <midrangel@xxxxxxxxxx>]

I would still be worried about the variable "value".  Even if it is not 
a reserved word, "values" is; it's too close and could cause trouble 
later.  Why not call it what it is, there is a reason for the 
calculation, what does the result describe?

Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


On 5/11/2011 7:10 AM, Mant, Phil wrote:
> 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.