As far as I can tell it sorts as it should. What sort are you expecting? Mark
----- Reply message ----- Van: "'Bogdan' [email protected] [firebird-support]" <[email protected]> Aan: <[email protected]> Onderwerp: [firebird-support] Error in order by clause Datum: ma, aug. 24, 2015 10:37 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))ASbeginfor 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 30 3 3 2 12 212 14 2 1 11 1Otherwise 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))ASBEGIN 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))ASbeginfor 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; Ta e-pošta je bila pregledana z Avast protivirusnim programom. www.avast.com
