We help IT Professionals succeed at work.
Get Started

T-SQL: Joining Tables

84 Views
Last Modified: 2020-03-09
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
This problem has been solved!
Unlock 1 Answer and 10 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE