Dynamics Community Forum Thread Details

Hi:

The first code block below has a field called IM. This is equal to the FormulaID field of FMItem of the second code.
The ItemKey field of the second code is the FormulaID field of the third code's FMItem table.
Both the second and third codes are of the same table, of course. The where clauses are slightly different.
The first code that I need modified already contains an IM of 506L.
In addition, I created another field named "IM2". This field should be returned as item 129. This is the only item in the ItemKey column that is a formula (i.e. the FMItem table)
Originally, I placed the following restriction at the end of the code: and FORMULA.ItemKey <> IM2.IM2.
But, I now have it commented out. (The restriction was preventing my code from returning results.)
How can I modify the syntax of the code to allow for having this restriction, so that IM = 506L and IM2 = 129?

Thank you!

John

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],
rtrim(cast((select DISTINCT IV30300.ITEMNMBR) as varchar)) as [Component],
FORMULA.ItemKey as IM,
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], 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
LEFT OUTER JOIN FMItem FORMULA on IV30300.ITEMNMBR = FORMULA.FormulaID
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] = '50418G12D' and WIPTEST.[INVHDRBATCH] = '50418G12D' 
 ) as WIP
   where LOTLABORWIPCOST <> 0 AND lotohwipcost <> 0 
   GROUP BY WIP.FGWEIGHT) as YIELD
CROSS JOIN 
(select ItemKey as IM2 
from 
(select ItemKey
   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 FMItem FORMULA on IV30300.ITEMNMBR = FORMULA.FormulaID
   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 WIPTEST.LOT IS NOT NULL and LOG.FGItem = '' 
     and WIPTEST.[FG] = '506' and WIPTEST.[FGBATCH] = '50418G12D' and WIPTEST.[INVHDRBATCH] = '50418G12D')
as WIP2)
as IM2
where LOT IS NOT NULL and LOG.FGItem = '' --and FORMULA.ItemKey <> IM2.IM2
and WIPTEST.[FG] = '506' and WIPTEST.[FGBATCH] = '50418G12D' and WIPTEST.[INVHDRBATCH] = '50418G12D'

select * from FMItem where FormulaId = '506N'

select * from FMItem where FormulaId = '506L'

Categories:

Microsoft Dynamics GP

T-SQL: Pulling Different Data from the Same Table

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates