sqlfirebird

Order By is not working using "WITH" Clause


I am using Firebird database.

I have the SQL below, which is concatenating the SHORT_CODE column data, but without ordering as per the ORDER_NUMBER column in ABC table in WITH clause.

With TBL_SHORT_CODE (SHORT_CODE, FK_KEY) As 
(
SELECT Distinct(XYZ.SHORT_CODE) As SHORT_CODE, ABC.FK_KEY 
From ABC Join XYZ On ABC.PK_KEY = XYZ.FK_KEY
where XYZ.FK_KEY = '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1
Order By ABC.ORDER_NUMBER
)


SELECT LIST(Distinct(TBL_SHORT_CODE.SHORT_CODE), '' ), ABC.FK_BOM
From ABC 
Join XYZ ON ABC.FK_KEY = XYZ.PK_KEY 
Join TBL_SHORT_CODE On TBL_SHORT_CODE.FK_KEY = ABC.FK_KEY
where ABC.FK_BOM = '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1
Group By ABC.FK_BOM

Solution

  • I got problem solved in Firebird yahoo group.

    SQL:

    EXECUTE BLOCK RETURNS (SHORT_CODES VARCHAR(2000), FK_BOM INTEGER
    AS 
    DECLARE VARIABLE SHORT_CODE1 VARCHAR(2000);
    DECLARE VARIABLE FK_BOM2 INTEGER;
    DECLARE VARIABLE DUMMY INTEGER;
    BEGIN
        FK_BOM = NULL;
        FOR With TBL_SHORT_CODE (SHORT_CODE, FK_KEY, ORDER_NUMBER) As 
            (SELECT XYZ.SHORT_CODE, ABC.FK_KEY, min(ABC.ORDER_NUMBER)
            From ABC Join XYZ On ABC.PK_KEY = XYZ.FK_KEY
            where XYZ.FK_KEY = '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1
            group by 1, 2)
    
        SELECT ABC.FK_BOM, tsc.SHORT_CODE, min(tsc.ORDER_NUMBER)
        From ABC 
        Join XYZ ON ABC.FK_KEY = XYZ.PK_KEY 
        Join TBL_SHORT_CODE tsc On tsc.FK_KEY = ABC.FK_KEY
        where ABC.FK_BOM = '{009DA0F 8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1
        Group By 1, 2
        ORDER BY 1, 3
        into :FK_BOM2, :SHORT_CODE1, :DUMMY do
        begin
            if (FK_BOM2 > FK_BOM) then
                suspend;
            if (FK_BOM2 is distinct from FK_BOM) then
            begin
                FK_BOM = FK_BOM2;
                SHORT_CODES = '';
            end
            SHORT_CODES = SHORT_CODES || SHORT_CODE1;
        end
        suspend;
    end