February 19, 2013 at 8:29 am
Hi All
I am using a CTE query for recording a list of purchase order numbers separated by a comma for a HeaderKey under one column as below:
---------------------------------
HeaderKeyPO_list
1000215 101142-00-36, 101820-00-37, 103643-00-37, 104120-00-37, 104530-00-37, 105095-00-37, 105700-00-37, 5062 SD, 99273-00-37, FIAT SAMPLES, S.RAPSON
1000219 102288, 104545
1000220 104321-00-3, 104321-00-4, 104321-00-5, 105715-00-4, 105715-00-5, 105715-003
I have run into the problem of excessive throughput time when the list of purchase orders has more than 15 entries listed in the Freightcontents table. I hope someone has a solution for this issue.
The table definition from where I am reading the list is:
---------------------------
CREATE TABLE [dbo].[FreightContents](
[Description] [char](35) NOT NULL,
[DetailKey] [char](35) NOT NULL,
[HeaderKey] [char](35) NOT NULL,
[ProductCode] [char](20) NOT NULL,
[PurchaseOrderNumber] [char](30) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
----------------------------
The data looks like below:
-------------------------------------------
INSERT INTO FreightContents VALUES('FJ#1000210','104001-00-3')
INSERT INTO FreightContents VALUES('FJ#1000211','SAMPLE' )
INSERT INTO FreightContents VALUES('FJ#1000211','Hydraulic Cylinder' )
INSERT INTO FreightContents VALUES('FJ#1000211','99148/10182' )
INSERT INTO FreightContents VALUES('FJ#1000211','99148/101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','99148' )
INSERT INTO FreightContents VALUES('FJ#1000211','99148' )
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101913' )
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101185' )
INSERT INTO FreightContents VALUES('FJ#1000211','101186' )
INSERT INTO FreightContents VALUES('FJ#1000211','101187' )
INSERT INTO FreightContents VALUES('FJ#1000211','101188' )
INSERT INTO FreightContents VALUES('FJ#1000211','101189' )
INSERT INTO FreightContents VALUES('FJ#1000211','101110' )
INSERT INTO FreightContents VALUES('FJ#1000211','101111' )
INSERT INTO FreightContents VALUES('FJ#1000211','101112' )
INSERT INTO FreightContents VALUES('FJ#1000211','101113' )
INSERT INTO FreightContents VALUES('FJ#1000211','101181' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182')
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101185' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','101186' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','101187' )
INSERT INTO FreightContents VALUES('FJ#1000211','101186' )
INSERT INTO FreightContents VALUES('FJ#1000211','101185' )
INSERT INTO FreightContents VALUES('FJ#1000211','101184' )
INSERT INTO FreightContents VALUES('FJ#1000211','101183' )
The CTE definition is :
------------------------
SELECT HeaderKey, CAST('' AS VARCHAR(8000)), CAST('' AS VARCHAR(8000)), 0
FROM Database1..FreightContents WITH (readuncommitted)
GROUP BY HeaderKey
UNION ALL
SELECT p.HeaderKey, CAST(PO_list + CASE WHEN length = 0 THEN '' ELSE ', ' END + rtrim(PurchaseOrderNumber) AS VARCHAR(8000)),
CAST(PurchaseOrderNumber AS VARCHAR(8000)), length + 1
FROM CTE c INNER JOIN
Database1..FreightContents p WITH (readuncommitted) ON c.HeaderKey = p.HeaderKey
WHERE p.PurchaseOrderNumber > c.product_name)
SELECT HeaderKey, PO_list
FROM (SELECT DISTINCT substring(HeaderKey, 4, 10), PO_list, RANK() OVER (PARTITION BY HeaderKey
ORDER BY length DESC)
FROM CTE) D (HeaderKey, PO_list, rank)
WHERE rank = 1;
February 19, 2013 at 9:14 am
One of the reason you don't have much of answers is that your posted code cannot be executed without error...
INSERT INTO FreightContents VALUES('FJ#1000210','104001-00-3')
...
results with error:
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
So, which columns the above data should be inserted into?
Also, runnable query with CTE would help more than just some cut out of it.
February 19, 2013 at 9:24 am
Thanks for that. I should have tried it myself first:
Here goes the working syntax for the table:
USE [BRI DW]
GO
/****** Object: Table [dbo].[FreightContents] Script Date: 02/19/2013 16:21:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
Create TABLE [dbo].[FreightContents](
[HeaderKey] [char](35) NOT NULL,
[PurchaseOrderNumber] [char](30) NOT NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO FreightContents VALUES('FJ#1000210','104001-00-3')
INSERT INTO FreightContents VALUES('FJ#1000211','SAMPLE' )
INSERT INTO FreightContents VALUES('FJ#1000211','Hydraulic Cylinder' )
INSERT INTO FreightContents VALUES('FJ#1000211','99148/10182' )
INSERT INTO FreightContents VALUES('FJ#1000211','99148/101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','99148' )
INSERT INTO FreightContents VALUES('FJ#1000211','99148' )
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101913' )
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101185' )
INSERT INTO FreightContents VALUES('FJ#1000211','101186' )
INSERT INTO FreightContents VALUES('FJ#1000211','101187' )
INSERT INTO FreightContents VALUES('FJ#1000211','101188' )
INSERT INTO FreightContents VALUES('FJ#1000211','101189' )
INSERT INTO FreightContents VALUES('FJ#1000211','101110' )
INSERT INTO FreightContents VALUES('FJ#1000211','101111' )
INSERT INTO FreightContents VALUES('FJ#1000211','101112' )
INSERT INTO FreightContents VALUES('FJ#1000211','101113' )
INSERT INTO FreightContents VALUES('FJ#1000211','101181' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182')
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101185' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','101186' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','101187' )
INSERT INTO FreightContents VALUES('FJ#1000211','101186' )
INSERT INTO FreightContents VALUES('FJ#1000211','101185' )
INSERT INTO FreightContents VALUES('FJ#1000211','101184' )
INSERT INTO FreightContents VALUES('FJ#1000211','101183' )
Here goes the working CTE:
USE [Database2]
GO
/****** Object: View [dbo].[CTE_POList_UK] Script Date: 02/19/2013 16:26:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[CTE_POList_UK] as
WITH CTE ( HeaderKey, PO_list, product_name, length )
AS ( SELECT HeaderKey, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
FROM Database1..FreightContents with(readuncommitted)
GROUP BY HeaderKey
UNION ALL
SELECT p.HeaderKey, CAST( PO_list +
CASE WHEN length = 0 THEN '' ELSE ', ' END + rtrim(PurchaseOrderNumber) AS VARCHAR(8000) ),
CAST( PurchaseOrderNumber AS VARCHAR(8000)), length + 1
FROM CTE c
INNER JOIN Database1..FreightContents p with(readuncommitted)
ON c.HeaderKey = p.HeaderKey
WHERE p.PurchaseOrderNumber > c.product_name )
SELECT HeaderKey, PO_list
FROM ( SELECT Distinct substring(HeaderKey,4,10), PO_list,
RANK() OVER ( PARTITION BY HeaderKey ORDER BY length DESC )
FROM CTE ) D ( HeaderKey, PO_list, rank )
WHERE rank = 1 ;
GO
------------------------------
Once created it shows the results upon right clicking the query in object explorer and selecting
February 20, 2013 at 3:27 am
I still could not run your CTE due to type mismatch error. (I guess when you do CHAR concatenation in the recursive CTE you should cast to NVARCHAR). However, looks like your solution will not perform good enough anyway.
Try this instead:
SELECT HeaderKey
,STUFF((SELECT ', ' + RTRIM(CAST(f2.PurchaseOrderNumber AS VARCHAR(30)))
FROM FreightContents AS f2
WHERE f2.HeaderKey = f1.HeaderKey
ORDER BY PurchaseOrderNumber
FOR XML PATH ('')), 1, 2, '') AS PO_list
FROM FreightContents AS f1
GROUP BY HeaderKey
February 20, 2013 at 3:35 am
Thanks Indeed, This works perfectly regardless of the No. of rows in FreightContents. 🙂 Good day
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply