Post reply

T-SQL: Pulling Different Data from the Same Table

  • April 29, 2020 at 9:52 pm

    #3747494

    Hi:

    The results of the first code below are attached as the file called "Results".

    Likewise, the results of the second and third code are attached as "Results2" and "Results3", respectively.  The table for both of these codes is FMItem.

    The first code 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.

    Again, 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, as shown in the ItemKey field of the third set of results.  This is the only item in the ItemKey column of the third set of results 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.  It 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'
    Attachments:
    You must be logged in to view attached files.

  • April 30, 2020 at 10:10 pm

    #3747864

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply