Use an "IF statement" on the "ORDER BY" in a report in INFORMIX 4GL

I'm having a report which is called many times but I want the order to be different each time.

How can I change the "order by" depending on a variable.

For example

report print_label()

   If is_reprint
   then
       order by rpt.item_code, rpt.description
   else
       order by rpt.description, rpt.item_code
   end if

I tried passing in a variable when calling the report:

let scratch = "rpt.item_code, rpt.description"
start print_label(scratch)

And in the report I did:

order by scratch

But it didn't work...... Any other suggestions ?? Thank you!

Solution

Short answer

The ORDER BY clause in an I4GL REPORT function has crucial effects on how the code implementing the report is generated. It is simply not feasible to rewire the generated code like that at run-time.

Therefore, you can't achieve your desired result directly.

Notes

Note that you should probably be using ORDER EXTERNAL BY rather than ORDER BY — the difference is that with EXTERNAL, the report can assume the data is presented in the correct order, but without, the report has to save up all the data (in a temporary table in the database), then select the data from the table in the required sorted order, making into into a two-pass report.

If you're brave and have the I4GL c-code compiler, you should take a look at the code generated for a report, but be aware it is some of the scariest code you're likely to encounter in a long time. It uses all sorts of tricks that you wouldn't dream of using yourself.

Workaround solutions — in outline

OK; so you can do it directly. What are your options? In my view, you have two options:

Use two parameters specifically for choosing the ordering, and then use an ORDER BY (without EXTERNAL) clause that always lists them in a fixed order. However, when it comes time to use the report, choose which sequence you want the arguments in.
Write two reports that differ only in the report name and the ORDER EXTERNAL BY clause. Arrange to call the correct report depending on which order you want.

Of these, option 2 is by far the simpler — except for the maintenance issue. Most likely, you'd arrange to generate the code from a single copy. That is, you'd save REPORT print_label_code_desc in one file, and then arrange to edit that into REPORT print_label_desc_code (use sed, for example) — and the edit would reverse the order of the names in the ORDER BY clause. This isn't all that hard to do in a makefile, though it requires some care.

Option 1 in practice

What does option 1 look like in practice?

DECLARE c CURSOR FOR
    SELECT * FROM SomeTable

START REPORT print_label -- optional specification of destination, etc.

FOREACH c INTO rpt.*
    IF do_item_desc THEN
        OUTPUT TO REPORT print_label(rpt.item_code, rpt.description, rpt.*)
    ELSE
        OUTPUT TO REPORT print_label(rpt.description, rpt.item_code, rpt.*)
    END IF
END FOREACH

FINISH REPORT print_label

The report function itself might look like:

REPORT print_label(col1, col2, rpt)
    DEFINE col1 CHAR(40)
    DEFINE col2 CHAR(40)
    DEFINE rpt  RECORD LIKE SomeTable.*

    ORDER BY col1, col2

FORMAT

    FIRST PAGE HEADER
        …
    BEFORE GROUP OF col1
        …
    BEFORE GROUP OF col2
        …
    ON EVERY ROW
        …
    AFTER GROUP OF col1
        …
    AFTER GROUP OF col2
        …
    ON LAST ROW
        …
END REPORT

^{Apologies for any mistakes in the outline code; it is a while since I last wrote any I4GL code.}

The key point is that the ordered by values are passed specially to the report and used solely for controlling its organization. You may need to be able to print different details in the BGO (shorthand for BEFORE GROUP OF; AGO for AFTER GROUP OF) sections for the two columns. That will typically be handled by (gasp) global variables — this is I4GL and they are the normal way of doing business. Actually, they should be module variables rather than global variables if the report driver code (the code which calls START REPORT, OUTPUT TO REPORT and FINISH REPORT) is in the same file as the report itself. You need this because in general the reporting at the group levels (in the BGO and AGO blocks) will need different titles or labels depending on whether you're sorting code before description or vice versa. Note that the meaning of the group aggregates change depending on the order in the ORDER BY clause.

Note that not every report necessarily lends itself to such reordering. Simply running the BGO and AGO blocks in a different order is not sufficient to make the report output look sensible. In that case, you will fall back onto option 2 — or option 2A, which is write two separate reports that don't pretend to be just a reordering of the ORDER BY clause because the formatting of the data needs to be different depending on the ORDER BY clause.

As you can see, this requires some care — quite a bit more care than the alternative (option 2). If you use dynamic SQL to create the SELECT statement, you can arrange to put the right ORDER BY clause into the string that is then prepared so that the cursor will fetch the data in the correct order — allowing you to use ORDER EXTERNAL BY after all.

Summary

If you're a newcomer to I4GL, go with option 2. If your team is not reasonably experienced in I4GL, go with option 2. I don't like it very much, but it is the way that can be handled easily and is readily understood by yourself, your current colleagues, and those still to come.

If you're reasonably comfortable with I4GL and your team is reasonably experienced with I4GL — and the report layout really lends itself to being reorganized dynamically — then consider option 1. It is trickier, but I've done worse things in I4GL in times past.