I have a SELECT that return more then 1 row but I want GET First row only.  Can I do the EGL statement ?

      get with #sql{

          SELECT
                AB03DTPR, AB03DTEN, AB03SOLI, AB03DINI
          FROM TEST.AB03
          WHERE AB03ROTP = :TAB020.AB03ROTP
          ORDER BY AB03DINI DESC
          }
          INTO
               AB03RS.AB03DTPR, AB03RS.AB03DTEN, TAB020.AB03SOLI, AB03RS.AB03DINI;

The generator knows how to build the statements Declare / Open / Fetch / Close as it did in Vagen?

or I need to code manually EGL Open/Get/Close ?

 

Hsieh,

EGL and VAGen are similar in how they process.  EGL just gives you even more options.

 

To try and answer your question:

 

The GET statement without a "position" operation or an array as the target will do an Declare/Open/Fetch/Close the same as it did in VAGen.  It will also only return one row if the target is NOT an array.   It will return the "first" row that matches the where clause/order by.   So again, the same as in VAGen.

 

If you want to just do a "FETCH", you can use Single Row processing, which is GET record SINGLEROW with #sql{ as needed }.    If singleRow will work meaning unique keys, then you can use this.  Again, this is the same as in VAGen. 

 

Is there some problem you are having or just wondering?

 

take care.

Mark

 

 

Hi Mark,

 

Yes, we are generating EGL to COBOL zVSE and getting error in sql-preprocessor

 

ARI0503E An SQL error has occurred.
You used an ORDER BY clause in a query with  no cursor. 
*ARI0505I SQLCODE = -524 ROWCOUNT = 0
*ARI0504I SQLERRP: ARIXOL1 SQLERRD1: -100 SQLERRD2: 0

 

For EGL SQL stmt:

 get singleRow with #sql{
          SELECT
                AB03DTPR, AB03DTEN, AB03SOLI, AB03DEST,
                AB03DEPT, AB03RAMA, AB03OBS1, AB03OBS2,
                AB03OBS3, AB03OBS4, AB03PART, AB03ORDE,
                AB03ROTP, AB03DINI
          FROM TEST.AB03
          WHERE AB03ROTP = :TAB020.AB03ROTP
          ORDER BY AB03DINI DESC

And no difference in cobol generated when I delete the option singleRow.

I would like EGL works as VAGen when the single SELECT return 1 row and I do not need make stmt Open/Get/Close

 

Hsieh

Hsieh,

The message is saying that a cursor is required to use an ORDER BY clause.   From the DB2 VSE Messages and Codes:

              The system assumes that a statement involving an ORDER BY clause will refer to more than one row.  Therefore, queries using an ORDER BY clause should use a cursor.

The use of SingleRow requires that one and only one row matches the where clause so an ORDER BY is meaningless if you know what is being returned is in fact unique.   It also means we can generate ONLY the FETCH and no cursor.  This makes it more efficient.

As stated before, the GET will return one row that matches the where clause unless the target is a dynamic array (which this is not).

So, all you need to do is code it this way.

get <sqlrecordVariable> with #sql{
          SELECT
                AB03DTPR, AB03DTEN, AB03SOLI, AB03DEST,
                AB03DEPT, AB03RAMA, AB03OBS1, AB03OBS2,
                AB03OBS3, AB03OBS4, AB03PART, AB03ORDE,
                AB03ROTP, AB03DINI
          FROM TEST.AB03
          WHERE AB03ROTP = :TAB020.AB03ROTP
          ORDER BY AB03DINI DESC

You can add an INTO clause if you want to explicitly define the target for the columns.  You used an INTO in your first example in this thread, so that sounds like what you should do.  

From this, EGL will generate a Declare/Open/Fetch/Close.   And it will ONLY return the first row that matches the where clause after the ORDER BY has been applied.

This is exactly the same way VAGen worked (i.e. just like the INQUIRY function).

Mark,

It works now !

But what's the difference with and without <sqlrecordVariable>.

Before I have the sql stmt:

get with #sql{

SELECT

AB03DTPR, AB03DTEN, AB03SOLI, AB03DEST,

AB03DEPT, AB03RAMA, AB03OBS1, AB03OBS2,

AB03OBS3, AB03OBS4, AB03PART, AB03ORDE,

AB03ROTP, AB03DINI

FROM TEST.AB03

WHERE AB03ROTP = :TAB020.AB03ROTP

ORDER BY AB03DINI DESC

}

INTO

AB03RS.AB03DTPR, AB03RS.AB03DTEN, TAB020.AB03SOLI,

TAB020.AB03DEST, TAB020.AB03DEPT, TAB020.AB03RAMA,

TAB020.AB03OBS1, TAB020.AB03OBS2, TAB020.AB03OBS3,

TAB020.AB03OBS4, TAB020.AB03PART, TAB020.AB03ORDE,

TAB020.AB03ROTP, AB03RS.AB03DINI;

not work.

and it works after add <sqlrecordVariable>

get AB03RS with #sql{

SELECT

AB03DTPR, AB03DTEN, AB03SOLI, AB03DEST,

AB03DEPT, AB03RAMA, AB03OBS1, AB03OBS2,

AB03OBS3, AB03OBS4, AB03PART, AB03ORDE,

AB03ROTP, AB03DINI

FROM TEST.AB03

WHERE AB03ROTP = :TAB020.AB03ROTP

ORDER BY AB03DINI DESC

...

 

glad it works. 

Specifying "get with #sql{}" and no record variable is the same as specifying "get singleRow with #sql{}".   In other words, either way means "singleRow".    As explained before, the "singleRow" means to bypass using a cursor and only generating the "Select into" (fetch only).

So,the existence of the sqlRecordVariable is what tells us to handle the SELECT using a cursor (declare/open/fetch/close).

 

Hope that explains it.

 

Mark

Ok !  Mark.

Now I get it.

Thanks a lot.

Regards,

Hsieh