Re: ON FIRST ROW

David Chan (dwc@cix.compulink.co.uk) wrote: : >We use the following technique to create the equivalent of a ON FIRST ROW : >clause. It is extremely useful when you want to declare cursors in : >a report only once, or perhaps do some sub-totalling on all rows sent : >down to a report when group sum() won't work. : > : > foreach c_report into p_vars.* : > output to report rpt(1,p_vars.*) : > end foreach : > : >The report then looks as follows : : > : >report rpt(r_first_row, r_vars) : > : > define r_first_row smallint : > define r_vars record like vars.* : > : > order external by r_first_row, r_vars.other_group : > : > before group of r_first_row : > declare cursors... : > initialize r_sub_totals : > : >Comments please... : For declaration of cursors and initialisation of variables, we use a : separate function: : define mSQLInit char(2) # modular initialisation variable : function InitOnce() : if length(mSQLInit) = 0 : or mSQLInit != "OK" : then : prepare ... : declare ... : let mSQLInit = "OK" : end if : : end function -- InitOnce : function doSomeWork() : call InitOnce() : : open ... : fetch ... : end function -- doSomeWork : This allows us to highly de-couple the functionality in our code which : means that anybody can call doSomeWork() without having to call a : separate initialisation function, and our functions can easily be reused. : We generally see a 2-4 fold performance improvement by doing this : especially if the prepared statements are large. In general, 95% of our : SQL statements (including singletons) are prepared in this way. Once : mSQLInit has been set to OK, the overhead of calling the function and the : if statement is minimal. : You will notice that the modular variable is uninitialised when first : used and there is a small risk (theoretically 1 in 65536 chance) that it : would fail. Fortunately, it has never failed in over 4 years of use, and : if it did, we would change it to a three letter character. : While on the subject of de-coupling, we don't use foreach much. We : prefer to use separate openCursor(), fetchCursor() functions which : encapsulates the SQL. This make it reuseable and reduces maintenance : when there are changes. openCursor() would call InitOnce(). : This may all seem over-complicated but we already have 2 million lines of : 4GL to maintain, and encapsulating functionality saves us from drowning. : Big systems are a nightmare! :-) : David W Chan : Objective Research Ltd : dwc@cix.compulink.co.uk TEST TEST