T-SQL: Cannot perform an aggregate function...

An aggregation function means you are grouping the results (group by) a criteria that will combine columns outside the grouping.

There are way too many tables that you are including.

without knowing the data

how many columns, tables are you combining and extracting data from?

I don't know.  I just want to get the math to work.

Thanks

You have a huge amount of information in the query that I have no info on.

What are the common columns that you can group on?

If you need calculations done, consider using cte

I want the finished good weight to be divided by the sum of the LOTQTYs shown in the last four records that I have highlighted in red, where the last two columns do not show records with 0.

perhaps you can provide us with an expected result as well? that will be helpful since we are not familiar with your system and requirement.

also, how to define the "last four records" ? is it based on certain conditions, etc? would it be in some conditions to have different number of records? we try to understand further since it was not being explained in your question.

You should change only the first part of your query, before the UNION ALL command.
The fixed code what you need is here:

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([IV30300].[ITEMNMBR] as varchar)) as [Component]
     , 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 cast([LOT] as varchar(100)) like cast([IV30300].[ITEMNMBR] as varchar(100)) + '%' 
                then convert(decimal(10, 2), [LOTLABORWIPCOST])
            else 0
       end as                                          [LOTLABORWIPCOST]
     , case when cast([LOT] as varchar(100))) like cast([IV30300].[ITEMNMBR] as varchar(100)) + '%' 
                then convert(decimal(10, 2), [LOTOHWIPCOST])
            else 0
       end as                                          [LOTOHWIPCOST],
       convert(decimal(10, 2), [WIPTEST].[LOTQTY]) / nullif([calc].[SUM_LOTQTY], 0) as [YIELD]
from [WIPTEST]
inner join [IV30200] as [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] as [LOG] on  [BATCH].[BACHNUMB] = [LOG].[BatchTicket]
                                                     and [BATCH].[DOCNUMBR] = [LOG].[GPDocNo]
outer apply (
    select sum(cast(wt.[LOTQTY] as decimal(10, 2))) as SUM_LOTQTY
    from [WIPTEST] wt
    where wt.[INVHDRBATCH] = [WIPTEST].[INVHDRBATCH]
    and wt.[FGBATCH] = [WIPTEST].[FGBATCH]
    and wt.[FG] = [WIPTEST].[FG]
    and cast(wt.[LOT] as varchar(100)) like cast([IV30300].[ITEMNMBR] as varchar(100)) + '%'
    and convert(decimal(10, 2), [LOTLABORWIPCOST]) <> 0
    and cast(wt.[LOT] as varchar(100))) like cast([IV30300].[ITEMNMBR] as varchar(100)) + '%' 
    and then convert(decimal(10, 2), [LOTOHWIPCOST]) <> 0
) calc
where [LOT] is not null and 
[LOG].[FGItem] = '' and 
[WIPTEST].[FG] = '506' and 
[WIPTEST].[FGBATCH] = '50418G12D' and 
[WIPTEST].[INVHDRBATCH] = '50418G12D'
union all
select distinct 
       '' as   [INVHDRBATCH]
     , '' as   [FGBATCH]
     , '' 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]
     , '' as   [Component]
     , '' as   [LOT]
     , null as [LOTQTY]
     , null as [LOTMATUNITCOST]
     , null as [LOTMATWIPCOST]
     , null as [LOTLABORWIPCOST]
     , null as [LOTOHWIPCOST]
from [WIPTEST]
inner join [IV30400] as [LOTNO] on  [WIPTEST].[Component] = [LOTNO].[ITEMNMBR]
                                and [WIPTEST].[LOT] = [LOTNO].[SERLTNUM]
inner join [IV30200]            on  [WIPTEST].[INVHDRBATCH] = [IV30200].[BACHNUMB]
                                and [WIPTEST].[INVHDRDOC] = [IV30200].[DOCNUMBR]
where [WIPTEST].[FG] = '506' and 
[WIPTEST].[FGBATCH] = '50418G12D' and 
[WIPTEST].[INVHDRBATCH] = '50418G12D';

Select allOpen in new window

