Wednesday, July 27, 2016

Use Select to retrieve one record of data

select into is used to retireve one record from a file

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.

6 comments:

  1. 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.

    ReplyDelete
  2. I 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.
    Read 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 ;

    ReplyDelete
  3. 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.

    ReplyDelete
  4. What 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

    ReplyDelete
  5. Hi Dan,

    The 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

    ReplyDelete
    Replies
    1. ... 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

      Birgitta

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.