Link to home
Create AccountLog in
Avatar of Software Engineer
Software Engineer

asked on

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

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

Open in new window

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Maybe don't use union all and compute the yield on each row likely using a window clause and case statement.

I don't have all your tables but kept my sample close to the SQL you posted.  You should be able to incorporate it into your main query.

Something like this:
select DISTINCT [INVHDRBATCH], 
[FGBATCH], [FG], CONVERT(DECIMAL(10,2), [FGMAT]) as [FGMAT],
fgweight / SUM(case when LOTLABORWIPCOST <> 0 AND lotohwipcost <> 0 then [LOTQTY] else 0 end) over(partition BY INVHDRBATCH, FGBATCH) [YIELD]
from junk;

Open in new window


I made up a simple test based on the data and SQL you provided:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=bf1874fec95be5e08974a8271d3e1046
Avatar of Software Engineer

ASKER

Hi slightwv:

Thank you, for the quick response!  That's much appreciated!

Unfortunately, that won't work.  I need the UNION ALL syntax to prevent having to GROUP BY LOT and LOTQTY.  

If I have too many GROUP BYs, Yield will not calculate properly.

Software Engineer
But the group by is just for the SUM.  If you move the SUM to use the window function, you don't need the group by.

If you need to SUM OVER on more fields, just add them the the PARTITION BY.  That is the "group by" of a window function.  Did you look at the fiddle I posted?  I get the numbers you say you want.

If you can post a better set of sample data and expected results, we can post a working example from it.

When I see DISTINCT, I normally see a bad design or bad SQL.  Then you have the inline select distinct.  Not sure you need those either.
Hi:

Yes, I saw the fiddle.  But, again, it does not work.

You mentioned "If you need to SUM OVER on more fields, just add them the the PARTITION BY."  Do you have syntax, for this?  That may give me what I need.

Again, I need for that Yield to appear in every row as it is and for the last row to be removed.  Short of that, the results spreadsheet that I posted are the expected results.

Software Engineer
>>Do you have syntax, for this?  That may give me what I need.

It is in the fiddle I provided.  It uses two columns to "group by"  INVHDRBATCH, FGBATCH.  If you need the grouping to be more, just add them to the statement.  

I cannot add them for you because I don't have your data.  What columns do you want the SUM to be formed on, those are the columns to add to the PARTITION BY.  Those are normally the unique columns that are important.  I guessed with INVHDRBATCH, FGBATCH.

>>Again, I need for that Yield to appear in every row as it is and for the last row to be removed.  Short of that, the results spreadsheet that I posted are the expected results.

I get that.  What I am suggesting does that.  You remove the last row by removing the UNION ALL.  Now all you need is the computed Yield field.  Using "SUM() OVER()" does that.

I set up a test case with the data you provided and produced the results you wanted.

If you want 100% accurate SQL you can copy and paste, we'll need data and expected results to set up a testcase with.

Otherwise, you will need to understand the SQL I posted and apply it to your data.
Use common table expressions

;with cte as (
Paste your complete query here
)

Update rablename a inner join cte b on a.column=b.column2 set a.column=b.column9,a.column2=b.column9,a.column8=b.column9

Open in new window


This does two thing, puts your all join query data into its own single reference table
That you can use
My suggestion before trying the update
Use select of desired columns
Select * from rable_to_be_updated a inner join cte b on a.column_reference=b.column_matching_reference

Once the select works
You can convert it to an update that replaces columns in table (a) by assigning it values from table b.
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
You "can" use a CTE if you want but you really don't need to.  I'm 99% convinced using the Window function on SUM will get you what you want.  The fiddle I posted more or less shows that using the data you provided.
Thank you, pcelba!  That is exactly what I needed!

Software Engineer