Hi All:

I still cannot get this to work.  

For those of you who asked questions, the "last four records" are again those that do not have 0 for LOTLABORWIPCOST and LOTOHWIPCOST.  Please see the results that I attached at the beginning of this case.

Also, I got "NULL" for all records for the new [YIELD] field brought about by using the outer apply clause.

If a cte would help, would someone please give me a hint on how to compose a cte for this?

Thank you, all, for your quick responses!  Very helpful!

Software Engineer

If your moniker is correct, please note the questions posed.
If there is a NULL in one column, the sum or any aggregating function might have NUL! As the result,
Isnull(column,value_when_true)

I can not help without the requisite information
What are the data that are common and on whose basis you are trying to combine data.

Hi arnold:

Yes, on the "NULL", but I'm trying to get past that issue.  It looks like I will have to use a cte but would like to know how to fit the concept of its syntax into my syntax.

Thanks!

Software Engineer

It is not possible for me to answer your questio.
Use the isnull option to avoid having nulls in the aggregate function.

Anybody else?  Please....

Surround columns with isnull to avoid having nulls
Isnull(columnname,0) for every column that you are aggregating.

Really, the main crux of this case is that I need for the sum.lotqty piece of the yield field that I'm trying to create to be just that - a summation of the lotqty field and not grouped by lotqty - and to be done so for each record where lotlaborwipcost <> 0 and lotohwipcost <> 0.

Having a summation of lotqty across records where lotlaborwipcost <> 0 and lotohwipcost <> 0 would exclude such records and would allow for the last four records of my results to be successfully summed.  I'm not really worried about NULLs right now.

I don't know how to explain all of this any better.  And, I don't understand why T-SQL has such a hard time summing records based on criteria.  It's not a difficult concept.

You seem to want t-SQL to bend to what you want, versus looking into how t-SQL works to get what you want.
You keep trying to push a square peg into a round hole.


Data is assembled from various sources to aggregate, you have to constrain the criteria on whose basis you are aggregating.

Cte with multiple tables that you would then join

As noted the query is just text on a page it means nothing to those who do not have access to the underlying data and what it is you are trying to combine and what you are summing.

"Cte with multiple tables that you would then join"....

How would I do this?

you can define multiple tables that are based on a select data that they represent

You have to analyz e each data source you are trying to combine and whether data needs to be aggregated ahead of time or the final query is how the aggregation should occur.

All I see is the tools hammer, nails, saw as an example
And then various planks of wood.
I have no idea what you are building, I only get your comment that whatever it is you are constructing does not come together.

Taking your moniker at face value, if I drop a two page code snippet in front of you with a similar description as you have, would you be able to point what the issue is that I am having for not getting the results I want?.

You have to structure your data to match the thing you want done to it.

Hi arnold:

No offense.  You're brilliant.  But, I think that I need to ask this of other people.  Telling me that I'm not providing you with enough information is not serving any purpose for me, just to be honest and candid.

So, to those "other people", please help me accomplish the following:

Really, the main crux of this case is that I need for the sum.lotqty piece of the yield field that I'm trying to create to be just that - a summation of the lotqty field and not grouped by lotqty - and to be done so for each record where lotlaborwipcost <> 0 and lotohwipcost <> 0.

Having a summation of lotqty across records where lotlaborwipcost <> 0 and lotohwipcost <> 0 would exclude such records and would allow for the last four records of my results to be successfully summed.  I'm not really worried about NULLs right now.

Thank you, for any help that you can provide, in advance!

Software Engineer

here is a thought in a small scale illustration
there are two tables
users whose columns are

userid, username, firstname, lastname
1,someuser, john,smith
2,anotheruser, jane, dow

Select allOpen in new window

each user performed tasks for which they were paid.
tasks table whose columns are

taskid,taskname, userid(referential based on the users table),duration, paid_boolean,amount
1,"build shelf",1,5,150
2,"assemble armoire",2,3,550
3,"code web interface to collect this data",2,3,350
4,"generate listings",1,4,350

Select allOpen in new window

now you want the follwoing inforamtion. number of tasks performed, total reimbursement, average hourly compensation and total hours spent.

select a.userid, a.username, a.firstname,a.lastname,count (b.taskid) as Number_of_Tasks,sum(b.amount as "Total Reimbursement"),sum(b.amount)/sum(b.duration) as "Average hourly rate",sum(b.duration) as "Total hours" from users a 
             inner join tasks b on a.userid=b.userid
group by a.userid,a,username,a.firstname,a.lastname

Select allOpen in new window

what the out put will be is

userid,username,firstname,lastname,Number_of_tasks,Total Reimbursement,average hourly rate,Total hours
1,someuser,john,smith,2,500,50,9
2,anotheruser,jane,dow,2,900,150,6

Select allOpen in new window

in my case I do not have columns.

The point of grouping is the basis on which the aggregate function works. you need a set of common columns which are set in the criteria of the group by
then each other column can not be included in the query unless it is aggregated, or distinguished using something like a case, at which point it adds additional columns.

In your case I see a huge number of columns, you want to sum some columns, but there is no criteria that I can see where there is a row which have multiple data points to aggregate.

Without a group by or there are other options
you have an assortment of rows while not unique in your view, are unique for the purpose of the T-SQL you have
To this point, if you remove the group by clause from the above example
the output will not match, and if not mistaken could generate an error .....

You need to add the following to the top portion of the union all query -

