asked on

Add sort order to recursive query

I have a recursive MSSQL query which generates a hierarchy of batches in our system. What happens is that if we receive, for example, batch # 1, and we process this, the batch number changes to the next available number, say # 4. This batch could be further processed. Sometimes these processes result in multiple output batches. Batch # 4 could be split into batch # 11 and # 12. And so on and so forth.
The query extracts each batch and indicates of which batch it is a child by setting a level. So batch # 1 would be level 0, batch # 4 would be level 1, batch # 11 and 12 would both be level 2. This all works as expected, albeit a bit slow due to the sheer number of batches.
However, in the report (SSRS 2005) I need to display this parent - child relationship correctly. The issue here is that if I sort on the "level" column, it would not correctly show the relationships as it would first show all batches with level 0, then level 1, then level 2, and so on. If I have batch # 2 and batch # 3 with level 0 as well, it would display batch number 1, 2 and 3 first before showing batch # 4, which does not show that 4 is a child of 1.
Unfortunately It is not necessarily the case that the output batch numbers are always higher than the input batch numbers, as these numbers are taken from different ranges depending on the circumstances. So I could have an input batch of # 4002134 and an output batch of # 1014324 for example. So sorting by batch number is also not an option.
Ideally I'd like to introduce a sorting order into the table, but I don't know how I can do this other than using a cursor and going through it line by line and comparing it to the previous entry. If I do this, the query becomes too slow to handle.

The recursive part of the procedure looks like this:

With A

([Level],
Product_number,
Original_batch_number,
Previous_batch_number,
Batch_number,
Germ
)

AS

(
select	0 as 'Level',
		ld.Article_number as 'Product_number',
		ld.Original_batch_number,
		ld.Previous_batch_number,
		ld.batch_number,
		ld.Determination_1 as 'Germ'
from	REP_ABS_DATA.rs.Lots_detail ld
		inner join REP_ABS_DATA.rs.Products p
			on ld.Article_number = p.Product_number
where	ld.Batch_number = ld.Original_batch_number
and		p.Parentline_indicator = 0
and		ld.Crop_code IN (Select Val From fn_Split(@Crop_code))

union all

Select	A.[Level] + 1,
		ld.Article_number as 'Product_number',
		ld.original_batch_number,
		ld.Previous_batch_number,
		ld.batch_number,
		ld.determination_1 as 'Germ'
from	REP_ABS_DATA.rs.Lots_detail ld
		inner join A
			on ld.Previous_batch_number = A.batch_number
)

Open in new window

My expected output would be something like this:
Batch #:
1
4
11
12
2
3
16
17
19
5

etc.

I'm not sure if I'm explaining this clearly, if not feel free to ask for more specific details. I'm hoping someone here can help me with this.
Thanks.

ste5an

Please post a concise and complete example. This includes table DDL and sample data INSERT statements. btw, I would guess the slow performance is caused by your usage of a user defined function. So this may at least make it faster:

DECLARE @Crop_Code TABLE ( Crop_Code INT );

INSERT  INTO @Crop_Code
        SELECT  DISTINCT
                Val
        FROM    fn_Split(@Crop_Code);

WITH    A ( [Level], Product_number, Original_batch_number, Previous_batch_number, Batch_number, Germ )
          AS ( SELECT   0 AS 'Level' ,
                        ld.Article_number AS 'Product_number' ,
                        ld.Original_batch_number ,
                        ld.Previous_batch_number ,
                        ld.batch_number ,
                        ld.Determination_1 AS 'Germ'
               FROM     REP_ABS_DATA.rs.Lots_detail ld
                        INNER JOIN REP_ABS_DATA.rs.Products p ON ld.Article_number = p.Product_number
                        INNER JOIN @Crop_Code CC ON CC.Crop_Code = ld.Crop_Code
               WHERE    ld.Batch_number = ld.Original_batch_number
                        AND p.Parentline_indicator = 0
               UNION ALL
               SELECT   A.[Level] + 1 ,
                        ld.Article_number AS 'Product_number' ,
                        ld.Original_batch_number ,
                        ld.Previous_batch_number ,
                        ld.Batch_number ,
                        ld.determination_1 AS 'Germ'
               FROM     REP_ABS_DATA.rs.Lots_detail ld
                        INNER JOIN A ON ld.Previous_batch_number = A.Batch_number
             )
    SELECT  *
    FROM    A;

Open in new window

Koen Van Wielink

ASKER

Hi Ste5an,

Thanks for your reply. The statement below should contain enough sample data to illustrate what I'm working with I think:

create table lots_detail
(	Article_number int
,	Original_batch_number int
,	Previous_batch_number int
,	batch_number int
,	Determination_1 numeric(5,2));

insert into lots_detail
values		(7055,	4077607	,0			,4077607	,79.00)
		,	(7055,  4077612	,4086720	,4089813	,96.00)
		,	(7055,	2247862	,1249536	,1249543	,NULL)
		,	(7055,	2247862	,1249536	,1249542	,97.50)
		,	(7055,	2247862	,2247862	,1249537	,0.00)
		,	(7055,	2247862	,2247862	,1249536	,97.50)
		,	(7055,	2247862	,0			,2247862	,0.00)
		,	(7055,	4077614	,4077614	,4086715	,NULL)
		,	(7055,	4077614	,4077614	,4086714	,96.00)
		,	(7055,	4077614	,0			,4077614	,82.00)
		,	(7055,	4077612	,4077612	,4086721	,NULL)
		,	(7055,	4077612	,4077612	,4086720	,96.50)
		,	(7055,	4077612	,0			,4077612	,87.00)
		,	(7055,	4077607	,4077607	,4086711	,NULL)
		,	(7055,	4077607	,4077607	,4086710	,90.00);
		
With A

([Level],
Product_number,
Original_batch_number,
Previous_batch_number,
Batch_number,
Germ
)

AS

(
select	0 as 'Level',
		ld.Article_number as 'Product_number',
		ld.Original_batch_number,
		ld.Previous_batch_number,
		ld.batch_number,
		ld.Determination_1 as 'Germ'
from	dbo.Lots_detail ld
where	ld.Batch_number = ld.Original_batch_number

union all

Select	A.[Level] + 1,
		ld.Article_number as 'Product_number',
		ld.original_batch_number,
		ld.Previous_batch_number,
		ld.batch_number,
		ld.determination_1 as 'Germ'
from	dbo.Lots_detail ld
		inner join A
			on ld.Previous_batch_number = A.batch_number
)

Select *
from A
-- The order statement here is not giving me the desired result.
order by	a.Original_batch_number
		,	a.Previous_batch_number
		,	a.Level;

drop table lots_detail;

Open in new window

Attached is an Excel file with the desired sorting order of this data. I don't think this can be achieved with the existing fields alone, and the only way to get it done is to add an extra sorting sequence. But like I said, other than going through the resulting data set line by line I'm not sure how this can be achieved.
Desired-sort-order.xlsx

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Koen Van Wielink

ASKER

Thanks a million! Works exactly as required.