Re: SQL Select and sum using RPG Program Variables

[Peter.Colpaert@xxxxxxxxxx]

Kevin,

The error indicates that you haven't specified any host variables in which 
to store the SUM's.

Try changing as follows:

C/EXEC SQL 
 
C+ SELECT SUM(ILQTY) AS "SQLQT1", SUM(ILQTY * ILNET)
AS "SQLSL1" into :sqlqt1, :SQLSL1 
C+ FROM SIH, SIL 
 
C+ WHERE (SIINVN = ILINVN) and (SICUST = :CUSNBR) and
(ILPROD = :ITMNBR)
C+       and (SIINVD BETWEEN 20050701 and 20050731) 
 
C/END-EXEC 

(I have done this one from memory, but it should work).

HTH,

Peter Colpaert
Application Developer
Massive - Kontich, Belgium
-----
Yoda of Borg are we.  Futile is resistance, assimilated will you be.
-----




Kevin Elliott <kmindy@xxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
24/08/2005 15:39
Please respond to RPG programming on the AS400 / iSeries

 
        To:     rpg400-l@xxxxxxxxxxxx
        cc: 
        Subject:        SQL Select and sum using RPG Program Variables


All:

I am writing a quickie program to correct monthly
sales totals. I need to use SQL to perform the 
select & sum of the monthly total from a Sales Invoice
Header & Detail file. When I compile
the source, I get the error message: Position 9 INTO
clause missing from embedded statement.

My embedded SQL:
C/EXEC SQL 
 
C+ SELECT SUM(ILQTY) AS "SQLQT1", SUM(ILQTY * ILNET)
AS "SQLSL1" 
C+ FROM SIH, SIL 
 
C+ WHERE (SIINVN = ILINVN) and (SICUST = :CUSNBR) and
(ILPROD = :ITMNBR)
C+       and (SIINVD BETWEEN 20050701 and 20050731) 
 
C/END-EXEC 
 

I am also unsure if I need to specify the SQLQT1 and
SQLSL1 in quotes as listed,
or use :SQLQT1 and :SQLSL1 as is normal per RPG
defined variables in embedded SQL.

Thanks in Advance

Kevin Elliott
Business Analyst
Gilchrist & Soames