March 26, 2012 at 3:10 pm
We have a machine build project that will consist of one parent Job Order and multiple sub Job Orders. Material cost can come from several different sources and I can sum the cost from each source independently but would like to create a function that would return the total material costs for the project... summing the 4 sources. I get correct results from the following query but of course I can't convert this into a scalar function. Any suggestions?
if object_id('tempdb..#tExtCost') is not null drop table #tExtCost
create table #tExtCost
(ExtCost numeric(15,2))
insert into #tExtCost
(ExtCost)
select
sum(a.frcpqty * a.fucost) as ExtCost
from vpoitem a
inner join vpomast b on a.fpono = b.fpono
where a.fjokey like '25807' + '%' and b.fstatus <>'CANCELLED' and a.frcpqty>0
and (a.fmultirls = 'n' or (a.fmultirls = 'y' and ltrim(a.frelsno) <> '0')) and len(a.fparentpo) = 0
and left(a.fpono,1) <>'s'
and a.frcpdate <= '5/28/2011'
UNION
select
isnull(sum(-1*a.fqtyshipr * a.fnorgucost),0) as ExtCost
from vpoitem a
inner join vpomast b on a.fpono = b.fpono
where a.fjokey like '25807' + '%' and b.fstatus <>'CANCELLED' and b.fctype='r'
and a.fdateship <= '5/28/2011'
UNION
select
sum(-1*(a.fqty)*a.fcost) as ExtCost
from vintran a
left join vinmast b on a.fpartno = b.fpartno and a.fcpartrev = b.frev
where (a.ftojob like '25807' + '%' or a.ffromjob like '25807' + '%') and a.ftype in ('t','i')
and a.fdate <= '5/28/2011'
UNION
select
sum(fqty_req * fcost) as ExtCost
from vocmisc
where fjob_so like '25807' + '%'
and fdate <= '5/28/2011'
select sum(ExtCost)
From #tExtCost
March 26, 2012 at 3:55 pm
You don't say what you want it to be a function of. I assume jobkey and maybe date.
My recommendation is DON'T use a scalar UDF. You will probably get better performance by using a correlated subquery or a CROSS/OUTER APPLY and you can use a view if you need to reference it many times.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 26, 2012 at 8:16 pm
You can use Mr. Celko's view but if you must have a scalar function, you can do it with the following (I'll let you figure out how to add the function preamble):
;WITH Costs AS (
select
sum(a.frcpqty * a.fucost) as ExtCost
from vpoitem a
inner join vpomast b on a.fpono = b.fpono
where a.fjokey like '25807' + '%' and b.fstatus <>'CANCELLED' and a.frcpqty>0
and (a.fmultirls = 'n' or (a.fmultirls = 'y' and ltrim(a.frelsno) <> '0')) and len(a.fparentpo) = 0
and left(a.fpono,1) <>'s'
and a.frcpdate <= '5/28/2011'
UNION
select
isnull(sum(-1*a.fqtyshipr * a.fnorgucost),0) as ExtCost
from vpoitem a
inner join vpomast b on a.fpono = b.fpono
where a.fjokey like '25807' + '%' and b.fstatus <>'CANCELLED' and b.fctype='r'
and a.fdateship <= '5/28/2011'
UNION
select
sum(-1*(a.fqty)*a.fcost) as ExtCost
from vintran a
left join vinmast b on a.fpartno = b.fpartno and a.fcpartrev = b.frev
where (a.ftojob like '25807' + '%' or a.ffromjob like '25807' + '%') and a.ftype in ('t','i')
and a.fdate <= '5/28/2011'
UNION
select
sum(fqty_req * fcost) as ExtCost
from vocmisc
where fjob_so like '25807' + '%'
and fdate <= '5/28/2011'
)
SELECT SUM(ExtCost) FROM Costs
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 26, 2012 at 10:01 pm
I agree with not creating a scalar UDF. They are prone to horrible performance. Drew is spot on that a correlated subquery or APPLY is better approach. Like he said make a view and the CROSS/OUTER APPLY.
Paul White has a great article on APPLY here. http://www.sqlservercentral.com/articles/APPLY/69953/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2012 at 9:57 am
Thank you everyone for your replies. The simple solution was to create the view that returned the date, job number and extended cost.
Mr Celko, I do appologize if posting my scratch-pad, trying-to-hammer-out-a-solution query upset your SQL sensabilities. Some of us do lack the finess of formal education on the subject and I admit my brute force methods of trying to get the job done may not be best practice but that is why I look to resources like this forum for guidance. I do appreciate that you took the time to respond and thank you for resetting my thinking so that I now have a more elegant and efficient solution. In regards to naming and normalization, you'll have to take that up with our ERP vendor.
March 27, 2012 at 10:09 am
mee.ron (3/27/2012)
Thank you everyone for your replies. The simple solution was to create the view that returned the date, job number and extended cost.Mr Celko, I do appologize if posting my scratch-pad, trying-to-hammer-out-a-solution query upset your SQL sensabilities. Some of us do lack the finess of formal education on the subject and I admit my brute force methods of trying to get the job done may not be best practice but that is why I look to resources like this forum for guidance. I do appreciate that you took the time to respond and thank you for resetting my thinking so that I now have a more elegant and efficient solution. In regards to naming and normalization, you'll have to take that up with our ERP vendor.
Glad you found a solution and thanks for letting us know.
Don't let that bulldog Celko get to you. He means well. He is incredibly knowledgeable about SQL since he served on the standards committee for a long time. Unfortunately that means he spent a lot of time in perfect scenarios and has totally forgotten about those of us that inherit unbelievably craptastic code from vendors or the people who created the mess and then left work. His online persona is very gruff and condescending. By all accounts that is intentional and that in person he is one of the nicest people around. Don't let his attitude dissuade you from coming back for help in the future.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply