Home > IT Answers > AS/400 > SQL to query a file
rmb
115 pts.
 SQL to query a file
Open Query File, RPG, SQL, SQL Query
I want to use SQL to query a file and summurize data, using multiple scalars( min, max, sum) and display the results on a workstation screen.  Can i use one select statement in RPG iseries or should I create a stored procedure?   Any code samples,etc would be greatly appreciated.

 



Software/Hardware used:
i series- RPG
ASKED: September 27, 2011  4:31 PM
UPDATED: March 31, 2012  1:08 AM
RELATED QUESTIONS
Answer Wiki:
Here's some sample code i just put together quick using fake information. It just shows a generic way to embed SQL in an ILE RPG program: <pre> d SalesRec DS d Salesman 25A INZ(*blanks) d TotalSales 10S 2 INZ(0) d MaxSale 10S 2 INZ(0) d MinSale 10S 2 INZ(0) ************** c/FREE //Declare a cursor for an SQL Statement exec sql declare SalesTotals cursor for select SalesManName, sum(sales) as TotSales, max(sales) as MaxSale, min(sales) as MinSale FROM TestLib/SalesData group by SalesManName order by SalesManName; //Open the Cursor exec sql open SalesTotals; //Get the first record exec sql fetch SalesTotals into :SalesRec; //Process until sqlcode is not 0 dow (sqlcode =0); // Do whatever you have to do with the record //Get the next record exec sql fetch SalesTotals into :SalesRec; enddo; //Close the Cursor exec sql close SalesTotals; *inlr = *on; return; /END-FREE </pre> Hopefully that helps!
Last Wiki Answer Submitted:  September 27, 2011  7:27 pm  by  Dcantwell   465 pts.
All Answer Wiki Contributors:  Dcantwell   465 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Here is code in program. it blows up on the FROM statement in the SQL line.
Wow, thanks for your fast reply.

**File List
FREPFILW IF E K DISK
FREP025D CF E WORKSTN
** Data Structure for build
DHOSTVARS DS
D HVTOTORD 5S 0
D HVTOTBKS 7S 0
D HVTOT$AMT 9S 2
D HVRSLMIN 3S 0
D HVRLSMAX 3S 0
D HVRLSAV 3S 0
D HVSHPMIN 3S 0
D HVSHPMAX 3S 0
D HVSHPAV 3S 0
D HVTOTBKS 7S 0
D HVTOT$AMT 9S 2
D HVRSLMIN 3S 0
D HVRLSMAX 3S 0
D HVRLSAV 3S 0
D HVSHPMIN 3S 0
D HVSHPMAX 3S 0
D HVSHPAV 3S 0
C/EXEC SQL
C+ SELECT COUNT(INV1), SUM(TOT$), SUM(QTYBKS), MIN(DAYS1), MAX(DAYS1),
C+ AVG(DAYS1), MIN(DAYS2), MAX(DAYS), AVG(DAYS2) INTO : HOSTVARS
C+ FROM WFILES.REPFILW
C/END-EXEC
/End-Free
C DOW *IN03 <> ’1′
C EVAL #TOTORD = HVTOTORD
C EVAL #TOTBKS = HVTOTBKS
C EVAL #TOTAMT = HVTOT$AMT
C EVAL #RLSMIN = HVRSLMIN
C EVAL #RLSMAX = HVRLSMAX
C EVAL #RLSAV = HVRLSAV
C EVAL #SHPMIN = HVSHPMIN
C EVAL #SHPMAX = HVSHPMAX
C EVAL #SHPAV = HVSHPAV
C Exfmt SCR1
C ENDDO
C Seton LR

rmb
 115 pts.
 

Here is a trivial example: 

     H Debug
     DHOSTVARS         ds
     D iCnt                           5  0
     D iTot                           5  0
     D minFile                       10
     D maxFile                       10

      /free
       exec sql SELECT COUNT(*), SUM(APNKYF), MIN(APFILE), MAX(APFILE)
                    INTO :HOSTVARS
                    FROM mylib/ACCPTH             ;

       dump ;
       return ;
      /end-free

Use DSPFD with TYPE(*ACCPTH) and OUTPUT(*OUTFILE) to generate a test file in library {mylib}. Use the example to investigate.

I don’t see that you have {/free} in your example source although you do have {/end-free}. Also, your HOSTVARS data structure doesn’t match up with you column list in your SELECT statement at all.

There are other possible problems, but I can’t tell if there should be more to your example program or not. Maybe that’s why {/free} is missing and why you have a F-spec for REPFILW that isn’t used. Also, you are using SQL naming in your FROM clause, but that might be specified on your compile.

Tom

TomLiotta
 108,280 pts.
 

your HOSTVARS data structure must match field for field (sequence and type) with the select statement.

BigKat
 7,185 pts.
 

I was able to get the program to compile and display the results. I had a “.” in the FROM, should have been “/”, took out the end free, matched the HOSTVAR to SQL. Pretty simple to do, without much code involved at all.

Thanks for all your help.

rmb
 115 pts.