May 7, 2020 at 9:59 pm
Hello:
Below is my T-SQL code, and attached are its results.
In the highlighted "Component" column, I need for the NULLs to be gone and for the data beginning in row 106 to be moved up to row 2. Likewise, I want this for the data within the "LOTMATUNITCOST" column.
This will remove the NULLs appearing in the beginning of both columns, effectively "collapsing" the data returned by my query.
I'm thinking that, once this feat is accomplished, I can conclude my project.
Thank you! Much appreciated!
John
Select * 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],
NULL as [Component],
rtrim(cast([LOT] as varchar)) as [LOT], CONVERT(DECIMAL(10,2), WIPTEST.[LOTQTY]) as [LOTQTY],
--CONVERT(DECIMAL(10,2), [LOTMATUNITCOST]) as [LOTMATUNITCOST],
0 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], YIELD.[YIELD] 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
CROSS JOIN (select
[FGWEIGHT]/SUM([LOTQTY]) as [YIELD] from (
select DISTINCT
CONVERT(DECIMAL(10,2),[FGWEIGHT]) as [FGWEIGHT],
CONVERT(DECIMAL(10,2), WIPTEST.[LOTQTY]) as [LOTQTY],
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] = '506320D23' and WIPTEST.[INVHDRBATCH] = '506320D23'
) as WIP
where LOTLABORWIPCOST <> 0 AND lotohwipcost <> 0
GROUP BY WIP.FGWEIGHT) as YIELD
where LOT IS NOT NULL and LOG.FGItem = ''
and WIPTEST.[FG] = '506' and WIPTEST.[FGBATCH] = '506320D23' and WIPTEST.[INVHDRBATCH] = '506320D23'
)
as test
UNION ALL
select * from (
select DISTINCT 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],
rtrim(cast((select DISTINCT IV30300.ITEMNMBR) as varchar)) as [Component],
NULL AS LOT,
NULL as [LOTQTY],
IV00300.UNITCOST as [LOTMATUNITCOST],
NULL as [LOTMATWIPCOST],
NULL as [LOTLABORWIPCOST],
NULL 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
LEFT OUTER JOIN IV00300 ON IV30300.ITEMNMBR = IV00300.ITEMNMBR
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] = '506320D23' and WIPTEST.[INVHDRBATCH] = '506320D23'
)
as test2
May 8, 2020 at 6:37 am
Actually, you won't eliminate all the nulls unless some of the values in row 106 to 149 need to be duplicated in rows 2 to 105. There aren't enough rows of data for a 1 to 1 replacement.
To take this further, you have posted a fairly complex union query expecting us to know what it is doing and how it is doing it thinking that we can just magically answer your question based solely on the query and the current results. Maybe we could, if we had access to your system and could see everything you see. Unfortunately we can't.
We would love to help you, but to do that you also need to help us. You would need to post the DDL for all the tables, including the ones in the view also used in the query, the DDL for the view, just mentioned. Sample data, not production data, that is reflective of the problem domain. Current results using the sample data, and expected results based on the sample data.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply