RE: SQL0338 Error using CRTSQLRPGI Command on V7R1 System

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

The value is a stock value. I will change the variable name to something
more meaningful. 

Thanks,
Phil.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Oberholtzer
Sent: Wednesday, May 11, 2011 5:41 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL0338 Error using CRTSQLRPGI Command on V7R1 System

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.
--
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.