SQL Query for Production Order Layout

[shikin]

Hi experts. I have one SQL Query that i want to use it in my crystal reports but there are some value appeared to be wrong. Here is my code and the current output.

SELECT distinct
  '1st Row' as 'type',
  t1.DocEntry,
  t0.bomCode as 'bomCode',
  t4.ItemCode,
  t2.DocNum, 
  t2.DocEntry,
  t4.componentcode as 'SOComponentCode',
  t4.Quantity as 'SoQty',
  --t4.LineNum as 'SOLineNum',
  --t0.ComponentCode as 'BOMComCode',
  t0.Quantity as 'BomQty',
  CASE
    when isnull(t4.[ComponentCode],'')=''then t0.ComponentCode
	ELSE t4.ComponentCode 
  END AS ComponentCode
FROM owor t1
  left join FT_BOM_SUBCOM_ROW1 t0 on t1.ItemCode=t0.BomCode
  left join ORDR t2 on t1.OriginNum=t2.DocNum
  left join rdr1 t3 on t2.DocEntry=t3.DocEntry --and t1.U_LineNum=t3.LineNum
  left join FT_CONFIG_SUBCOM_ROW1 t4 on t1.ItemCode=t4.ItemCode--and t4.DocEntry=t2.DocEntry --and t4.ComponentCode=t0.ComponentCode
where t1.DocNum='407791'

union all

SELECT distinct
  '2nd Row' as 'type',
  t1.DocEntry,
  t0.bomCode as 'bomCode',
  t4.ItemCode,
  t2.DocNum, 
  t2.DocEntry,
  t4.componentcode as 'SOComponentCode',
  t4.Quantity as 'SoQty',
  --t4.LineNum as 'SOLineNum',
  --t0.ComponentCode as 'BOMComCode',
  t0.Quantity as 'BomQty',
  CASE
    when isnull(t4.[ComponentCode],'')=''then t0.ComponentCode
	ELSE t4.ComponentCode 
  END AS ComponentCode
FROM owor t1
  left join FT_BOM_SUBCOM_ROW2 t0 on t1.ItemCode=t0.BomCode
  left join ORDR t2 on t1.OriginNum=t2.DocNum
  left join rdr1 t3 on t2.DocEntry=t3.DocEntry --and t1.U_LineNum=t3.LineNum
  left join FT_CONFIG_SUBCOM_ROW2 t4 on t1.ItemCode=t4.ItemCode
where t1.DocNum='407791'

union all

SELECT distinct
  '3rd Row' as 'type',
  t1.DocEntry,
  t0.bomCode as 'bomCode',
  t4.ItemCode,
  t2.DocNum, 
  t2.DocEntry,
  t4.componentcode as 'SOComponentCode',
  t4.Quantity as 'SoQty',
  --t4.LineNum as 'SOLineNum',
  --t0.ComponentCode as 'BOMComCode',
  t0.Quantity as 'BomQty',
  CASE
    when isnull(t4.[ComponentCode],'')='' then t0.ComponentCode
	ELSE t4.ComponentCode 
  END AS ComponentCode
  FROM owor t1
  left join FT_BOM_SUBCOM_ROW3 t0 on t1.ItemCode=t0.BomCode
  left join ORDR t2 on t1.OriginNum=t2.DocNum
  left join rdr1 t3 on t2.DocEntry=t3.DocEntry --and t1.U_LineNum=t3.LineNum
  left join FT_CONFIG_SUBCOM_ROW3 t4 on t1.ItemCode=t4.ItemCode
  where t1.DocNum='407791'

the results:

The ComponentCode for 3rd row types, it pickup value from t0.componentcode which is WRONG. The value on ComponentCode column should follow whatever SOComponentCode column (t4.componentcode) is.

Can someone help to amend my code please. I really stuck right now.

Thank you in advance!!!

[DellSC]

You had a number of unnecessary tags on your question, which I have removed. The two tags that I've left are the ones that will get you the best answer for you question.

For more information about how to use the Crystal Reports tags see Using SAP Crystal Reports Tags | SAP Blogs.

Also, for information about using Commands in Crystal, see Best Practices When Using Commands with Crystal Reports.

-Dell

[LoHa]

Hi Nur,

this query gives you all lines from production order (including stages). Feel free to modify it on your own.

/*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

[clas_hortien]

Hello,

but this is exactly what you are selecting (row3 part of the selection):

....
  CASE
    when isnull(t4.[ComponentCode],'')='' then t0.ComponentCode
	ELSE t4.ComponentCode 
  END AS ComponentCode
....

When the t4.ComponentCode is empty than take t0.ComponentCode. If you always want t4.ComponentCode for the row3 selection, then replace this in the row3 part with:

....
  t4.ComponentCode AS ComponentCode
....

Best regards

Clas