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.

 

[Mercury2]

Hi again,

I think I won't understand anything more, now.
Can you show here your SQLRPGLE program and its related report or at least a meaningful excerpt,
or better yet send both as attachment to carpediemps@hotmail.com ?

Philippe

 

[thelearner]

Hi Talkturkey ,

I sent you both programs and the sample report.

Thanks

 

[Mercury2]

Hi,

Blank report
I received your attachements and tried to understand why the program without the overlay keyword worked and produced a blank report when this keyword was in use.
This is what I came up with: in fact it doesn't work because the overlay kwd applies on the DS statement itself and not on a subfield inside the DS instead.
D TKREC E DS EXTNAME(@TKL050)
D OPNDAT 6 OVERLAY(TKREC:493) These statements are wrong and produce bad results because the field OPNDAT overlays the DS TKREC and not a subfield in the DS. I presume that the overlay doesn't take place and makes all the fields blanks on the record ?
On the other hand, the Manual says "This keyword is allowed only for data structure subfields" and therefore the compiler should have detected an error.

SQL vs. native read
- @TKL050 keyed on TKOPNC, .Y,.M,.D
- Change this SQL stm (much overhead I think)"WHERE TKOPNC * 1000000 + TKOPNY * 10000 + TKOPNM * 100 TKOPND..." against "WHERE
DIGITS(TKOPNC) !! DIGITS(TKOPNY) !! DIGITS(TKOPNM) !!
DIGITS(TKOPND)..."
Give it a try and let us know, will you ?

Philippe

 

[thelearner]

Hi Talkturkey,

SQL vs. Native Read. I already try that, still same thing. It takes about 45 sec. to run pgm with SQL vs. less than 5 sec. with the native read.

Thanks.

 

[thelearner]

Hi Talkturkey,

I tried to compare each field instead, like this.

WHERE TKOPNC = :FMCC AND TKOPNY = :FMYY AND TKOPNM = :FMMM
AND TKOPND >= :FMDD
The result is a lot faster (but still about 3 sec. slower than native read).
I just tries for the testing purpose. I can't check date range this way because if date range is cover last month to this month,ie from 20040201 to 20040310, the result won't come out correctly.
So I think when pgm has to concat or calculate, it creates a lot of overhead. But I think there must be some way to overcome this.

Thanks.