next »

[Wee Ting C.]

Hi,

I have a weird case. Currently my application still running under Genero BDL 2.02.

My 4GL program generate four types of reports concurrently, i.e.
.......
START REPORT r_prtdet1 TO l_rptname1
START REPORT r_prtdet2 TO l_rptname2
START REPORT r_prtdet3 TO l_rptname3
START REPORT r_prtdet4 TO l_rptname4
........
  OUTPUT TO REPORT r_prtdet1 (l_table1.*, l_table2.fpartcd, l_rpt.*, l_cnt)
  OUTPUT TO REPORT r_prtdet2 (l_table1.*, l_table2.fpartcd, l_rpt.*, l_cnt)
  OUTPUT TO REPORT r_prtdet3 (l_table1.*, l_table2.fpartcd, l_rpt.*, l_cnt)
  OUTPUT TO REPORT r_prtdet4 (l_table1.*, l_table2.fpartcd, l_rpt.*, l_cnt, l_seqplo)
.........
FINISH REPORT r_prtdet1
FINISH REPORT r_prtdet2
FINISH REPORT r_prtdet3
FINISH REPORT r_prtdet4
........

Recently, we add additional field "fcourse_type" into the program for variable "l_rpt". Definitely, the field also use in REPORT functions. In DB, the field is VARCHAR(10) type, but in program, we assign to another field fcoursetype and define as CHAR(10). The field also use to do sorting in the report functions, i.e.
REPORT r_prtdet1()
.....
  ORDER BY ....., l_rpt.fcoursetype, ....
....
END REPORT
REPORT r_prtdet2()
.....
  ORDER BY ....., l_rpt.fcoursetype, ....
....
END REPORT
REPORT r_prtdet3()
.....
  ORDER BY ....., l_rpt.fcoursetype, ....
....
END REPORT
REPORT r_prtdet4()
.....
  ORDER BY ....., l_rpt.fcoursetype, ....
....
END REPORT

Now, we encounter this issue, "Program stopped at 'exmrpt14.4gl', line number 1123.
FORMS statement error number -1329.
A database index could not be created for a temporary database table needed for a report.", which refer to report "REPORT r_prtdet4" function.

The weird thing is it only hit REPORT r_prtdet4, not the other report functions. We have done few testing:
1) remove report r_prtdet4, program runs well for report r_prtdet1, r_prtdet2 and r_prtdet3.
2) remove report r_prtdet1, r_prtdet2 and r_prtdet3, program hits the -1329 error for report r_prtdet4.
3) remove "fcoursetype" variable, program runs well for all four reports.

Anyone can advise?

Thank you very much.

Regards,
Cheong

[Gary C.]

Hi

Have you checked that the user you are accessing the database as has permission to create indexes?

Gary

[Reuben B.]

Have a read of http://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_reports_Two_Pass_Reports.html, and see if there is any reason why report4 might be a two-pass report (and thus creating a temporary table) whilst the other reports can (presumably) be printed using a single pass.  More often than not, I find customers have two-pass reports that could be avoided by use of the ORDER EXTERNAL keyword

It looks like the temp table could be created because if it couldn't we would see a -1328 error.  In your case you get a -1329 error which relates to the creation of an index.  The documentation http://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/r_fgl_errors_001.html in discussing error -1329 gives a very specific case on why a -1329 error maybe generated.  Have you checked to see if an index with that name exists already.

The other thing to try is to enable FGLSQLDEBUG and see what database statements are being executed.

Reuben

PS Using 2.02, time to upgrade.

[Wee Ting C.]

Hi Gary,

Users have been using the program, error only occurs when we add additional field into the program.

Hi Reuben,

The program is capturing data from few tables, so unable to use ORDER EXTERNAL.

Reuben,

Good news, using FGLSQLDEBUG, we found the error is -517.

create temp table t_r3(par0 CHAR(2),par1 CHAR(5),par2 CHAR(3),par3 CHAR(1),par4 CHAR(1),par5 CHAR(3),par6 CHAR(1),par7 CHAR(1),par8 SMALLINT,par9 CHAR(7),par10 CHAR(1),par11 CHAR(6),par12 CHAR(2),par13 CHAR(2),par14 CHAR(6),par15 DATE,par16 CHAR(1),par17 CHAR(1),par18 CHAR(2),par19 CHAR(1),par20 CHAR(1),par21 CHAR(2),par22 CHAR(7),par23 CHAR(6),par24 CHAR(1),par25 CHAR(1),par26 CHAR(1),par27 CHAR(8),par28 CHAR(1),par29 CHAR(1),par30 CHAR(7),par31 SMALLINT,par32 DECIMAL(7,4),par33 SMALLINT,par34 CHAR(10),par35 SMALLINT,par36 SMALLINT) with no log:
  STATUS:0
create index i_r3 on t_r3(par2,par3,par6,par11,par5,par8,par10,par17,par23,par24,par25,par26,par28,par27,par34,par31,par33):
  STATUS:-517

I have combined the fields to into lesser variable (e.g. 6 variables instead of 17 fields for i_r3), and it is working fine now.

Thank you Reuben and Gary.

Regards,
Cheong