Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
SQL SELECT INTO Array in RPG
has no ratings.
Published: 20 Nov 2013
Revised: 17 Mar 2015 - 1247 days ago
Last viewed on: 15 Aug 2018 (11195 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SQL SELECT INTO Array in RPG Published by: Bob Cozzi on 20 Nov 2013 view comments(4)

The V7R1 docs indicate that a multiple occurrance data structure may be used when fetching more than one row at a time.

Here's what I want. I have defined an array with DIM(50), it is character with a length of 30.

The SQL statement is supposed to grab all the records that match a specific code (normally under 10 rows, but sometimes 10, 11 or even 12 exist). So I coded the array as DIM(50).

I'm only using a SELECT statement to get the data, as follows:

D myArray             S                   30A  Dim(50)

EXEC SQL SELECT ORDTEXT INTO :myArray FROM ORDMSG WHERE code = :ordCode
             FETCH FIRST 50 ROWS ONLY;

This isn't compiling. It's complaining about the MYARRAY variable being not valid. I've never actually had to do this (read into an array), and I thought it just worked.   Maybe someone else knows if this is possible and can show me the way?

[UPDATE]

The accepted way to use a DS array is to only reference it in a DECLARE/FETCH sequence, not via a stand-alone SELECT/INTO statement.

H MAIN(TESTARR)  DFTACTGRP(*NO)                            
H OPTION(*SRCSTMT)  EXTBININT(*YES)                        
                                                           
D myArray         DS                  Qualified Dim(50)    
D  ordText                      30A                        
                                                           
P TESTARR         B                                        
D TESTARR         PI                  EXTPGM('TESTARR')  
 /free                                                     
      EXEC SQL SET OPTION naming = *SYS, COMMIT = *NONE;   
                                                           
      EXEC SQL DECLARE FMITA CURSOR FOR                    
               SELECT ordText                              
               FROM   TESTORD;                             
                                                           
      EXEC SQL OPEN  FMITA;                                
      EXEC SQL FETCH FMITA FOR 50 ROWS INTO :myArray;      
      EXEC SQL CLOSE FMITA;                                
 /end-free                            

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS