August 9, 2016 at 9:48 am
I have the following select statement where i am trying to pull period Revenue along with Total Revenue for engagements. The Rev_main table has a period column so i am trying to setup the case statement so that it will pull the period Revenue based on the period of the system date. We have a CalcPeriod function that will return the period of a date field in a table but we need today's date. Right now just to get data to pull into my query, i hard coded this months period. Ideally i would like the
Current Syntax
SELECT rev.sales_doc_no
, period_rev = SUM(CASE WHEN period = '201704' THEN amount
ELSE 0
END)
, todate_rev = SUM(amount)
FROM rev_main rev
GROUP BY rev.sales_doc_no;
Expected Syntax
Select rev.sales_doc_no
period_rev = SUM(CASE WHEN Period = select dbo.calcperiod(value) from sys_registry where system_code = 'gbl' and name = 'reportdate' THEN amount ELSE 0 END),
todate_rev = SUM(amount)
FROM rev_main rev
Group By rev.sales_doc_no
Any thoughts?
August 9, 2016 at 9:56 am
Is this query:
select dbo.calcperiod(value) from sys_registry where system_code = 'gbl' and name = 'reportdate'
related any way to the outer query?
Will it return a single row? If so, why don't you assign the value to a variable? What's inside the calcperiod function? Scalar functions are bad for performance, and should be avoided most of the times.
August 9, 2016 at 9:59 am
Reformatted slightly to make easier to read.
I'm guessing everything is in the same table? Luis has a good question on the relationships. You want to be careful.
If the function is an inline table valued function, maybe a cross apply?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 9, 2016 at 10:26 am
Here are two options:
declare @Period char(6);
select @Period = dbo.calcperiod(value) from sys_registry where system_code = 'gbl' and name = 'reportdate';
select rev.sales_doc_no
period_rev = SUM(CASE WHEN Period = @Period THEN amount ELSE 0 END),
todate_rev = SUM(amount)
from rev_main rev
Group By rev.sales_doc_no;
-- or
select rev.sales_doc_no
period_rev = SUM(CASE WHEN Period = ca1.ReportPeriod THEN amount ELSE 0 END),
todate_rev = SUM(amount)
from rev_main rev
cross apply (select dbo.calcperiod(value) from sys_registry where system_code = 'gbl' and name = 'reportdate')ca1(ReportPeriod)
group By rev.sales_doc_no;
I would look at rewriting the scalar function dbo.calcperiod to a itvf (in-line table valued function) and modify the second option to use it instead of the scalar function.
August 9, 2016 at 11:43 am
I would look at rewriting the scalar function dbo.calcperiod to a itvf (in-line table valued function) and modify the second option to use it instead of the scalar function.
Why, Lynn? While I love inline table-valued functions as much as any decent right-thinking person, there is no advantage to one here because the scalar function is not being called on every row of the query. It is being used prior to the query to assign a value to the @PERIOD variable. This value doesn't change from row to row, so what performance gain can be expected?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 9, 2016 at 11:47 am
thanks Lynn. this solved my issue and I did not make a change to the function
August 9, 2016 at 11:58 am
The Dixie Flatline (8/9/2016)
I would look at rewriting the scalar function dbo.calcperiod to a itvf (in-line table valued function) and modify the second option to use it instead of the scalar function.
Why, Lynn? While I love inline table-valued functions as much as any decent right-thinking person, there is no advantage to one here because the scalar function is not being called on every row of the query. It is being used prior to the query to assign a value to the @PERIOD variable. This value doesn't change from row to row, so what performance gain can be expected?
Depending on the complexity, the statement calling the function (I'm not sure about the function itself) wouldn't be able to run in parallel. There's also an additional cost of just calling the scalar function compared to just running the code directly.
August 9, 2016 at 12:01 pm
Use of a variable in a query keeps it from running in parallel?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 9, 2016 at 12:14 pm
The Dixie Flatline (8/9/2016)
Use of a variable in a query keeps it from running in parallel?
No, the use of a scalar function in the query.
August 9, 2016 at 12:28 pm
tstagliano (8/9/2016)
I have the following select statement where i am trying to pull period Revenue along with Total Revenue for engagements. The Rev_main table has a period column so i am trying to setup the case statement so that it will pull the period Revenue based on the period of the system date. We have a CalcPeriod function that will return the period of a date field in a table but we need today's date. Right now just to get data to pull into my query, i hard coded this months period. Ideally i would like theCurrent Syntax
SELECT rev.sales_doc_no
, period_rev = SUM(CASE WHEN period = '201704' THEN amount
ELSE 0
END)
, todate_rev = SUM(amount)
FROM rev_main rev
GROUP BY rev.sales_doc_no;
Expected Syntax
Select rev.sales_doc_no
period_rev = SUM(CASE WHEN Period = select dbo.calcperiod(value) from sys_registry where system_code = 'gbl' and name = 'reportdate' THEN amount ELSE 0 END),
todate_rev = SUM(amount)
FROM rev_main rev
Group By rev.sales_doc_no
Any thoughts?
While the options given will work, the reason that your original query did not work is that subqueries must always be enclosed in parentheses, because it otherwise becomes too difficult to determine where the subquery ends. Your original query can be rewritten as follows:
Select rev.sales_doc_no
period_rev = SUM(CASE WHEN Period = ( select dbo.calcperiod(value) from sys_registry where system_code = 'gbl' and name = 'reportdate' ) THEN amount ELSE 0 END),
todate_rev = SUM(amount)
FROM rev_main rev
Group By rev.sales_doc_no
Of course, this rewrite will only work if the subquery returns a single value.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 9, 2016 at 12:31 pm
select @Period = dbo.calcperiod(value) from sys_registry where system_code = 'gbl' and name = 'reportdate';
This is my point. The first solution does NOT use the scalar function in the main query. It simply uses it once to assign a value to @Period. I understand that there might be a touch of overhead launching this as a separate query, but surely we're talking milliseconds. This isn't the same case as putting a scalar function in a query to supply values to multiple rows.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply