ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Fill the result table with x records

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Fill the result table with x records

    Hello,

    I'm using a SQL query in a RPG IV ILE program and the records founded are display on the screen (pressing the rollup button shows the next records). It works good but require too much time because of the thousands of record and the complexity of the query.
    So I'd like that the SQL query only fill the result table with x records (the number display on the screen). And rollup will fill the table with the x next records (I think STRSQL tool works in this way).

    Thank you for your help.

    Philb

  • #2
    You can modify this code

    Code:
           *
           * just put this in do loop (fetchSQL) like do 10. 
           * Hope this helps I can mail you the whole program if 
           * you want.
           *     
         C                   exsr      $ClearSFL
         C                   exsr      $DeclareSQL
         C                   exsr      $OpenSQL
         C                   exsr      $FetchSQL
         C                   dow       SQLCOD = SQLErrorCode
          *
         C                   if        qDBXTYP = 'S'
          *
          * Check Object & Get Object Description
          *
         C                   exsr      $QUSRMBRD
         C                   if        not %error
         C                   exsr      $LoadSFL
         C                   endif
          *
         C                   endif
          *
         C                   exsr      $FetchSQL
         C                   enddo
          *
         C                   exsr      $CloseSQL
          *
         C                   if        RRn1 <= 0
         C                   eval      *in51 = *off
         C                   endif
         C                   eval      *in89 = *on
          *
         C                   endsr
     
          *-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
          * Declare SQL
          *-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
         C     $DeclareSQL   begsr
          *
         C/EXEC SQL
         C+ DECLARE csrQDBXREF CURSOR FOR
         C+  SELECT DBXFIL, DBXLIB, DBXATR, DBXTYP, DBXTXT
         C+  FROM   QADBXREF
         C+  WHERE  DBXTYP = 'S'
         C/END-EXEC
          *
         C                   endsr
          *-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
          * Fetch SQL
          *-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
         C     $FetchSQL     begsr
          *
         C/EXEC SQL
         C+   FETCH csrQDBXREF
         C+   INTO  :QDBXREF
         C/END-EXEC
          *
         C                   endsr
          *-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
          * Open SQL Cursor
          *-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
         C     $OpenSQL      begsr
          *
         C/EXEC SQL
         C+ OPEN csrQDBXREF
         C/END-EXEC
          *
         C                   endsr
          *-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
          * Close SQL Cursor
          *-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
         C     $CloseSQL     begsr
          *
         C/EXEC SQL
         C+   CLOSE csrQDBXREF
         C/END-EXEC
          *
         C                   endsr
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

    Comment


    • #3
      Re: You can modify this code

      Thank you for your help.

      Could you send me the whole program ?

      Philb

      Comment


      • #4
        Here is an example

        Phil

        remember you will have to modify this code to work for you.
        it now reads all records. you will have to put in a do loop...like do 10 then when user pages down then do 10 again....





        download the ziped text files above.

        there are many SQl examples located here
        All my answers were extracted from the "Big Dummy's Guide to the As400"
        and I take no responsibility for any of them.

        www.code400.com

        Comment


        • #5
          there really is no SQL statement to limit returned rows?

          in other databases I am use to doing something like:

          select * from orders limit 10;

          or such.

          I ask because I use things like this a lot at a customer site to examine database tables and get a feel for what data is in the table.

          so, you people are saying that from STRSQL there is no way to issue a select statement and return only a given number of rows?

          i don't buy it. there's gotta be a way.

          matt
          Some people are like slinkies.
          Not really good for anything, but
          you can't help but smile when you
          see them tumble down the stairs.

          Comment


          • #6
            found it

            select * from qadbxref fetch first 10 rows only

            stick that in your serial port and smoke it.
            v5r3 btw. don't know about earlier versions.

            matt
            Some people are like slinkies.
            Not really good for anything, but
            you can't help but smile when you
            see them tumble down the stairs.

            Comment


            • #7
              fetch first X rows only worked with earlier versions too--but if you were using net.DATA and its SQL, then you had to use something else (to limit rows returned). Another one of those wonderful "cross platform" idiosyncrasies--even though in this case it's the same hardware/OS! ^^''

              Comment


              • #8
                pleading ignorance...

                whats net.DATA?
                Some people are like slinkies.
                Not really good for anything, but
                you can't help but smile when you
                see them tumble down the stairs.

                Comment


                • #9
                  Re: pleading ignorance...

                  Originally posted by matt.stucky
                  whats net.DATA?
                  It's a scripting language IBM was developing, along the lines of PHP, for creating dynamic web pages--it was abandoned a few years ago (before it ever got out of beta stage).

                  Using it was nasty, and it was very slow (performance wise).

                  Comment

                  Working...
                  X