Tuesday, October 24, 2017, 1:37:01 AM, junk@... wrote:

> I have a stored procedure along the lines of

> SELECT ID, Amount1, Amount2 ..

> FROM Table1.
> UNION
> SELECT ID, Amount1, Amount2...
> FROM Table2
> ORDER BY 1

> Is it possible to construct a conditional ORDER By clause that
> orders by Amount1 under certain conditions, and order by Amount2 under other conditions?

Using DSQL, no, since direct DSQL statements are compiled in the BLR
code of the stored procedure. You need to avail yourself of EXECUTE
STATEMENT. It's the sort of thing that this command is for.

> At the moment, the only way around it I have found (and I spent
> most of the night trying!) is to use an If clause and duplicate nearly all the SQL

> IF (Condition1) THEN
> SELECT ID, Amount1, Amount2 ..
> FROM Table1.
> UNION
> SELECT ID, Amount1, Amount2...
> FROM Table2
> ORDER BY 1

> ELSE IF (Condition2) THEN
> SELECT ID, Amount1, Amount2 ..
> FROM Table1.
> UNION
> SELECT ID, Amount1, Amount2...
> FROM Table2
> ORDER BY 2

> This seems a crazy way of having to achieve my aim!

Yep.

> Any help gratefully received.

create procedure my proc
(orderingcolumn char /* pass '2' or '3' depending on user input */
)
returns (
id bigint,
amount1 decimal,
etc. [columns you want in the left-to-right order you want]
)
as
declare variable execstring varchar (1000); /* whatever max. length you need */
begin
execstring = 'SELECT ID, Amount1, Amount2 ..'
|| ' FROM Table1'
|| ' UNION'
|| ' SELECT ID, Amount1, Amount2...'
|| ' FROM Table2'
|| ' ORDER BY ' || :orderingcolumm ;

FOR execute statement execstring
into :ID, :AMOUNT1, etc. [your output variables]
DO SUSPEND;
end

Looking at this problem from another angle...your examples are
just examples, of course...but on the surface it looks as if you would
better serve your need by using a DSQL statement directly, rather than
trying to bury it in a SP. Or you could define a view, which would be
unordered, and supply the order by clause when selecting from it.

Helen