SQL in RPG

[thelearner]

Hi,

If I use SQL in stead of read, is it possible to use the original field names from the file? If yes, how?

Thanks.

 

[iSeriesCodePoet]

Yes, you just use the field names.

select myfield from myfile

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[thelearner]

wow! didn't expect it to be that easy.

Thank

 

[iSeriesCodePoet]

SQL is very easy. Let us know if you have any questions.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[thelearner]

Hi,

If MYFILE contain fields name
FIELD1 and FIELD2 and I want to use both of the fields in my RPG then my SQL would be something like

C/EXCE SQL
C+ SELECT FIELD1, FIELD2
C+ INTO :FIELD1, :FIELD2
C+ FROM MYFILE
C/END-EXEC

and I have to defind FILED1 and FIELD2 in D-spec, right?

What if I need to use 20 fields then I need to type all the 20 fields in the SELECT, INTO and D-spec. Isn't there a way to get around this?

Thanks

 

[LearningFox]

You could define an external data structure over the file and then use qualified sub-fields.

 

[thelearner]

Thanks. Another question, I want to read the file within certain date range. The fields in db that contain date is separate into
Century (len 2.0)
year (2.0)
month (2.0)
day (2.0)
How do I code the WHERE cause so the SQL only read data within the specified date range.

Thanks.

 

[Mercury2]

Hi Rapeek,

If you want to access rows one by one from file MYFILE above, you can do it this way.

First you have to define an external data structure to receive the rows. You then declare a cursor (recordset/buffer) for SQL to put the records in, open and fetch the rows out of it to retrieve the fields from the selected rows.

Here is an example. Assume you want to select rows from MYFILE between Mar 2003 1st and Feb 2004 29th.

1/ Define external data structure 
     D FileDs     E DS               ExtName( MYFILE )

If the file is not journalled insert the following stm first at the beginning of your program:
C/EXEC SQL +
     SET OPTION COMMIT = *NONE
C/END-EXEC

2/ Declare cursor. (!! or CONCAT = Concatenate)
C/EXEC SQL +
C+   DECLARE MyCsr CURSOR FOR 
C+   SELECT FIELD1, FIELD2, ...
C+   FROM   MYFILE
C+   WHERE  DIGITS(CENTURY) !! 
            DIGITS(YEAR) CONCAT 
            DIGITS(MONTH) !! 
            DIGITS(DAY) 
            BETWEEN '20030301' AND '20040229'
C/END-EXEC
              If     SqlStt<>'00000' 
              ... Error ...
              Endif    
                 
3/ OPEN cursor 
C/EXEC SQL +
C+   OPEN MyCsr 
C/END-EXEC
              If     SqlStt<>'00000' 
              ... Error ...
              Endif      

4/ FETCH cursor in a loop to the end
C              Do    *Hival
C/EXEC SQL +
C+   FETCH NEXT MyCsr 
C+   INTO  :FileDs
C/END-EXEC
                if   SqlStt <> '01000' /* End */
                leave 
                endif
                ...
                enddo

5/ CLOSE cursor
C/EXEC SQL +
C+   CLOSE MyCsr 
C/END-EXEC

HTH -- Philippe

 

[thelearner]

Thank you very much, Talkturkey.

 

[thelearner]

Hi,

I want to put some of these fields in Data Structure to combine the value. ie

D DS
D newfld 1 4
D field1 1 2 0
D field2 3 4 0

It won't compile. What am I missing? The error msg said Name in external description is not renamed; external name is ignored.

Thanks

 

[iSeriesCodePoet]

Try this instead:

D newfield   DS
D field1              2 0
D field2              2 0

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[thelearner]

Hi iSeriesCodePoet;

It did not work. Look like I can't redefind it in DS since I already defined the whole record in external DS. That's ok I just have to concat them inside the pgm.

Thank everyone, for all your help.

 

[Mercury2]

Hi again,

Assume the 2 fields to combine are adjacent in positions 55-56 and 57-58 in FileDs.
You may do this, it should work.

D FileDS        E DS                  Extname( MYFILE )    
D  FldsCombined                  4    Overlay( FileDs: 55 )

 

