We help IT Professionals succeed at work.

T-SQL: Joining Tables

Software Engineer
Software Engineer asked
on
Hi:

Below are three blocks of T-SQL code, each followed by an image of the records retrieved from the specific block above it.

I'm trying to develop a manufacturing "Work in Process" report showing data from the PNMAST table  where the data in the BM010115.CPN_I field is in the BM010115.PPN_I field and BM010115.SUBCAT_I = 1.

Please help me modify the syntax of the first T-SQL code block - after its UNION ALL clause - to accommodate this.

The main thing is that I don't want 506 as the FormulaId of the PNMAST table.

Finally, please keep in mind that the images that I have attached are simply the first few records of the tables and not all of the hundreds or even thousands of records in those tables/.

Thank you!

Software Engineer


select NULL as [LOTSEQNBR], FG.BatchNo as [FGBATCH], FG.FormulaId as [FG], FG.[Labor Cost] as [FGLABOR], FG.[OverHead Cost] as [FGOH], FG.[Material Cost] as [FGMAT], 
FG.[Labor Cost] + FG.[OverHead Cost] + FG.[Material Cost] as [FGCOST],
NULL as [FORMULA],
NULL as [FORMULAMAT], NULL AS [FORUMLALABOR], NULL AS [FORMULAOH],
NULL AS [Component], NULL as [LOT], NULL as [LOTQTY], NULL as [LOTUNITCOST]
from BM_View_Dashboard_ProductionCostAnalysis FG
INNER JOIN BM010115 BOM ON FG.FormulaId = BOM.PPN_I
where FG.FormulaId = '506' and FG.BatchNo = '50418G12D' and BOM.SUBCAT_I = 1
UNION ALL
select DISTINCT NULL as [LOTSEQNBR], NULL as [FGBATCH], NULL as [FG], NULL as [FGLABOR], NULL as [FGOH], NULL as [FGMAT], NULL as [FGCOST], BOM.PPN_I as [FORMULA],
PROD.BatchMatCostActual as [FORMULAMAT], PROD.BatchLabortCostActual AS [FORMULALABOR], PROD.BatchOverHeadCostActual AS [FORMULAOH],
--CASE WHEN PROD.BatchMatCostActual = 0 and PROD.BatchLabortCostActual = 0 and 
--PROD.BatchOverHeadCostActual = 0
--THEN 
INV.ITEMNMBR as [Component], 
NULL as [LOT], NULL as [LOTQTY], NULL as [LOTUNITCOST]
from PNMAST PROD
INNER JOIN BM010115 BOM ON PROD.FormulaId = BOM.PPN_I
INNER JOIN BMMTrxHdr BMHDR ON PROD.BatchNo = BMHDR.BMDocNo
INNER JOIN BMMTrxDtl BMDTL ON BMHDR.Dex_Row_Id = BMDTL.RefDex_Row_Id
INNER JOIN IV30300 INV ON BMDTL.LNSeqNo = INV.LNSEQNBR 
where --PROD.FormulaId IN ('506N')
BMHDR.IVDocNmbr IN ('081007', '081008', '081009')
and INV.LNSEQNBR = 16384 and INV.ITEMNMBR IN ('506N', '1880', '1355') 
and INV.DOCTYPE = 1 --and INV.DOCNUMBR IN ('081007', '081008', '081009')

Open in new window

WIP
select * from BM010115 where PPN_I = '506'

Open in new window

BOM
select * from PNMAST where FormulaId = '506N'

Open in new window

PNMAST
Comment
Watch Question

View Solution Only

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
You need to provide data as CREATE TABLE with INSERT statements.  A splat of data on the screen is, frankly, useless to us, since we can't write, nor test, SQL against it.
Software Engineer

Author

Commented:
Hi Scott:

Please provide me the syntax, on how to conduct this.

Thank you!

