SQL to query a file

115 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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!

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • rmb
    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
    115 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • BigKat
    your HOSTVARS data structure must match field for field (sequence and type) with the select statement.
    8,330 pointsBadges:
    report
  • rmb
    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.
    115 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following