FGWEIGHT/(SUM( [LOTMATUNITCOST] + [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]) AS [YIELD]

Select allOpen in new window

Then you will need to complete that portion with a group by of all the unaggreated fields. Note that you can't use aliases for these columns and REMOVE  all the DISTINCT keywords -

[INVHDRBATCH], 
[FGBATCH],
[FG],
[FGMAT],
CONVERT(DECIMAL(10,2), [FGLABOR]),
CONVERT(DECIMAL(10,2), [FGOH]), 
CONVERT(DECIMAL(10,2), [FGCOST]), 
CONVERT(DECIMAL(10,2),[FGWEIGHT]),
[MATLB], 
[LABORLB], 
[OHLB],
rtrim(cast(IV30300.ITEMNMBR as varchar),
rtrim(cast([LOT] as varchar)),
CONVERT(DECIMAL(10,2),
WIPTEST.[LOTQTY]), 
CONVERT(DECIMAL(10,2), [LOTMATUNITCOST]), 
CONVERT(DECIMAL(10,2), [LOTMATWIPCOST]), 
case when rtrim(cast([LOT] as varchar)) LIKE rtrim(cast((IV30300.ITEMNMBR) as varchar))+'%' 
THEN CONVERT(DECIMAL(10,2), [LOTLABORWIPCOST]) else 0 end, 
case when rtrim(cast([LOT] as varchar)) LIKE rtrim(cast((IV30300.ITEMNMBR) as varchar))+'%' 
THEN CONVERT(DECIMAL(10,2), [LOTOHWIPCOST]) else 0 end

Select allOpen in new window

As for the second half of your union all, I don't understand why all you are adding is a b8nch of nulls and empty strings but, if it must be there, just add a null for the yield position and there's no need to group by.

Hi awking00:

Thank you, so much, for your quick response!

Unfortunately, I received the same "aggregate" error.  Below is the revised code.  Would you please tell me what I did wrong?

Thanks, again!

Software Engineer

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],
--[FORMULA], [FORMULAMAT], [FORMULALABOR], [FORMULAOH], 
rtrim(cast((select DISTINCT IV30300.ITEMNMBR) as varchar)) as [Component],
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],
[FGWEIGHT]/(SUM([LOTMATUNITCOST] + [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)+
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 [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
where LOT IS NOT NULL and LOG.FGItem = ''
and WIPTEST.[FG] = '506' and WIPTEST.[FGBATCH] = '50418G12D' and WIPTEST.[INVHDRBATCH] = '50418G12D' 
GROUP BY WIPTEST.INVHDRBATCH, WIPTEST.FGBATCH, WIPTEST.FG, WIPTEST.FGMAT, WIPTEST.FGLABOR, WIPTEST.FGOH, WIPTEST.FGCOST, WIPTEST.FGWEIGHT, WIPTEST.MATLB,
WIPTEST.LABORLB, WIPTEST.OHLB, IV30300.ITEMNMBR, WIPTEST.LOT, WIPTEST.LOTQTY, 
WIPTEST.LOTMATUNITCOST, WIPTEST.LOTMATWIPCOST, WIPTEST.LOTLABORWIPCOST,
WIPTEST.LOTOHWIPCOST

Select allOpen in new window

What is the compete error message?

That's because it's basically your original code. It doesn't contain the formula I showed and the group by statement. It is still using the distinct and select distinct wordage. The group by statement can not use aliases (e.g.You can't use [FTOG] when you're selecting  CONVERT(DECIMAL(10,2), [FGOH])  It's such a large query, I was just trying to tell you the changes needed. Here is what I think (and hope) you need. Remove those words in italics and add those in bold.
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],
--[FORMULA], [FORMULAMAT], [FORMULALABOR], [FORMULAOH],
rtrim(cast((select DISTINCT IV30300.ITEMNMBR) as varchar)) as [Component],
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],
[FGWEIGHT]/(SUM([LOTMATUNITCOST] + [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)+
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 [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
where LOT IS NOT NULL and LOG.FGItem = ''
and WIPTEST.[FG] = '506' and WIPTEST.[FGBATCH] = '50418G12D' and WIPTEST.[INVHDRBATCH] = '50418G12D'
GROUP BY WIPTEST.INVHDRBATCH, WIPTEST.FGBATCH, WIPTEST.FG, WIPTEST.FGMAT, WIPTEST.FGLABOR, WIPTEST.FGOH, WIPTEST.FGCOST, WIPTEST.FGWEIGHT, WIPTEST.MATLB,
WIPTEST.LABORLB, WIPTEST.OHLB, IV30300.ITEMNMBR, WIPTEST.LOT, WIPTEST.LOTQTY,
WIPTEST.LOTMATUNITCOST, WIPTEST.LOTMATWIPCOST, WIPTEST.LOTLABORWIPCOST,
WIPTEST.LOTOHWIPCOST
-- Add this
FGWEIGHT/(SUM( [LOTMATUNITCOST] + [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]) AS [YIELD]

from WIPTEST
GROUP BYU [INVHDRBATCH],
[FGBATCH],
[FG],
[FGMAT],
CONVERT(DECIMAL(10,2), [FGLABOR]),
CONVERT(DECIMAL(10,2), [FGOH]),
CONVERT(DECIMAL(10,2), [FGCOST]),
CONVERT(DECIMAL(10,2),[FGWEIGHT]),
[MATLB],
[LABORLB],
[OHLB],
rtrim(cast(IV30300.ITEMNMBR as varchar),
rtrim(cast([LOT] as varchar)),
CONVERT(DECIMAL(10,2),
WIPTEST.[LOTQTY]),
CONVERT(DECIMAL(10,2), [LOTMATUNITCOST]),
CONVERT(DECIMAL(10,2), [LOTMATWIPCOST]),
case when rtrim(cast([LOT] as varchar)) LIKE rtrim(cast((IV30300.ITEMNMBR) as varchar))+'%'
THEN CONVERT(DECIMAL(10,2), [LOTLABORWIPCOST]) else 0 end,
case when rtrim(cast([LOT] as varchar)) LIKE rtrim(cast((IV30300.ITEMNMBR) as varchar))+'%'
THEN CONVERT(DECIMAL(10,2), [LOTOHWIPCOST]) else 0 end

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'

You will need to remove the select distinct words from the formula line I showed by mistake -
FGWEIGHT/(SUM( [LOTMATUNITCOST] + [LOTMATWIPCOST] + case when rtrim(cast([LOT] as varchar)) LIKE rtrim(cast(( 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((IV30300.ITEMNMBR) as varchar))+'%'
 THEN CONVERT(DECIMAL(10,2), [LOTOHWIPCOST]) else 0 end as [LOTOHWIPCOST]) AS [YIELD]