As far as I can tell it sorts as it should. What sort are you expecting?

Hi to all

Server: Firebird 2.5.4.

I have following stored procedure:

ALTER PROCEDURE TEST_XXXX (

    I SMALLINT)

RETURNS (

    SUMA NUMERIC(15,2),

    A NUMERIC(15,2),

    B NUMERIC(15,2),

    C INTEGER,

    D VARCHAR(10),

    E VARCHAR(10))

AS

begin

for select sum(t.a) - sum(t.b), sum(a), sum(b), c, d, e from xxxx t

    group by c, d, e

    order by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E)) DESCENDING

    into :suma, :a, :b, :c, :d, :e

    do suspend;

end

If i run it with paraneter i = 0 it gives wrong sort order.

Results:

SUMA  A             B             C             D             E

-1,1        1,2          2,3          1             11           3

-1,8        3,4          5,2          2             11           3

0             3             3             2             12           2

12           14           2             1             11           1

Otherwise the sort order is ok.

This propagates only when there is iif within iif clause.

Am i doing something wrong or it is a bug ?

Best regards

Bogdan

=====  complete script:

SET SQL DIALECT 3;

/******************************************************************************/

/****                          Stored procedures                           ****/

/******************************************************************************/

SET TERM ^ ;

CREATE PROCEDURE TEST_XXXX (

    I SMALLINT)

RETURNS (

    SUMA NUMERIC(15,2),

    A NUMERIC(15,2),

    B NUMERIC(15,2),

    C INTEGER,

    D VARCHAR(10),

    E VARCHAR(10))

AS

BEGIN

  SUSPEND;

END^

SET TERM ; ^

/******************************************************************************/

/****                                Tables                                ****/

/******************************************************************************/

CREATE TABLE XXXX (

    A  NUMERIC(15,2),

    B  NUMERIC(15,2),

    C  INTEGER,

    D  VARCHAR(10) COLLATE PXW_SLOV,

    E  VARCHAR(10) COLLATE PXW_SLOV

);

/******************************************************************************/

/****                          Stored procedures                           ****/

/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER PROCEDURE TEST_XXXX (

    I SMALLINT)

RETURNS (

    SUMA NUMERIC(15,2),

    A NUMERIC(15,2),

    B NUMERIC(15,2),

    C INTEGER,

    D VARCHAR(10),

    E VARCHAR(10))

AS

begin

for select sum(t.a) - sum(t.b), sum(a), sum(b), c, d, e from xxxx t

    group by c, d, e

    order by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E)) DESCENDING

    into :suma, :a, :b, :c, :d, :e

    do suspend;

end^

SET TERM ; ^

INSERT INTO XXXX (A, B, C, D, E)

          VALUES (1.2, 2.3, 1, '11', '3');

INSERT INTO XXXX (A, B, C, D, E)

          VALUES (3.4, 5.2, 2, '11', '3');

INSERT INTO XXXX (A, B, C, D, E)

          VALUES (0, 1, 2, '12', '2');

INSERT INTO XXXX (A, B, C, D, E)

          VALUES (3, 2, 2, '12', '2');

INSERT INTO XXXX (A, B, C, D, E)

          VALUES (14, 2, 1, '11', '1');

COMMIT WORK;