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

From: Olli Rinne <otr_at_hemuli.tte.vtt.fi>
Date: 1996/09/09
Message-ID: <wi3f0rq4w5.fsf_at_titi-uu.tte.vtt.fi>#1/1

scancm_at_biobase.dk (Christian Mondrup) writes:
> : DEFINE TRIGGER
> : NAME = PRE-QUERY
> : TRIGGER_TYPE = V3
> : TEXT = <<<
> : BEGIN

> : 	   :sort_columns :=
> : 		'#= sort_columns) ORDER BY ('


> :                 ||:start.sort_order;

> :          END;

> :          >>>

> :       ENDDEFINE TRIGGER


 
> : <SELECT LIST> <WHERE CLAUSE> ORDER BY (PRIORITY,SCANDIANUMBER)


> :                                                ^

> :                                                lacking right parenthesis



In ORDER BY -clause there should be comma separated list of

expressions. And something in parenthesis is one expression. And comma alone is not an operator.
So the working query should be something like:

 <SELECT LIST> <WHERE CLAUSE> ORDER BY PRIORITY,SCANDIANUMBER

But when you are using this '#'-trick in Forms you are actually replacing clause

 AND ( columnname equals_something )

where '#' replaces columnname ('sort_column' in this case) so there will be extra left parenthesis at the end of clause.

You might try something like this:

 	   :sort_columns :=
 		'#= sort_columns) ORDER BY '
                 ||:start.sort_order || ',( SORT_COLUMNS';

Now the real query will be:

 <SELECT LIST> <WHERE CLAUSE>
 AND (SORT_COLUMNS = sort_columns)
 ORDER BY PRIORITY,SCANDIANUMBER,(SORT_COLUMS) which is legal in SQL-syntax.

                                                olli R Received on Mon Sep 09 1996 - 00:00:00 CEST