John
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
CREATE TABLE dbo.BM010115 (
    LOTSEQNBR int NULL,
    FGBATCH varchar(30) NULL, ...
INSERT INTO dbo.BM010115 VALUES
    (NULL, '50418G12D', ...).
    (NULL, NULL, ..., 506, ...),
    ...
Software Engineer

Author

Commented:
Well, that table is already created.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
On your system, not on ours.

Good luck with your q, I will allow others to follow up from this point.
Software Engineer

Author

Commented:
Here is the BM010115 CREATE TABLE script:

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[BM010115](
	[PPN_I] [char](31) NOT NULL,
	[CPN_I] [char](31) NOT NULL,
	[BOMCAT_I] [smallint] NOT NULL,
	[BOMNAME_I] [char](15) NOT NULL,
	[BOMTYPE_I] [smallint] NOT NULL,
	[BOMSEQ_I] [int] NOT NULL,
	[POSITION_NUMBER] [smallint] NOT NULL,
	[SUBCAT_I] [smallint] NOT NULL,
	[SUBNAME_I] [char](15) NOT NULL,
	[SUB_REV_LEVEL_SEQ_I] [int] NOT NULL,
	[QUANTITY_I] [numeric](19, 5) NOT NULL,
	[OPTPERCENT_I] [int] NOT NULL,
	[SCRAPPERCENT_I] [int] NOT NULL,
	[FLOORSTOCK_I] [tinyint] NOT NULL,
	[EFFECTIVEINDATE_I] [datetime] NOT NULL,
	[EFFECTIVEOUTDATE_I] [datetime] NOT NULL,
	[ALTERNATE_I] [tinyint] NOT NULL,
	[ALTERNATEPARTFOR_I] [char](31) NOT NULL,
	[ALT_FOR_BOM_SEQ_I] [int] NOT NULL,
	[LEADTIMEOFFSET_I] [numeric](19, 5) NOT NULL,
	[LEADTIMEOFFSETINC_I] [smallint] NOT NULL,
	[BOMUSERDEF1_I] [char](31) NOT NULL,
	[BOMUSERDEF2_I] [char](31) NOT NULL,
	[BOMSINGLELOT_I] [tinyint] NOT NULL,
	[BOMENGAPPROVAL_I] [tinyint] NOT NULL,
	[WCID_I] [char](11) NOT NULL,
	[LOCNCODE] [char](11) NOT NULL,
	[BACKFLUSHITEM_I] [tinyint] NOT NULL,
	[CHANGEDATE_I] [datetime] NOT NULL,
	[USERID] [char](15) NOT NULL,
	[OPTIONED_ITEM_I] [tinyint] NOT NULL,
	[ACTUAL_CONSUMED_CHECK_I] [tinyint] NOT NULL,
	[FIXED_QTY_I] [numeric](19, 5) NOT NULL,
	[UOFM] [char](9) NOT NULL,
	[U_Of_M_2] [char](9) NOT NULL,
	[QTYBSUOM] [numeric](19, 5) NOT NULL,
	[OFFSET_FROM_I] [smallint] NOT NULL,
	[MFGNOTEINDEX_I] [numeric](19, 5) NOT NULL,
	[MFGNOTEINDEX2_I] [numeric](19, 5) NOT NULL,
	[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PKBM010115] PRIMARY KEY CLUSTERED 
(
	[PPN_I] ASC,
	[BOMCAT_I] ASC,
	[BOMNAME_I] ASC,
	[CPN_I] ASC,
	[BOMSEQ_I] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BM010115]  WITH CHECK ADD CHECK  ((datepart(hour,[CHANGEDATE_I]) = 0 and datepart(minute,[CHANGEDATE_I]) = 0 and datepart(second,[CHANGEDATE_I]) = 0 and datepart(millisecond,[CHANGEDATE_I]) = 0))
GO

ALTER TABLE [dbo].[BM010115]  WITH CHECK ADD CHECK  ((datepart(hour,[EFFECTIVEINDATE_I]) = 0 and datepart(minute,[EFFECTIVEINDATE_I]) = 0 and datepart(second,[EFFECTIVEINDATE_I]) = 0 and datepart(millisecond,[EFFECTIVEINDATE_I]) = 0))
GO

ALTER TABLE [dbo].[BM010115]  WITH CHECK ADD CHECK  ((datepart(hour,[EFFECTIVEOUTDATE_I]) = 0 and datepart(minute,[EFFECTIVEOUTDATE_I]) = 0 and datepart(second,[EFFECTIVEOUTDATE_I]) = 0 and datepart(millisecond,[EFFECTIVEOUTDATE_I]) = 0))
GO

Open in new window

Software Engineer

Author

Commented:
Here is the CREATE TABLE script for PNMAST:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PNMAST](
	[BatchNo] [nvarchar](15) NOT NULL,
	[BatchType] [char](1) NOT NULL,
	[FormulaId] [nvarchar](20) NULL,
	[ItemKey] [nvarchar](80) NULL,
	[LocationKey] [nvarchar](15) NULL,
	[Description] [nvarchar](80) NULL,
	[Status] [nvarchar](1) NOT NULL,
	[DrawFromLocation] [nvarchar](15) NULL,
	[PostToLocation] [nvarchar](15) NULL,
	[BatchTicketDate] [datetime] NULL,
	[Notes] [nvarchar](3500) NULL,
	[CustKey] [nvarchar](25) NULL,
	[EntryDate] [datetime] NULL,
	[SchStartDate] [datetime] NULL,
	[SchCompletionDate] [datetime] NULL,
	[ActStartDate] [datetime] NULL,
	[ActCompletionDate] [datetime] NULL,
	[FixedCostLaborID] [nvarchar](20) NULL,
	[FixedCostOverheadID] [nvarchar](20) NULL,
	[FixedCostHours] [float] NULL,
	[FixedCostAmount] [decimal](22, 6) NULL,
	[FixedOverheadAmount] [decimal](22, 6) NULL,
	[VariableCostLaborID] [nvarchar](20) NULL,
	[VariableCostOverheadID] [nvarchar](20) NULL,
	[VariableLaborAmount] [decimal](22, 6) NULL,
	[VariableOverheadAmount] [decimal](22, 6) NULL,
	[SetupCostLabourId] [nvarchar](20) NULL,
	[SetupCostOverheadID] [nvarchar](20) NULL,
	[SetupCostHours] [float] NULL,
	[SetupLaborAmount] [decimal](22, 6) NULL,
	[SetupOverheadAmount] [decimal](22, 6) NULL,
	[SpecGravOverride] [float] NULL,
	[SysDocId] [int] NULL,
	[ProcessCellId] [nvarchar](20) NULL,
	[RunHours] [float] NULL,
	[LastLineSeqNo] [int] NULL,
	[WIPAccount] [nvarchar](80) NULL,
	[WIPBalance] [decimal](22, 6) NULL,
	[BatchMatCostStd] [decimal](22, 6) NULL,
	[BatchMatCostActual] [decimal](22, 6) NULL,
	[BatchLaborCostStd] [decimal](22, 6) NULL,
	[BatchLabortCostActual] [decimal](22, 6) NULL,
	[BatchOverHeadCostStd] [decimal](22, 6) NULL,
	[BatchOverHeadCostActual] [decimal](22, 6) NULL,
	[TotalByPCost] [decimal](22, 6) NULL,
	[TotalWeightUsed] [float] NULL,
	[TotalVolumetUsed] [float] NULL,
	[TotalFGWeightYielded] [float] NULL,
	[TotalFGVolumeYielded] [float] NULL,
	[TotalByWeightYielded] [float] NULL,
	[TotalByVolumeYielded] [float] NULL,
	[BatchVolume] [float] NULL,
	[BatchWeight] [float] NULL,
	[OrderWeight] [float] NULL,
	[OrderVolume] [float] NULL,
	[TotalStdCost] [decimal](22, 6) NULL,
	[TotalActualCost] [decimal](22, 6) NULL,
	[SONumber] [nvarchar](50) NULL,
	[OldLossFactor] [float] NULL,
	[OldLossConstant] [float] NULL,
	[AplDate] [datetime] NULL,
	[FMRevisionNumber] [nvarchar](10) NULL,
	[RecUserid] [nvarchar](15) NULL,
	[Recdate] [datetime] NULL,
	[User1] [nvarchar](225) NULL,
	[User2] [nvarchar](225) NULL,
	[User3] [nvarchar](225) NULL,
	[User4] [nvarchar](50) NULL,
	[User5] [nvarchar](50) NULL,
	[User6] [datetime] NULL,
	[User7] [float] NULL,
	[User8] [float] NULL,
	[User9] [decimal](22, 6) NULL,
	[User10] [decimal](22, 6) NULL,
	[User11] [int] NULL,
	[User12] [int] NULL,
	[TransactionStatus] [nvarchar](15) NULL,
	[CreatedBy] [nvarchar](15) NULL,
	[ConsiderVariableCostBasedOnBatchWtPnmast] [bit] NULL,
	[FixedCostAmountStd] [decimal](22, 6) NULL,
	[FixedOverheadAmountStd] [decimal](22, 6) NULL,
	[VariableLaborAmountStd] [decimal](22, 6) NULL,
	[VariableOverheadAmountStd] [decimal](22, 6) NULL,
	[SetupLaborAmountStd] [decimal](22, 6) NULL,
	[SetupOverheadAmountStd] [decimal](22, 6) NULL,
 CONSTRAINT [PK_PNMAST] PRIMARY KEY CLUSTERED 
(
	[BatchNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_Status]  DEFAULT ('N') FOR [Status]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_FixedCostFactor]  DEFAULT ((0)) FOR [FixedCostHours]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_FixedCostAmount]  DEFAULT ((0)) FOR [FixedCostAmount]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_FixedOverheadAmount]  DEFAULT ((0)) FOR [FixedOverheadAmount]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_VariableLaborAmount]  DEFAULT ((0)) FOR [VariableLaborAmount]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_VariableOverheadAmount]  DEFAULT ((0)) FOR [VariableOverheadAmount]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_SetupCostFactor]  DEFAULT ((0)) FOR [SetupCostHours]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_SetupLaborAmount]  DEFAULT ((0)) FOR [SetupLaborAmount]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_SetupOverheadAmount]  DEFAULT ((0)) FOR [SetupOverheadAmount]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_SpecGravOverride]  DEFAULT ((0)) FOR [SpecGravOverride]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_SysDocId]  DEFAULT ((0)) FOR [SysDocId]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_RunHours]  DEFAULT ((0)) FOR [RunHours]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_LastLineSeqNo]  DEFAULT ((0)) FOR [LastLineSeqNo]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_WIPBalance]  DEFAULT ((0)) FOR [WIPBalance]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_BatchMatCostStd]  DEFAULT ((0)) FOR [BatchMatCostStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_BatchMatCostActual]  DEFAULT ((0)) FOR [BatchMatCostActual]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_BatchLaborCostStd]  DEFAULT ((0)) FOR [BatchLaborCostStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_BatchLabortCostActual]  DEFAULT ((0)) FOR [BatchLabortCostActual]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_BatchOverHeadCostStd]  DEFAULT ((0)) FOR [BatchOverHeadCostStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_BatchOverHeadCostActual]  DEFAULT ((0)) FOR [BatchOverHeadCostActual]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalByPCost]  DEFAULT ((0)) FOR [TotalByPCost]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalWeightUsed]  DEFAULT ((0)) FOR [TotalWeightUsed]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalVolumetUsed]  DEFAULT ((0)) FOR [TotalVolumetUsed]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalWeightYielded]  DEFAULT ((0)) FOR [TotalFGWeightYielded]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalVolumeYielded]  DEFAULT ((0)) FOR [TotalFGVolumeYielded]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalByWeightYielded]  DEFAULT ((0)) FOR [TotalByWeightYielded]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalByVolumeYielded]  DEFAULT ((0)) FOR [TotalByVolumeYielded]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalBatchVolume]  DEFAULT ((0)) FOR [BatchVolume]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalBatchVolume1]  DEFAULT ((0)) FOR [BatchWeight]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_OrderWeight]  DEFAULT ((0)) FOR [OrderWeight]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_OrderVolume]  DEFAULT ((0)) FOR [OrderVolume]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalStdCost]  DEFAULT ((0)) FOR [TotalStdCost]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalActualCost]  DEFAULT ((0)) FOR [TotalActualCost]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_OldLossFactor]  DEFAULT ((0)) FOR [OldLossFactor]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_OldLossConstant]  DEFAULT ((0)) FOR [OldLossConstant]
