Query to list components and unit price

[RahF]

Hi Experts

I am trying to write a query to list all the components in a production order and the unit price from the ITT1 table
The results is multiplying by the number of rows in the BOM. It maybe because there are multiple phantom part numbers in the BOM
Can someone please point me in the right direction

SELECT 
T1.[ItemCode], 
T1.[ItemName], 
T1.[PlannedQty], 
T2.[Price] 
FROM OWOR T0 
INNER JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
INNER JOIN ITT1 T2 ON T1.[ItemCode] = T2.[Code] 
WHERE T0.[DocNum]  = [%0]

Regards

Rah

[LoHa]

Hi 

you can try this

/*Getting all lines from Production Order*/
/*as shown in the grid*/
/*by OWOR.DocEntry*/

DECLARE @Stage AS nvarchar(15)
DECLARE @Row AS INT = 0

/*Add OWOR.DocEntry here*/
DECLARE @DocEntry AS  INT = 22275

/*Check if TempTable already exist*/
IF OBJECT_ID ('tempdb..#Lines','U') IS NOT NULL
/*Else*/
DROP TABLE [dbo].[#Lines]
/*Create new Table*/
CREATE TABLE [#Lines]
(
	[Typ] nvarchar(max),
	[Code]  nvarchar(max),
	[Dscrption]  nvarchar(max),
	[VisOrder] nvarchar(max),
	[LineNum]  nvarchar(max)
)
/*Check for Stages*/
IF 
(
	SELECT
		TOP 1
		[StageID]
	FROM 
		[WOR4]
	WHERE
		[WOR4].[DocEntry] = @DocEntry
)
is not null

BEGIN
/*Set Cursor and get all Stages*/
DECLARE [StageCursor] CURSOR
FOR
	SELECT
		[StageID]
	FROM 
		[WOR4]
	WHERE
		[WOR4].[DocEntry] = @DocEntry
	ORDER BY 
		[WOR4].[StgEntry] ASC
;

/*Start Cursor*/
OPEN [StageCursor]
;

FETCH NEXT FROM [StageCursor] INTO 
    @Stage

WHILE @@FETCH_STATUS = 0
    BEGIN
INSERT INTO [#Lines] ([Typ],[Code],[Dscrption],[VisOrder],[LineNum]) 
	/*Getting Stages*/
	SELECT 
		  'Routenabschnitt'
		, [ORST].[Code]
		, ''
		, ''
		, ''
	FROM 
		[WOR4]
		INNER JOIN [ORST] ON 
			[ORST].[AbsEntry] = [WOR4].[StageId]
	WHERE
		[WOR4].[DocEntry] = @DocEntry 
			AND 
				[WOR4].[StgEntry] = @Stage

INSERT INTO [#Lines] ([Typ],[Code],[Dscrption],[VisOrder],[LineNum])
	/*Getting Lines according to Stage*/
	SELECT
		  CASE WHEN [WOR1].[ItemType] = -18 THEN 'Text'
		       WHEN [WOR1].[ItemType] = 290 THEN 'Ressource'
			   WHEN [WOR1].[ItemType] = 4 THEN 'Artikel'
		  ELSE 'Unbekannt' END AS [Typ]
		, CASE WHEN [WOR1].[ItemType] = -18 THEN '' ELSE [WOR1].[ItemCode] END AS [Code]
		, CASE WHEN [WOR1].[ItemType] = -18 THEN [Wor1].[LineText] 
		       WHEN [WOR1].[ItemType] = 290 THEN [ORSC].[ResName]
			   WHEN [WOR1].[ItemType] = 4 THEN [OITM].[ItemName]
		  ELSE 'Unbekannt' END AS [Dscrption]
		, [WOR1].[VisOrder]
		, [WOR1].[LineNum]
	FROM
		[WOR1]
	LEFT JOIN [OITM] ON 
		[OITM].[ItemCode] = [WOR1].[ItemCode]
	LEFT JOIN [ORSC] ON 
		[ORSC].[VisResCode] = [WOR1].[ItemCode]
	WHERE 
		[WOR1].[DocEntry] = @DocEntry 
			AND 
				[WOR1].[StageId] = @Stage
	ORDER BY 
		[WOR1].[VisOrder]

			FETCH NEXT FROM [StageCursor] INTO 
				@Stage;
		END;

CLOSE [StageCursor];

DEALLOCATE StageCursor;
END;
ELSE
/*If there are no Stages*/
BEGIN
INSERT INTO [#Lines] ([Typ],[Code],[Dscrption],[VisOrder],[LineNum])
	SELECT
		  CASE WHEN [WOR1].[ItemType] = -18 THEN 'Text'
		       WHEN [WOR1].[ItemType] = 290 THEN 'Ressource'
			   WHEN [WOR1].[ItemType] = 4 THEN 'Artikel'
		  ELSE 'Unbekannt' END AS [Typ]
		, CASE WHEN [WOR1].[ItemType] = -18 THEN '' ELSE [WOR1].[ItemCode] END AS [Code]
		, CASE WHEN [WOR1].[ItemType] = -18 THEN [Wor1].[LineText] 
		       WHEN [WOR1].[ItemType] = 290 THEN [ORSC].[ResName]
			   WHEN [WOR1].[ItemType] = 4 THEN [OITM].[ItemName]
		  ELSE 'Unbekannt' END AS [Dscrption]
		, [WOR1].[VisOrder]
		, [WOR1].[LineNum]
	FROM
		[WOR1]
	LEFT JOIN [OITM] ON 
		[OITM].[ItemCode] = [WOR1].[ItemCode]
	LEFT JOIN [ORSC] ON 
		[ORSC].[VisResCode] = [WOR1].[ItemCode]
	WHERE 
		[WOR1].[DocEntry] = @DocEntry 
	ORDER BY 
		[WOR1].[VisOrder]
END

/*Getting Data - Showing Lines as in Production Order*/
SELECT 
  ROW_NUMBER () OVER (ORDER BY (select 0)) AS [#]
, * 
FROM [#Lines]
DROP TABLE [#Lines]

regards Lothar