T-SQL: "Collapsing" To Get Rid of NULLs

1

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!

backinthe1980s

Results
Results1492×707 52.6 KB 

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
2

where column_you_have_nulls IS NOT NULL

1 Like