It should be
SUMA A B C D E 12 14 2 1 11 1 0 3 3 2 12 2 -1,1 1,2 2,3 1 11 3 -1,8 3,4 5,2 2 11 3 Suma descending Regards Bogdan From: [email protected] [mailto:[email protected]] Sent: Monday, August 24, 2015 6:26 PM To: [email protected] Subject: Re: [firebird-support] Error in order by clause 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)) 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; _____ <https://www.avast.com/antivirus> Avast logo Ta e-pošta je bila pregledana z Avast protivirusnim programom. www.avast.com <https://www.avast.com/antivirus> --- Ta e-pošta je bila pregledana z Avast protivirusnim programom. https://www.avast.com/antivirus