GO

ALTER TABLE [dbo].[PNMAST] ADD  DEFAULT ((0)) FOR [ConsiderVariableCostBasedOnBatchWtPnmast]
GO

ALTER TABLE [dbo].[PNMAST] ADD  DEFAULT ((0)) FOR [FixedCostAmountStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  DEFAULT ((0)) FOR [FixedOverheadAmountStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  DEFAULT ((0)) FOR [VariableLaborAmountStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  DEFAULT ((0)) FOR [VariableOverheadAmountStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  DEFAULT ((0)) FOR [SetupLaborAmountStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  DEFAULT ((0)) FOR [SetupOverheadAmountStd]
GO

Open in new window

Software Engineer

Author

Commented:
Here is the INSERT INTO script for BM010115:

INSERT INTO [dbo].[BM010115]
           ([PPN_I]
           ,[CPN_I]
           ,[BOMCAT_I]
           ,[BOMNAME_I]
           ,[BOMTYPE_I]
           ,[BOMSEQ_I]
           ,[POSITION_NUMBER]
           ,[SUBCAT_I]
           ,[SUBNAME_I]
           ,[SUB_REV_LEVEL_SEQ_I]
           ,[QUANTITY_I]
           ,[OPTPERCENT_I]
           ,[SCRAPPERCENT_I]
           ,[FLOORSTOCK_I]
           ,[EFFECTIVEINDATE_I]
           ,[EFFECTIVEOUTDATE_I]
           ,[ALTERNATE_I]
           ,[ALTERNATEPARTFOR_I]
           ,[ALT_FOR_BOM_SEQ_I]
           ,[LEADTIMEOFFSET_I]
           ,[LEADTIMEOFFSETINC_I]
           ,[BOMUSERDEF1_I]
           ,[BOMUSERDEF2_I]
           ,[BOMSINGLELOT_I]
           ,[BOMENGAPPROVAL_I]
           ,[WCID_I]
           ,[LOCNCODE]
           ,[BACKFLUSHITEM_I]
           ,[CHANGEDATE_I]
           ,[USERID]
           ,[OPTIONED_ITEM_I]
           ,[ACTUAL_CONSUMED_CHECK_I]
           ,[FIXED_QTY_I]
           ,[UOFM]
           ,[U_Of_M_2]
           ,[QTYBSUOM]
           ,[OFFSET_FROM_I]
           ,[MFGNOTEINDEX_I]
           ,[MFGNOTEINDEX2_I])
     VALUES
           (<PPN_I, char(31),>
           ,<CPN_I, char(31),>
           ,<BOMCAT_I, smallint,>
           ,<BOMNAME_I, char(15),>
           ,<BOMTYPE_I, smallint,>
           ,<BOMSEQ_I, int,>
           ,<POSITION_NUMBER, smallint,>
           ,<SUBCAT_I, smallint,>
           ,<SUBNAME_I, char(15),>
           ,<SUB_REV_LEVEL_SEQ_I, int,>
           ,<QUANTITY_I, numeric(19,5),>
           ,<OPTPERCENT_I, int,>
           ,<SCRAPPERCENT_I, int,>
           ,<FLOORSTOCK_I, tinyint,>
           ,<EFFECTIVEINDATE_I, datetime,>
           ,<EFFECTIVEOUTDATE_I, datetime,>
           ,<ALTERNATE_I, tinyint,>
           ,<ALTERNATEPARTFOR_I, char(31),>
           ,<ALT_FOR_BOM_SEQ_I, int,>
           ,<LEADTIMEOFFSET_I, numeric(19,5),>
           ,<LEADTIMEOFFSETINC_I, smallint,>
           ,<BOMUSERDEF1_I, char(31),>
           ,<BOMUSERDEF2_I, char(31),>
           ,<BOMSINGLELOT_I, tinyint,>
           ,<BOMENGAPPROVAL_I, tinyint,>
           ,<WCID_I, char(11),>
           ,<LOCNCODE, char(11),>
           ,<BACKFLUSHITEM_I, tinyint,>
           ,<CHANGEDATE_I, datetime,>
           ,<USERID, char(15),>
           ,<OPTIONED_ITEM_I, tinyint,>
           ,<ACTUAL_CONSUMED_CHECK_I, tinyint,>
           ,<FIXED_QTY_I, numeric(19,5),>
           ,<UOFM, char(9),>
           ,<U_Of_M_2, char(9),>
           ,<QTYBSUOM, numeric(19,5),>
           ,<OFFSET_FROM_I, smallint,>
           ,<MFGNOTEINDEX_I, numeric(19,5),>
           ,<MFGNOTEINDEX2_I, numeric(19,5),>)
GO

Open in new window

Software Engineer

Author

Commented:
Here is the PNMAST INSERT INTO script:


INSERT INTO [dbo].[PNMAST]
           ([BatchNo]
           ,[BatchType]
           ,[FormulaId]
           ,[ItemKey]
           ,[LocationKey]
           ,[Description]
           ,[Status]
           ,[DrawFromLocation]
           ,[PostToLocation]
           ,[BatchTicketDate]
           ,[Notes]
           ,[CustKey]
           ,[EntryDate]
           ,[SchStartDate]
           ,[SchCompletionDate]
           ,[ActStartDate]
           ,[ActCompletionDate]
           ,[FixedCostLaborID]
           ,[FixedCostOverheadID]
           ,[FixedCostHours]
           ,[FixedCostAmount]
           ,[FixedOverheadAmount]
           ,[VariableCostLaborID]
           ,[VariableCostOverheadID]
           ,[VariableLaborAmount]
           ,[VariableOverheadAmount]
           ,[SetupCostLabourId]
           ,[SetupCostOverheadID]
           ,[SetupCostHours]
           ,[SetupLaborAmount]
           ,[SetupOverheadAmount]
           ,[SpecGravOverride]
           ,[SysDocId]
           ,[ProcessCellId]
           ,[RunHours]
           ,[LastLineSeqNo]
           ,[WIPAccount]
           ,[WIPBalance]
           ,[BatchMatCostStd]
           ,[BatchMatCostActual]
           ,[BatchLaborCostStd]
           ,[BatchLabortCostActual]
           ,[BatchOverHeadCostStd]
           ,[BatchOverHeadCostActual]
           ,[TotalByPCost]
           ,[TotalWeightUsed]
           ,[TotalVolumetUsed]
           ,[TotalFGWeightYielded]
           ,[TotalFGVolumeYielded]
           ,[TotalByWeightYielded]
           ,[TotalByVolumeYielded]
           ,[BatchVolume]
           ,[BatchWeight]
           ,[OrderWeight]
           ,[OrderVolume]
           ,[TotalStdCost]
           ,[TotalActualCost]
           ,[SONumber]
           ,[OldLossFactor]
           ,[OldLossConstant]
           ,[AplDate]
           ,[FMRevisionNumber]
           ,[RecUserid]
           ,[Recdate]
           ,[User1]
           ,[User2]
           ,[User3]
           ,[User4]
           ,[User5]
           ,[User6]
           ,[User7]
           ,[User8]
           ,[User9]
           ,[User10]
           ,[User11]
           ,[User12]
           ,[TransactionStatus]
           ,[CreatedBy]
           ,[ConsiderVariableCostBasedOnBatchWtPnmast]
           ,[FixedCostAmountStd]
           ,[FixedOverheadAmountStd]
           ,[VariableLaborAmountStd]
           ,[VariableOverheadAmountStd]
           ,[SetupLaborAmountStd]
           ,[SetupOverheadAmountStd])
     VALUES
           (<BatchNo, nvarchar(15),>
           ,<BatchType, char(1),>
           ,<FormulaId, nvarchar(20),>
           ,<ItemKey, nvarchar(80),>
           ,<LocationKey, nvarchar(15),>
           ,<Description, nvarchar(80),>
           ,<Status, nvarchar(1),>
           ,<DrawFromLocation, nvarchar(15),>
           ,<PostToLocation, nvarchar(15),>
           ,<BatchTicketDate, datetime,>
           ,<Notes, nvarchar(3500),>
           ,<CustKey, nvarchar(25),>
           ,<EntryDate, datetime,>
           ,<SchStartDate, datetime,>
           ,<SchCompletionDate, datetime,>
           ,<ActStartDate, datetime,>
           ,<ActCompletionDate, datetime,>
           ,<FixedCostLaborID, nvarchar(20),>
           ,<FixedCostOverheadID, nvarchar(20),>
           ,<FixedCostHours, float,>
           ,<FixedCostAmount, decimal(22,6),>
           ,<FixedOverheadAmount, decimal(22,6),>
           ,<VariableCostLaborID, nvarchar(20),>
           ,<VariableCostOverheadID, nvarchar(20),>
           ,<VariableLaborAmount, decimal(22,6),>
           ,<VariableOverheadAmount, decimal(22,6),>
           ,<SetupCostLabourId, nvarchar(20),>
           ,<SetupCostOverheadID, nvarchar(20),>
           ,<SetupCostHours, float,>
           ,<SetupLaborAmount, decimal(22,6),>
           ,<SetupOverheadAmount, decimal(22,6),>
           ,<SpecGravOverride, float,>
           ,<SysDocId, int,>
           ,<ProcessCellId, nvarchar(20),>
           ,<RunHours, float,>
           ,<LastLineSeqNo, int,>
           ,<WIPAccount, nvarchar(80),>
           ,<WIPBalance, decimal(22,6),>
           ,<BatchMatCostStd, decimal(22,6),>
           ,<BatchMatCostActual, decimal(22,6),>
           ,<BatchLaborCostStd, decimal(22,6),>
           ,<BatchLabortCostActual, decimal(22,6),>
           ,<BatchOverHeadCostStd, decimal(22,6),>
           ,<BatchOverHeadCostActual, decimal(22,6),>
           ,<TotalByPCost, decimal(22,6),>
           ,<TotalWeightUsed, float,>
           ,<TotalVolumetUsed, float,>
           ,<TotalFGWeightYielded, float,>
           ,<TotalFGVolumeYielded, float,>
           ,<TotalByWeightYielded, float,>
           ,<TotalByVolumeYielded, float,>
           ,<BatchVolume, float,>
           ,<BatchWeight, float,>
           ,<OrderWeight, float,>
           ,<OrderVolume, float,>
           ,<TotalStdCost, decimal(22,6),>
           ,<TotalActualCost, decimal(22,6),>
           ,<SONumber, nvarchar(50),>
           ,<OldLossFactor, float,>
           ,<OldLossConstant, float,>
           ,<AplDate, datetime,>
           ,<FMRevisionNumber, nvarchar(10),>
           ,<RecUserid, nvarchar(15),>
           ,<Recdate, datetime,>
           ,<User1, nvarchar(225),>
           ,<User2, nvarchar(225),>
           ,<User3, nvarchar(225),>
           ,<User4, nvarchar(50),>
           ,<User5, nvarchar(50),>
           ,<User6, datetime,>
           ,<User7, float,>
           ,<User8, float,>
           ,<User9, decimal(22,6),>
           ,<User10, decimal(22,6),>
           ,<User11, int,>
           ,<User12, int,>
           ,<TransactionStatus, nvarchar(15),>
           ,<CreatedBy, nvarchar(15),>
           ,<ConsiderVariableCostBasedOnBatchWtPnmast, bit,>
           ,<FixedCostAmountStd, decimal(22,6),>
           ,<FixedOverheadAmountStd, decimal(22,6),>
           ,<VariableLaborAmountStd, decimal(22,6),>
           ,<VariableOverheadAmountStd, decimal(22,6),>
           ,<SetupLaborAmountStd, decimal(22,6),>
           ,<SetupOverheadAmountStd, decimal(22,6),>)
GO

Open in new window

Commented:
I split the statement that I wanted modified into two separate unioned statements, as shown here:

select DISTINCT NULL as [LOTSEQNBR], NULL as [FGBATCH], NULL as [FG], NULL as [FGLABOR], NULL as [FGOH], NULL as [FGMAT], NULL as [FGCOST], --BOM.PPN_I as [FORMULA],
PROD.FormulaId as [FORMULA], PROD.BatchMatCostActual as [FORMULAMAT], PROD.BatchLabortCostActual AS [FORMULALABOR], PROD.BatchOverHeadCostActual AS [FORMULAOH],
INV.ITEMNMBR 
as [Component], 
NULL as [LOT], NULL as [LOTQTY], NULL as [LOTUNITCOST]
from PNMAST PROD
INNER JOIN IV30300 INV ON PROD.FormulaId = INV.ITEMNMBR
where PROD.FormulaId IN (select BOM.CPN_I from BM010115 BOM INNER JOIN PNMAST PROD on PROD.FormulaId = BOM.CPN_I where BOM.SUBCAT_I = 1) and
INV.LNSEQNBR = 16384 and --INV.ITEMNMBR IN ('506N', '1880', '1355') and 
INV.DOCTYPE = 1 and INV.DOCNUMBR IN ('081007', '081008', '081009')
UNION ALL
select DISTINCT NULL as [LOTSEQNBR], NULL as [FGBATCH], NULL as [FG], NULL as [FGLABOR], NULL as [FGOH], NULL as [FGMAT], NULL as [FGCOST], --BOM.PPN_I as [FORMULA],
NULL as [FORMULA], NULL as [FORMULAMAT], NULL AS [FORMULALABOR], NULL AS [FORMULAOH],
INV.ITEMNMBR 
as [Component], 
NULL as [LOT], NULL as [LOTQTY], NULL as [LOTUNITCOST]
from IV30300 INV INNER JOIN BM010115 BOM ON INV.ITEMNMBR = BOM.CPN_I
where --PROD.FormulaId IN (select BOM.CPN_I from BM010115 BOM INNER JOIN PNMAST PROD on PROD.FormulaId = BOM.CPN_I where BOM.SUBCAT_I = 1) and
INV.LNSEQNBR = 16384 and --INV.ITEMNMBR IN ('506N', '1880', '1355') and 
INV.DOCTYPE = 1 and INV.DOCNUMBR IN ('081007', '081008', '081009')

Open in new window