Subject Wrong sort results
Author Bogdan

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 i = 0 it gives wrong sort order.

 

A             B             C             D             E

1,2          2,3          1             11           3

3,4          5,2          2             11           3

0             1             2             12           2

3             2             2             12           2

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;

 

 

 

Lep pozdrav,

 

Bogdan

 

MordiCom d.o.o.

info@...

+386 (05) 33 00 360

 




Avast logo

Ta e-poŇ°ta je bila pregledana z Avast protivirusnim programom.
www.avast.com