Order without work using "WITH" clause
I am using Firebird database.
I have below SQL which concatenates ShortCode column data but without ordering according to ORDER_NUMBER column in ABC table under WITH 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
Thanks in advance.
Regards.
Vishal
+3
source to share
3 answers
I resolved the issue on the 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
+2
source to share
ORDER BY doesn't work in a generic table expression, and your joining TBL_SHORT_CODE won't do it anyway anyway. If your order is to just order the withdrawal, then change your script to order in your final request:
With TBL_SHORT_CODE (SHORT_CODE, FK_KEY) As
(
SELECT 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
GROUP BY XYZ.SHORT_CODE, ABC.FK_KEY
)
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
+2
source to share
Try the following,
with TBL_SHORT_CODE (SHORT_CODE, FK_KEY, FK_BOM, ORDER_NUMBER) As
(
SELECT Distinct(XYZ.SHORT_CODE) As SHORT_CODE
, ABC.FK_KEY, ABC.FK_BOM, 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
)
SELECT LIST(Distinct(TBL_SHORT_CODE.SHORT_CODE), '' ), A.FK_BOM
From TBL_SHORT_CODE A
where A.FK_BOM = '{009DA0F8-51EE-4207-86A6-7E18F96B983A}'
And ABC.STATUS_CODE = 1
Group By A.FK_BOM
ORDER BY TBL_SHORT_CODE.ORDER_NUMBER
0
source to share