The germ for this post came from a question I was asked by a reader of this blog:

In SQLRPG do I have to define cursor, open cursor, fetch, close cursor if I only want one record?

That is a lot of work just to retrieve one record. Fortunately there is a very simple method where I can retrieve a field, multiple fields, the entire record, or join to another record, etc providing I am just returning one record/row.

But before I get started let me give the DDS of my example file, TESTFILE , and show the data it contains.

01 A R TESTFILER 02 A FCHAR 3A 03 A FPACKED 3P 0 04 A FDATE L 05 A FTIME T 06 A FTIMESTAMP Z 07 A FDFT 1A DFT('A') 08 A FNULL 1A ALWNULL 09 A DFT(*NULL) 10 A K FCHAR

And the file contains two records:

FCHAR FPACKED FDATE FTIME FTIMESTAMP FDFT FNULL 1 1 2001-07-05 08.38.00 2016-05-04-15.40.01.000000 A - 2 2 2001-04-15 19.12.23 2015-04-30-15.45.12.000000 A X ******** End of report ********

I am sure I do not have to explain what the different types of fields are. Just not that the field FNULL contains null in the first record.

In my first example I want to retrieve the data from the first record/row. I use the SELECT INTO which allows me to retrieve just one row/record from the statement.

dcl-s xTime time ; exec sql SELECT FTIME INTO :xTime FROM TESTFILE WHERE FCHAR = '1' ;

The only difference between this Select and the many others I have used in this blog is the INTO clause. This takes the value from the retrieved record's FTIME field and places it in my program variable xTime . As the SELECT INTO can only retrieve one record/row I have to make sure that my WHERE is unique. If it is not the statement will not complete successfully and the value of the SQL Code field, SQLCOD , will be -811.

This next example show how to retrieve a value from a field that could be null, I won't go into too many details as I have it extensively in the post SQL and null.

dcl-s xChar char(3) ; dcl-s NullInd int(5) ; exec sql SELECT FNULL INTO :xChar :NullInd FROM TESTFILE WHERE FCHAR = '1' ; dsply ('Rec 1: xChar = & NullInd = ') ; exec sql SELECT FNULL INTO :xChar :NullInd FROM TESTFILE WHERE FCHAR = '2' ; dsply ('Rec 2: xChar = & NullInd = ') ;

Notice that in the INTO clause there are two variables, notice that there is not a comma ( , ) between them. The first variable will contain the value from FNULL and the second will contain the value from the null byte map for the FNULL field. When I look at the output from the DSPLY operation code I see:

DSPLY Rec 1: xChar = < > & NullInd = DSPLY Rec 2: xChar = <X > & NullInd = <0>

The first record's FNULL is null, therefore, the program variable NullInd is -1.

Of course if I wanted to retrieve more than one field from the file I could just:

dcl-s xChar char(3) ; dcl-s xPacked packed(5) ; exec sql SELECT FCHAR,FPACKED INTO :xChar,:xPacked FROM TESTFILE WHERE FCHAR = '1' ;

If I wanted to retrieve all of the fields from the file I could list them all, or I could use a data structure like this:

dcl-ds InDs extname('TESTFILE') qualified ; end-ds ; dcl-s InNulls int(5) dim(7) ; exec sql SELECT * INTO :InDs :InNulls FROM TESTFILE WHERE FCHAR = '1' ; dsply ('Inds.fNull = + InNulls(7) = ') ;

Notice I have a data structure for nulls. As the FNULL can contain nulls I need this to contain the null byte map for FNULL . I can ignore the values for all the other array elements except for the seventh, which is the null byte value for FNULL .

DSPLY Inds.fNull = < > InNulls(7) =

I can even join multiple files together and retrieve a single "row":

dcl-s xChar char(3) ; dcl-s xName char(10) ; exec sql SELECT A.FCHAR,B.FNAME INTO :xChar,:xName FROM TESTFILE A LEFT OUTER JOIN NAMEFILE B ON A.FCHAR = B.FCHAR AND A.FCHAR = '1' ;

In all these examples I have used the SELECT INTO like a Chain operation code in RPG. But I can do so much more, like this:

dcl-s xChar1 char(3) ; dcl-s xChar2 like(xChar1) ; dcl-s xPacked packed(5) ; exec sql SELECT COUNT(*),MIN(FCHAR),MAX(FCHAR) INTO :xPacked,:xChar1,:xChar2 FROM TESTFILE ; dsply ('xPacked = + xChar1 = + xChar2 = ') ;

Which gives me the following:

DSPLY xPacked = <2> xChar1 = <1 > xChar2 = <2 >

xPacked contains the number of records/rows in the file

contains the number of records/rows in the file xChar1 is the lowest value in the field/column FCHAR

is the lowest value in the field/column xChar2 is the greatest value in the field/column FCHAR

You certainly cannot do that with a CHAIN .

You can learn more about the SELECT INTO statement from the IBM website here.

This article was written for IBM i 7.2, and should work for earlier releases too.