RE: SQL0338 Error using CRTSQLRPGI Command on V7R1 System

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.