[thelearner]

Interesting, it compiled but my report came out blank!

 

[Mercury2]

Can you pls explain your problem more precisely ?

Philippe

 

[thelearner]

Hi Talkturkey,

Well, let me give you my source code. When I use overlay my report print with no data. If I mark off the overlay line then I got data in the report.
D TKREC E DS EXTNAME(@TKL050)
D OPNDAT 6 OVERLAY(TKREC:493)


C/EXEC SQL

C+ SET OPTION

C+ COMMIT = *NONE,

C+ CLOSQLCSR = *ENDMOD

C/END-EXEC


C/EXEC SQL

C+ DECLARE MYCURSOR DYNAMIC SCROLL CURSOR FOR

C+ SELECT *

C+ FROM @TKL050

C+ WHERE TKOPNC * 1000000 + TKOPNY * 10000 + TKOPNM * 100 +
C+ TKOPND BETWEEN :FMDATE AND :TODATE AND

C+ TKPENT = :ZPENT AND TKETCD <> '00'
C/END-EXEC

C/EXEC SQL
C+ OPEN MYCURSOR
C/END-EXEC


/FREE

DOW SQLCOD = 0;

/END-FREE

C/EXEC SQL
C+ FETCH FROM MYCURSOR
C+ INTO :TKREC
C/END-EXEC

/FREE

IF SQLCOD <> 0;
LEAVE;
ENDIF;
....
..print report...
.....
ENDDO;


Just for your info, the position that I overlay is the same position of field TKOPNM, TKOPND, and TKOPNY in the WHERE clause. I don't know if it has any relation with the problem.

Thanks

 

[Mercury2]

Hi Rapeek,

Sorry, I did not notice the subfields were in packed notation. Don't consider my previous post pls.
Therefore you can't use the overlay keyword and use the OPNDAT field to group packed fields together to test the group field against other values. So you're right to remove the overlay line and use concat instead.
However, it should have been correct to use the OPNDAT field to group alphanumeric fields together be they adjacent or not in the record description and test OPNDAT value against another alphanumeric field or literal.

Philippe

 

[thelearner]

Hi Talkturkey,

The subfield (TKOPNM, TKOPND, and TKOPNY) are defined in DDS as "S" (zone dec.) not packed.
"Therefore you can't use the overlay keyword and use the OPNDAT field to group packed fields together to test the group field against other values." Per what you said here, is it apply to zone dec. too? I'm just curious.

Another question. When should we use SQL instead of native read? In my case here, suppose if I have a file with the key TKOPNC, TKOPNY, TKOPNM, and TKOPND, then it should be faster to just use native read, right?

Thanks.

 

[Mercury2]

Hi again,

"... is it apply to zone dec. too?" No and it should work and I now think that your report is blank because you overlay fields TKOPNM, TKOPND and TKOPNY in that wrong order in the file definition (I presume it is the order fields are in the record definition) instead of this right order TKOPNY, TKOPNM and TKOPND and obviously SQL never finds any corresponding records. Pls check and let me know.

"...use SQL instead of native read?". Well, it depends IMHO essentially on the file size.
If the file is large enough (say >= 300,000 rcds) I think SQL will go faster else READ could be a better choice. Sounds also better to use the keyed file on TKOPNC, TKOPNY, TKOPNM, and TKOPND in both cases however.

Let me know what's your choice. Thanks.

Philippe

 

[thelearner]

Hi Talkturkey ,

The order in DDS is TKOPNC, TKOPNY, TKOPNM, and TKOPND. TKOPNC is at posiontion 491-492 and the rest followed right after.

D OPNDAT 6 OVERLAY(TKREC:493)

I only use the overlay field, OPNDAT, for printing on the report. It's not part of record selection. And for testing purpose, I did not manipulate OPNDAT at all. I just code it on the 'O' spec as one of my column in the report. So I don't see any relation with the wrong order.

SQL vs. native read
My file has almost 700,000 records. I copied this pgm and modified to use native read instead and found that native read is alot faster. My file has a key TKOPNC, .Y,.M,.D.
Is it the way I coded my SQL? Any thing that you see can be improved?

Thanks