AW: SQL0338 Error using CRTSQLRPGI Command on V7R1 System

According to the SQL Reference 7.1 Appendix I are both VALUE and VALUES
reserved words.
VALUE can be used as soon as it is embedded within double quotes "VALUE".

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jim Oberholtzer
Gesendet: Wednesday, 11. May 2011 14:41
An: Midrange Systems Technical Discussion
Betreff: 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.