Re: ON FIRST ROW

>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