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.