Declare @count int=0; ;with CTE(RM_PART_NO ,FG_PART_NO,LEVEL) as ( select RM_PART_NO ,FG_PART_NO,0 as LEVEL from BOM_MASTER (nolock) where FG_PART_NO='29049260' and WH_CODE='0021' union all select BOM.RM_PART_NO, CTE.RM_PART_NO,LEVEL+1 from CTE as CTE inner join BOM_MASTER BOM (nolock) on BOM.FG_PART_NO=CTE.RM_PART_NO where WH_CODE='0021' ) select @count=count(RM_PART_NO) From CTE where RM_PART_NO=( SELECT SEWS_PART_NO FROM PRODUCT_MASTER with(nolock) WHERE ITEM_NO = ( SELECT ITEM_NO FROM INV_SERIAL with(nolock) WHERE SERIAL_NO = 'BA00586147' )) group by CTE.LEVEL order by LEVEL option(maxrecursion 0) if (@count=0) begin select 'FG & RM PART MISMATCH ' end
Declare @count int=0; ;with CTE(RM_PART_NO ,FG_PART_NO,LEVEL) as ( select RM_PART_NO ,FG_PART_NO,0 as LEVEL from BOM_MASTER (nolock) where FG_PART_NO='29049260' and WH_CODE='0021' union all select BOM.RM_PART_NO, CTE.RM_PART_NO,LEVEL+1 from CTE as CTE inner join BOM_MASTER BOM (nolock) on BOM.FG_PART_NO=CTE.RM_PART_NO where WH_CODE='0021' ) select @count=count(RM_PART_NO) From CTE CTE inner join PRODUCT_MASTER pm (nolock) on CTE.RM_PART_NO=pm.SEWS_PART_NO inner join INV_SERIAL iv (nolock) on pm.ITEM_NO=iv.ITEM_NO where iv.serial_NO='BA00586147' group by CTE.LEVEL order by LEVEL option(maxrecursion 0) if (@count=0) begin select 'FG & RM PART MISMATCH ' end
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)