----- Reply message -----
Od: "Alan J Davies Alan.Davies@... [firebird-support]" <firebird-support@yahoogroups.com>
Do: <firebird-support@yahoogroups.com>
Temat: [firebird-support] how to have different (conditional) order by clause with same select
Data: śr., paź 22, 2014 05:11

Hi, I have a number of SPs with several joined tables, sub-selects and
case statements. The only variation is the index or order by clause
used. When user requirements change, I have to ensure that all the
different SPs are updated (a Pain). Any help/advice would be welcome,
thanks.

Ideally, if I could have something like this, it would do it, but it
throws up this error:
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 24, column 25.
,.

create or alter procedure my_SP1 (
index_by integer) /* the order I want */
returns (
myfield1_out char(15),
myfield2_out char(15))
AS
begin
for select
myfield1,myfield2
from mytable
case
when index_by=1
then order by myfield1,myfield2
when index_by=2
then order by myfield2,myfield1
end
into :myfield1_out,:myfield2_out
do
suspend;
end

Simplifying things, I have:
create or alter procedure my_SP1 (
index_by integer) /* the order I want */
returns (
myfield1_out char(15),
myfield2_out char(15))
AS
begin
for select
myfield1,myfield2
from mytable
order by myfield1,myfield2
into :myfield1_out,:myfield2_out
do
suspend;
end

create or alter procedure my_SP1 (
index_by integer) /* the order I want */
returns (
myfield1_out char(15),
myfield2_out char(15))
AS
begin
for select
myfield1,myfield2
from mytable
order by myfield2,myfield1
into :myfield1_out,:myfield2_out
do
suspend;
end

Alan J Davies
Aldis