Re: Report3.0, Set Order-By dynamically?

From: Martin <mjones.satisdata_at_btinternet.com>
Date: Thu, 6 May 1999 19:06:05 +0100
Message-ID: <7gslqt$1jk$1_at_plutonium.btinternet.com>


define a user parameter say p_Order_by as character string say 100 long. Use an after parameter form trigger to set the contents of the string to the required order by , e.g

BEGIN
    :p_Order_by := 'ORDER BY ' || :p_users_choice; END;

  • p_users_choice being a parameter set in the parameter form or passed in from forms etc. - use as much logice as you need

Now, in your data model query append &p_Order_By to it e.g.

        SELECT *
        FROM    emp
        WHERE dept_no =10
        &p_Order_by

This will dynamically replace the string at runtime. The technique can be used to substitute almost any part of a query.

--
Regards
Martin Jones
--------------------------------------------
OCP - DBA
OCP - Application Developer
---------------------------------------------
zhang <zhang_at_visi.net> wrote in message news:3730E869.596_at_visi.net...

> Hi, everyone,
>
> Our Reports 3.0 runs on Windows 95.
> Question: How can I set Query-data Order-By(sort by) <Field_name>
> dynamically? i.e., Suppose my query fetches data from
> a table <emp> with field name <empno> ,<ssn#>, <name> etc,
> How can I use ONLY one report to print different
> data formats via different sorting fields, letting
> the end-users choose their own sorting field? (let's say
> User 1 chooses sorting by <empno>, user 2 chooses
> sorting by <ssn#> etc).
>
> Any suggestions and advices are greatly appreciated.
Received on Thu May 06 1999 - 20:06:05 CEST

Original text of this message