Once again - i've posted wrong procedure results L
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 i = 0 it gives wrong sort order.
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;
---
Ta e-pošta je bila pregledana z Avast protivirusnim programom.
https://www.avast.com/antivirus
