Re: Forms 3.0, problem with # in PRE-QUERY

Christian Mondrup (scancm_at_biobase.dk) wrote:
: Every now and then some Forms 3.0 guru has pointed to the use of '#' written
: to a database field during a PRE-QUERY trigger.
 

: I have been using this feature several times most often to force my own
: ORDER BY clause into the SELECT statement generated by runform. But now I
: have encountered a problem with this feature (or rather the SQL syntax
: checker in runform I think).
 

: What I want to do is to use a forms variable with a column list generated
: by a function before the PRE-QUERY trigger fires. The queried block is based
: upon a view with the last column (sort_columns) containing a fixed char
: value. This column is represented by the last database field of the block
: with a display lenght great enough to get the ORDER BY copied into it:
 

: DEFINE TRIGGER
 
: NAME = PRE-QUERY
: TRIGGER_TYPE = V3
: TEXT = <<<
: BEGIN
: :sort_columns :=
: '#= sort_columns) ORDER BY ('
: ||:start.sort_order;
: END;
: >>>
 

: ENDDEFINE TRIGGER
 
: If the list in :start.sort_order contains only one column name (or
: expression) my query runs OK. But if there are more than one
: columns/expressions runform complains with error 40505 (unable to perform
: query). When I press KEY-HELP to examine the erroneous SQL statement a
: mark is set at the comma after the first column name in the ORDER BY clause
: together with an error description:
 

: <SELECT LIST> <WHERE CLAUSE> ORDER BY (PRIORITY,SCANDIANUMBER)
: ^
: lacking right parenthesis
 

: There are NO unmatching parentheses in the WHERE clause !!

With no follow-ups to my message it looks like there is no way to use the '#' feature for my purpose: my users being allowed to configure the sorting of the records at runtime.

So I have abandoned the #-technique and introduced an external PL/SQL script with a positional parameter containing the current ORDER BY clause. I then have to accept the performance overhead caused by this external script.

: --
: ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
: + Christian Mondrup +
: + Scandiatransplant, Skejby Hospital, University Hospital of Aarhus +
: + Brendstrupgaardsvej +
: + DK 8200 Aarhus N +
: + Denmark +
: + +
: + Phone: +49 89 49 53 01 +
: + Telefax: +45 89 49 60 07 +
: + E-Mail: scancm_at_biobase.dk +
: ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

--
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+   Christian Mondrup                                                      +
+   Scandiatransplant, Skejby Hospital, University Hospital of Aarhus      +
+   Brendstrupgaardsvej                                                    +
+   DK 8200 Aarhus N                                                       +
+   Denmark                                                                +
+                                                                          +
+   Phone:   +49 89 49 53 01                                               +
+   Telefax: +45 89 49 60 07                                               +
+   E-Mail:  scancm_at_biobase.dk                                             +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++