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 = <' + xChar +
'> & NullInd = <' + %char(NullInd) + '>') ;
exec sql SELECT FNULL
INTO :xChar :NullInd
FROM TESTFILE
WHERE FCHAR = '2' ;
dsply ('Rec 2: xChar = <' + xChar +
'> & NullInd = <' + %char(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 = <-1> 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 = <' + Inds.fNull + '> +
InNulls(7) = <' + %char(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) = <-1> |
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 = <' + %char(xPacked) + '> +
xChar1 = <' + xChar1 + '> +
xChar2 = <' + xChar2 + '>') ;
|
Which gives me the following:
DSPLY xPacked = <2> xChar1 = <1 > xChar2 = <2 > |
- xPacked contains the number of records/rows in the file
- xChar1 is the lowest value in the field/column FCHAR
- 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.
Please note, that SELECT ... INTO ... command ends in error, if SELECT returns multiple rows. This can be solved by using FETCH FIRST 1 ROWS ONLY clause.
ReplyDeleteI have found it useful to use the SQLCOD = -811 sometimes. When you receive data from third parties you need to deal with the data that they send you. We receive a header file and a detail file of invoices. The GL account number is in the detail file. The GL number will be same for all detail line of the invoice. Read the header file and check the detail file for the GL number.
ReplyDeleteRead G#EPAPHDR ;
DOW Not %EOF ;
Exec SQL
Select D.APEGL
Into :XS_APEGL
From G#EPAPDTL as D
WHERE D.CMPNO = :CMPNO AND D.VNDNO = :VNDNO
AND D.INVC# = :INVC# ;
// 1 Rec. Found Or Multiple Records found
If SQLCOD = 0 Or SQLCOD = -811 ;
APEGL = XS_APEGL ;
Update EPAPHDR %Fields(APEGL) ;
EndIf ;
Read G#EPAPHDR ;
EndDo ;
Excellent article, Simon! Another way is the VALUES command in SQL, and it can be embedded in RPG. I just want to add that we must stay clear of the old way: "SELECT...FROM QSYS/QSYSDUMMY1". This is the least efficient of all the methods. Some say that doing SELECT INTO or VALUES is less efficient that CHAIN. Granted the first time a program runs the query there is a latency because the system has to stand up OPD's under the covers, but the 2nd, 3rd and nth execution is crazy fast, and can be as fast as CHAIN. Those that stick only to CHAIN for alleged performance reasons do not factor in the time RPG takes to open the file to be chained to. Opens are very expensive, and with SELECT INTO or VALUES SQL does this under the covers. When tuned properly, SELECT INTO and VALUES execute just as fast as CHAIN, especially after v6r1.
ReplyDeleteWhat a coincidence.. I tried this yesterday in one of my pgm to condition a file based on non key field... Later I thought to go with native opcodes because of more number of records for selection and not to use data structure/arrays.. I should have tried fetch first.. Thanks Simon and Vojtech safanda
ReplyDeleteHi Dan,
ReplyDeleteThe 1st and 2nd executions will be slower, the 3rd and beyond can be faster. And only if the program is compiled correctly.
In Job Log:
1st time SQL is run: ODP Created, ODP Deleted.
2nd time SQL is run: ODP Created, ODP Not Deleted.
3rd time SQL is run: ODP Reused. <-- winner!
And program must be compiled with CLOSQLCSR = *ENDACTGRP or *ENDJOB instead of *ENDMOD. There are a few other rules too like a prepared SQL stmt should use ? parm markers.
Ringer
... and don't use the *NEW activation group. At the end of the program everything that belongs to the program (including the ODPs) is deleted and removed from memory
DeleteBirgitta