asked on

T-SQL: Join a Query to a CTE

Hello:

At the end of this posting is T-SQL programming for a CTE. Prior to this is my "main query" where I'm trying to join this CTE to my query.

Also below are the errors that SQL is returning to me. “Msg102” refers to the parentheses that I have at the very end of may main query.

Where did I go wrong, in my syntax?

Thank you!

Software Engineer

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'With'.
Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 63
Incorrect syntax near ')'

select TOP 80 PERCENT * from (
[i];With Drilldown as (
  select bm.ppn_i, bm.cpn_i, bm.bomname_i, bm.QUANTITY_I, 1 as BOMLevel
  from BM010115 bm
  where bm.PPN_I = '506' 
  UNION ALL
  select dd.cpn_i, bb.cpn_i, bb.bomname_i, bb.quantity_i, 
  (BOMLevel + 1) as BOMLevel
  from Drilldown dd join
       BM010115 bb
       on dd.cpn_i = bb.ppn_i
)[/i]
select DISTINCT 
[INVHDRBATCH], 
ROW_NUMBER() OVER(PARTITION BY IV00300.LOTNUMBR, CASE WHEN WIPTEST.Component in (select FormulaId from FMMAST) 
then WIPTEST.Component else IV00300.ITEMNMBR end ORDER BY IV00300.LOTNUMBR, 
CASE WHEN WIPTEST.Component in (select FormulaId from FMMAST) then WIPTEST.Component else IV00300.ITEMNMBR end) AS R,
[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], 
CONVERT(DECIMAL(10,2),[MATLB]) as [MATLB], 
CONVERT(DECIMAL(10,2),[LABORLB]) as [LABORLB], 
CONVERT(DECIMAL(10,2),[OHLB]) as [OHLB], 
--CASE WHEN WIPTEST.Component in (select FormulaId from FMMAST) then WIPTEST.Component else IV00300.ITEMNMBR end as [Component],  
[i]Drilldown.cpn_i as [Component],[/i]
IV00300.LOTNUMBR AS LOT,
IV00300.QTYRECVD - IV00300.QTYSOLD - IV00300.ATYALLOC as [LOTQTY], 
IV00300.UNITCOST as [LOTMATUNITCOST], 
(IV00300.QTYRECVD - IV00300.QTYSOLD - IV00300.ATYALLOC) * IV00300.UNITCOST as [LOTMATWIPCOST], 
CONVERT(DECIMAL(10,2), WIPTEST.[LOTLABORWIPCOST]) as [LOTLABORWIPCOST],
CONVERT(DECIMAL(10,2), WIPTEST.[LOTOHWIPCOST]) 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 IV00300 ON IV30300.ITEMNMBR = IV00300.ITEMNMBR
INNER JOIN BM_View_SL_BatchTicketAuditLog LOG on BATCH.BACHNUMB = LOG.BatchTicket AND BATCH.DOCNUMBR = LOG.GPDocNo
INNER JOIN Drilldown on Drilldown.cpn_i = WIPTEST.Component
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 test2
where r = 1
[i]OPTION (MAXRECURSION 0)[/i]

Open in new window

;With Drilldown as (
  select bm.ppn_i, bm.cpn_i, bm.bomname_i, bm.QUANTITY_I, 1 as BOMLevel
  from BM010115 bm
  where bm.PPN_I = '506' 
  UNION ALL
  select dd.cpn_i, bb.cpn_i, bb.bomname_i, bb.quantity_i, 
  (BOMLevel + 1) as BOMLevel
  from Drilldown dd join
       BM010115 bb
       on dd.cpn_i = bb.ppn_i
)

--you don't need to work the following two lines into the main query, but you do need 
--to limit the amount of data returned from here and, therefore, from the main query:
select top 1000 Drilldown.*, i.ITEMDESC from Drilldown, iv00101 i
where CPN_I = itemnmbr

--bottom of the main query:
OPTION (MAXRECURSION 0)

Open in new window

ste5an

The syntax of a CTE is

WITH cteName AS ( query )
SELECT *
FROM cteName;

Open in new window

where any DML is also possible (UPDATE, INSERT, DELETE).

Thus you need to take your CTE and add your query as further CTE:

WITH BOM AS ( bomQuery ),
otherQuery AS ( theOtherQuery )
SELECT *
FROM BOM 
  INNER JOIN otherQuery ON <joinPredicate>;

Open in new window

A simple sample:

WITH columnData
AS (SELECT object_id,
           name
    FROM sys.columns),
     tableData
AS (SELECT name,
           object_id,
           schema_id
    FROM sys.tables)
SELECT *
FROM tableData T
    INNER JOIN columnData C ON C.object_id = T.object_id;

Open in new window

ASKER CERTIFIED SOLUTION

Máté Farkas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Software Engineer

ASKER

Below is my revised query, with the CTE.
The reason that I put in formatting last time is to show which part of the main query that I was trying to tie to the CTE.
I'm a little apprehensive about taking out the "select TOP 80 PERCENT..." from the query as I do have the query ending with r=1 at the end.
And, the reason that I'm using 80 rather than 100 is because I was told that I need to limit the amount of data returned due to the volume of data that may be returned with the CTE.
I look forward to hearing back from you. Thank you, as usual!
Software Engineer

--select TOP 80 PERCENT * from (
Drilldown as (
  select bm.ppn_i, bm.cpn_i, bm.bomname_i, bm.QUANTITY_I, 1 as BOMLevel
  from BM010115 bm
  where bm.PPN_I = '506' 
  UNION ALL
  select dd.cpn_i, bb.cpn_i, bb.bomname_i, bb.quantity_i, 
  (BOMLevel + 1) as BOMLevel
  from Drilldown dd join
       BM010115 bb
       on dd.cpn_i = bb.ppn_i
)
select DISTINCT 
[INVHDRBATCH], 
ROW_NUMBER() OVER(PARTITION BY IV00300.LOTNUMBR, CASE WHEN WIPTEST.Component in (select FormulaId from FMMAST) 
then WIPTEST.Component else IV00300.ITEMNMBR end ORDER BY IV00300.LOTNUMBR, 
CASE WHEN WIPTEST.Component in (select FormulaId from FMMAST) then WIPTEST.Component else IV00300.ITEMNMBR end) AS R,
[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], 
CONVERT(DECIMAL(10,2),[MATLB]) as [MATLB], 
CONVERT(DECIMAL(10,2),[LABORLB]) as [LABORLB], 
CONVERT(DECIMAL(10,2),[OHLB]) as [OHLB], 
--CASE WHEN WIPTEST.Component in (select FormulaId from FMMAST) then WIPTEST.Component else IV00300.ITEMNMBR end as [Component],  
Drilldown.cpn_i as [Component],
IV00300.LOTNUMBR AS LOT,
IV00300.QTYRECVD - IV00300.QTYSOLD - IV00300.ATYALLOC as [LOTQTY], 
IV00300.UNITCOST as [LOTMATUNITCOST], 
(IV00300.QTYRECVD - IV00300.QTYSOLD - IV00300.ATYALLOC) * IV00300.UNITCOST as [LOTMATWIPCOST], 
CONVERT(DECIMAL(10,2), WIPTEST.[LOTLABORWIPCOST]) as [LOTLABORWIPCOST],
CONVERT(DECIMAL(10,2), WIPTEST.[LOTOHWIPCOST]) 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 IV00300 ON IV30300.ITEMNMBR = IV00300.ITEMNMBR
INNER JOIN BM_View_SL_BatchTicketAuditLog LOG on BATCH.BACHNUMB = LOG.BatchTicket AND BATCH.DOCNUMBR = LOG.GPDocNo
INNER JOIN Drilldown on Drilldown.cpn_i = WIPTEST.Component
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 test2
where r = 1
OPTION (MAXRECURSION 0)

Open in new window

Software Engineer

ASKER

Hi Mate:
Thanks to you, I figured it out. Below is my revised code.
I appreciate it!
Software Engineer

--select TOP 80 PERCENT * from (
with Drilldown as (
  select bm.ppn_i, bm.cpn_i, bm.bomname_i, bm.QUANTITY_I, 1 as BOMLevel
  from BM010115 bm
  where bm.PPN_I = '506' 
  UNION ALL
  select dd.cpn_i, bb.cpn_i, bb.bomname_i, bb.quantity_i, 
  (BOMLevel + 1) as BOMLevel
  from Drilldown dd join
       BM010115 bb
       on dd.cpn_i = bb.ppn_i
)
select TOP 80 PERCENT * from (
select DISTINCT 
[INVHDRBATCH], 
ROW_NUMBER() OVER(PARTITION BY IV00300.LOTNUMBR, CASE WHEN WIPTEST.Component in (select FormulaId from FMMAST) 
then WIPTEST.Component else IV00300.ITEMNMBR end ORDER BY IV00300.LOTNUMBR, 
CASE WHEN WIPTEST.Component in (select FormulaId from FMMAST) then WIPTEST.Component else IV00300.ITEMNMBR end) AS R,
[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], 
CONVERT(DECIMAL(10,2),[MATLB]) as [MATLB], 
CONVERT(DECIMAL(10,2),[LABORLB]) as [LABORLB], 
CONVERT(DECIMAL(10,2),[OHLB]) as [OHLB], 
--CASE WHEN WIPTEST.Component in (select FormulaId from FMMAST) then WIPTEST.Component else IV00300.ITEMNMBR end as [Component],  
Drilldown.cpn_i as [Component],
IV00300.LOTNUMBR AS LOT,
IV00300.QTYRECVD - IV00300.QTYSOLD - IV00300.ATYALLOC as [LOTQTY], 
IV00300.UNITCOST as [LOTMATUNITCOST], 
(IV00300.QTYRECVD - IV00300.QTYSOLD - IV00300.ATYALLOC) * IV00300.UNITCOST as [LOTMATWIPCOST], 
CONVERT(DECIMAL(10,2), WIPTEST.[LOTLABORWIPCOST]) as [LOTLABORWIPCOST],
CONVERT(DECIMAL(10,2), WIPTEST.[LOTOHWIPCOST]) 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 IV00300 ON IV30300.ITEMNMBR = IV00300.ITEMNMBR
INNER JOIN BM_View_SL_BatchTicketAuditLog LOG on BATCH.BACHNUMB = LOG.BatchTicket AND BATCH.DOCNUMBR = LOG.GPDocNo
LEFT OUTER JOIN Drilldown on Drilldown.cpn_i = WIPTEST.Component
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 test2
--and r = 1
--OPTION (MAXRECURSION 0)
)
as test2
where r = 1
OPTION (MAXRECURSION 0)

Open in new window

ste5an

WITH Drilldown
AS (SELECT bm.ppn_i,
           bm.cpn_i,
           bm.bomname_i,
           bm.QUANTITY_I,
           1 AS BOMLevel
    FROM BM010115 bm
    WHERE bm.PPN_I = '506'
    UNION ALL
    SELECT dd.cpn_i,
           bb.cpn_i,
           bb.bomname_i,
           bb.quantity_i,
           (BOMLevel + 1) AS BOMLevel
    FROM Drilldown dd
        JOIN BM010115 bb
            ON dd.cpn_i = bb.ppn_i),
     WIP
AS (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'),
     YIELD
AS (SELECT [FGWEIGHT] / SUM([LOTQTY]) AS [YIELD]
    FROM WIP
    WHERE LOTLABORWIPCOST <> 0
          AND LOTOHWIPCOST <> 0
    GROUP BY WIP.FGWEIGHT),
     test2
AS (SELECT DISTINCT
           [INVHDRBATCH],
           ROW_NUMBER() OVER (PARTITION BY IV00300.LOTNUMBR,
                                           CASE
                                               WHEN WIPTEST.Component IN
                                                    (
                                                        SELECT FormulaId FROM FMMAST
                                                    ) THEN
                                                   WIPTEST.Component
                                               ELSE
                                                   IV00300.ITEMNMBR
                                           END
                              ORDER BY IV00300.LOTNUMBR,
                                       CASE
                                           WHEN WIPTEST.Component IN
                                                (
                                                    SELECT FormulaId FROM FMMAST
                                                ) THEN
                                               WIPTEST.Component
                                           ELSE
                                               IV00300.ITEMNMBR
                                       END
                             ) AS R,
           [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],
           CONVERT(DECIMAL(10, 2), [MATLB]) AS [MATLB],
           CONVERT(DECIMAL(10, 2), [LABORLB]) AS [LABORLB],
           CONVERT(DECIMAL(10, 2), [OHLB]) AS [OHLB],
           Drilldown.cpn_i AS [Component],
           IV00300.LOTNUMBR AS LOT,
           IV00300.QTYRECVD - IV00300.QTYSOLD - IV00300.ATYALLOC AS [LOTQTY],
           IV00300.UNITCOST AS [LOTMATUNITCOST],
           (IV00300.QTYRECVD - IV00300.QTYSOLD - IV00300.ATYALLOC) * IV00300.UNITCOST AS [LOTMATWIPCOST],
           CONVERT(DECIMAL(10, 2), WIPTEST.[LOTLABORWIPCOST]) AS [LOTLABORWIPCOST],
           CONVERT(DECIMAL(10, 2), WIPTEST.[LOTOHWIPCOST]) 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 IV00300
            ON IV30300.ITEMNMBR = IV00300.ITEMNMBR
        INNER JOIN BM_View_SL_BatchTicketAuditLog LOG
            ON BATCH.BACHNUMB = LOG.BatchTicket
               AND BATCH.DOCNUMBR = LOG.GPDocNo
        LEFT OUTER JOIN Drilldown
            ON Drilldown.cpn_i = WIPTEST.Component
        CROSS JOIN YIELD
    WHERE LOT IS NOT NULL
          AND LOG.FGItem = ''
          AND WIPTEST.[FG] = '506'
          AND WIPTEST.[FGBATCH] = '506320D23'
          AND WIPTEST.[INVHDRBATCH] = '506320D23')
SELECT TOP 80 PERCENT
       *
FROM test2
WHERE R = 1
OPTION (MAXRECURSION 0);

Open in new window