troubleshooting Question

TSQL: How to Copy a Value from One UNION ALL Clause to Another

Avatar of Software Engineer
Software Engineer asked on
Microsoft SQL Server* T-SQLSQL
9 Comments1 Solution35 ViewsLast Modified:
Hi There:

Below is my T-SQL code and attached are the results.

You'll notice in the results and in the code that the final field is called "Yield".

I need for the Yield value of 1.52 to be moved from the final row (row 9) into each of the other (eight) rows in the Yield field.  And, I'd like the final row (row 9) to be eliminated.

You see, I want the figure for Yield that is "NOT NULL" to be moved into the other eight rows and for the last row to be removed.

In effect, I want the value for Yield from the second UNION ALL clause to be placed into the first UNION ALL clause.

How do I conduct the syntax to accomplish this?

Thank you!

Software Engineer

Results.xlsx

select DISTINCT [INVHDRBATCH], 
[FGBATCH], [FG], CONVERT(DECIMAL(10,2), [FGMAT]) as [FGMAT], 
CONVERT(DECIMAL(10,2), [FGLABOR]) as [FGLABOR], CONVERT(DECIMAL(10,2), [FGOH]) as [FGOH], 
CONVERT(DECIMAL(10,2), [FGCOST]) as [FGCOST], 
CONVERT(DECIMAL(10,2),[FGWEIGHT]) as [FGWEIGHT], [MATLB] as [MATLB], 
[LABORLB] as [LABORLB], 
[OHLB] as [OHLB],
--[FORMULA], [FORMULAMAT], [FORMULALABOR], [FORMULAOH], 
rtrim(cast((select DISTINCT IV30300.ITEMNMBR) as varchar)) as [Component],
rtrim(cast([LOT] as varchar)) as [LOT], CONVERT(DECIMAL(10,2), WIPTEST.[LOTQTY]) as [LOTQTY], 
CONVERT(DECIMAL(10,2), [LOTMATUNITCOST]) as [LOTMATUNITCOST], 
CONVERT(DECIMAL(10,2), [LOTMATWIPCOST]) as [LOTMATWIPCOST], 
case when rtrim(cast([LOT] as varchar)) LIKE rtrim(cast((select DISTINCT IV30300.ITEMNMBR) as varchar))+'%' 
THEN CONVERT(DECIMAL(10,2), [LOTLABORWIPCOST]) else 0 end as [LOTLABORWIPCOST], 
case when rtrim(cast([LOT] as varchar)) LIKE rtrim(cast((select DISTINCT IV30300.ITEMNMBR) as varchar))+'%' 
THEN CONVERT(DECIMAL(10,2), [LOTOHWIPCOST]) else 0 end as [LOTOHWIPCOST], NULL as [YIELD]
from WIPTEST
INNER JOIN IV30200 BATCH on WIPTEST.[INVHDRBATCH] = BATCH.BACHNUMB AND WIPTEST.[INVHDRDOC] = BATCH.DOCNUMBR
INNER JOIN IV30300 ON BATCH.TRXSORCE = IV30300.TRXSORCE AND BATCH.DOCNUMBR = IV30300.DOCNUMBR
INNER JOIN IV30400 ON IV30300.TRXSORCE = IV30400.TRXSORCE
INNER JOIN BM_View_SL_BatchTicketAuditLog LOG on BATCH.BACHNUMB = LOG.BatchTicket AND BATCH.DOCNUMBR = LOG.GPDocNo
where LOT IS NOT NULL and LOG.FGItem = ''
and WIPTEST.[FG] = '506' and WIPTEST.[FGBATCH] = '50418G12D' and WIPTEST.[INVHDRBATCH] = '50418G12D' 
UNION ALL
select
NULL AS [INVHDRBATCH], NULL AS [FGBATCH], NULL AS [FG], NULL AS [FGMAT], NULL AS [FGLABOR], NULL AS [FGOH], NULL AS [FGCOST], NULL AS [FGWEIGHT], 
NULL AS [MATLB], NULL as [LABORLB], NULL as [OHLB], NULL as [Component], NULL as [LOT], NULL as [LOTQTY], 
NULL as [LOTMATUNITCOST], NULL as [LOTMATWIPCOST], NULL as [LOTLABORWIPCOST], NULL as [LOTOHWIPCOST],
[FGWEIGHT]/SUM([LOTQTY]) as [YIELD] from (
select DISTINCT [INVHDRBATCH], 
[FGBATCH], [FG], CONVERT(DECIMAL(10,2), [FGMAT]) as [FGMAT], 
CONVERT(DECIMAL(10,2), [FGLABOR]) as [FGLABOR], CONVERT(DECIMAL(10,2), [FGOH]) as [FGOH], 
CONVERT(DECIMAL(10,2), [FGCOST]) as [FGCOST], 
CONVERT(DECIMAL(10,2),[FGWEIGHT]) as [FGWEIGHT], [MATLB] as [MATLB], 
[LABORLB] as [LABORLB], 
[OHLB] as [OHLB],
--[FORMULA], [FORMULAMAT], [FORMULALABOR], [FORMULAOH], 
rtrim(cast((select DISTINCT IV30300.ITEMNMBR) as varchar)) as [Component],
rtrim(cast([LOT] as varchar)) as [LOT], CONVERT(DECIMAL(10,2), WIPTEST.[LOTQTY]) as [LOTQTY], 
CONVERT(DECIMAL(10,2), [LOTMATUNITCOST]) as [LOTMATUNITCOST], 
CONVERT(DECIMAL(10,2), [LOTMATWIPCOST]) as [LOTMATWIPCOST], 
case when rtrim(cast([LOT] as varchar)) LIKE rtrim(cast((select DISTINCT IV30300.ITEMNMBR) as varchar))+'%' 
THEN CONVERT(DECIMAL(10,2), [LOTLABORWIPCOST]) else 0 end as [LOTLABORWIPCOST], 
case when rtrim(cast([LOT] as varchar)) LIKE rtrim(cast((select DISTINCT IV30300.ITEMNMBR) as varchar))+'%' 
THEN CONVERT(DECIMAL(10,2), [LOTOHWIPCOST]) else 0 end as [LOTOHWIPCOST]
from WIPTEST
INNER JOIN IV30200 BATCH on WIPTEST.[INVHDRBATCH] = BATCH.BACHNUMB AND WIPTEST.[INVHDRDOC] = BATCH.DOCNUMBR
INNER JOIN IV30300 ON BATCH.TRXSORCE = IV30300.TRXSORCE AND BATCH.DOCNUMBR = IV30300.DOCNUMBR
INNER JOIN IV30400 ON IV30300.TRXSORCE = IV30400.TRXSORCE
INNER JOIN BM_View_SL_BatchTicketAuditLog LOG on BATCH.BACHNUMB = LOG.BatchTicket AND BATCH.DOCNUMBR = LOG.GPDocNo
where LOT IS NOT NULL and LOG.FGItem = ''
and WIPTEST.[FG] = '506' and WIPTEST.[FGBATCH] = '50418G12D' and WIPTEST.[INVHDRBATCH] = '50418G12D' 
) as WIP
where LOTLABORWIPCOST <> 0 AND lotohwipcost <> 0 
GROUP BY WIP.INVHDRBATCH, WIP.FGBATCH, WIP.FG, WIP.FGMAT, WIP.FGLABOR, WIP.FGOH, WIP.FGCOST, WIP.FGWEIGHT, WIP.MATLB,
WIP.LABORLB, WIP.OHLB, WIP.Component, WIP.LOTMATUNITCOST
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros