Anonymous

WITH
	O_FIELDS(
		ZONE_NUM,
		DETAIL_LINE_ID, 
		CUSTOMER, 
		DESTNAME, 
		FREIGHT_BILL, 
		STATUS, 
		WEIGHT, 
		START_ZONE, 
		END_ZONE, 
		BILL_DATE, 
		DELIVER_BY, 
		ACTUAL_DELIVERY
	) AS (
		SELECT 
		case
			when O.END_ZONE = 'ONBUR' then '1'
			when O.END_ZONE = 'ONHAM' then '1'
			when O.END_ZONE = 'ONSTON' then '1'
			when O.END_ZONE = 'ONBRAM' then '2'
			when O.END_ZONE = 'ONCAM' then '2'
			when O.END_ZONE = 'ONETO' then '2'
			when O.END_ZONE = 'ONGEOR' then '2'
			when O.END_ZONE = 'ONEGU' then '2'
			when O.END_ZONE = 'ONMIS' then '2'
			when O.END_ZONE = 'ONNAN' then '2'
			when O.END_ZONE = 'ONREX' then '2'
			when O.END_ZONE = 'ONBOL' then '3'
			when O.END_ZONE = 'ONCON' then '3'
			when O.END_ZONE = 'ONKIT' then '3'
			when O.END_ZONE = 'ONRIC' then '3'
			when O.END_ZONE = 'ONSCA' then '3'
			when O.END_ZONE = 'ONWOOD' then '3'
			when O.END_ZONE = 'ONWOODB' then '3'
			when O.END_ZONE = 'ONHARR' then '4'
			when O.END_ZONE = 'ONKIN' then '4'
			when O.END_ZONE = 'ONKINGS' then '4'
			when O.END_ZONE = 'ONNEW' then '4'
			when O.END_ZONE = 'ONSTT' then '4'
			when O.END_ZONE = 'ONWIN' then '4'
			when O.END_ZONE = 'ONUTOP' then '4'
			when O.END_ZONE = 'MIMELV' then '4'
		else  O.END_ZONE end as ZONE_NUM,
		O.DETAIL_LINE_ID,
		O.CUSTOMER, 
		O.DESTNAME, 
		O.BILL_NUMBER AS FREIGHT_BILL,
		O.CURRENT_STATUS AS STATUS, 
		O.WEIGHT, 
		O.START_ZONE, 
		O.END_ZONE,
		O.BILL_DATE, 
		O.DELIVER_BY, 
		O.ACTUAL_DELIVERY
		FROM TLORDER O
		WHERE CUSTOMER IN ('CPR-CONTRA','CPZONE4')
		AND O.BILL_DATE BETWEEN TIMESTAMP (CAST(? AS CHAR(14))) AND (TIMESTAMP (CAST(? AS CHAR(14))) + 1 month - 1 second)
		AND INTERFACE_STATUS_F IS NOT NULL
		ORDER BY FREIGHT_BILL
	),
	T_SUBSET(key, val, rnum) AS
	( 
		SELECT DETAIL_NUMBER , TRACE_NUMBER, row_number() over(partition by DETAIL_NUMBER) 
		FROM Trace 
		WHERE DETAIL_NUMBER IN (
			SELECT DETAIL_LINE_ID FROM O_FIELDS
		)
		AND TRACE_TYPE='1'
	),
	T_RECURSE(key, str, cnt, cnt_max) AS 
	( 
		SELECT key, VARCHAR('', 1000), 0, MAX(rnum) FROM T_SUBSET GROUP BY key
	UNION ALL
		SELECT T_RECURSE.key, T_RECURSE.str || RTRIM(CHAR(T_SUBSET.val)) || '  ', T_RECURSE.cnt + 1, T_RECURSE.cnt_max
		FROM T_SUBSET, T_RECURSE
		WHERE T_SUBSET.key = T_RECURSE.key AND
		T_SUBSET.rnum = T_RECURSE.cnt + 1 AND
		T_RECURSE.cnt < T_RECURSE.cnt_max 
	)
SELECT 
	O.ZONE_NUM,
	O.CUSTOMER, 
	O.DESTNAME, 
	O.FREIGHT_BILL, 
	O.STATUS, 
	O.WEIGHT, 
	O.START_ZONE, 
	O.END_ZONE, 
	O.BILL_DATE, 
	O.DELIVER_BY, 
	O.ACTUAL_DELIVERY,
	T.STR AS CARS
FROM O_FIELDS O 
LEFT OUTER JOIN T_RECURSE T ON O.DETAIL_LINE_ID = T.KEY
WHERE (T.CNT_MAX = T.CNT OR T.CNT IS NULL)
ORDER BY ZONE_NUM, FREIGHT_BILL