September 17, 2014 at 1:04 am
I am having 4 Columns Qty decimal(12,3),CF1 Decimal(12,3),CF2 Decimal(12,3),Flag TinyInt.
I want to perform following without using case please tell me how it is possible.
When value of Flag is 0 then Qty*(CF2/CF1)
When value of Flag is 1 then Qty
And i Don't want to use any functions like isnull,NullIf,IIF even not union or union all.How to do this calculation without using any function.
Actually i am having more then 100000 rows in table and if i use functions then my index might not be called.,that why want to avoid cases and functions.
September 17, 2014 at 2:21 am
shahi.alokchandra (9/17/2014)
I am having 4 Columns Qty decimal(12,3),CF1 Decimal(12,3),CF2 Decimal(12,3),Flag TinyInt.I want to perform following without using case please tell me how it is possible.
When value of Flag is 0 then Qty*(CF2/CF1)
When value of Flag is 1 then Qty
Use NULLIF() And ISNULL(). Why would you want to do this?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
September 17, 2014 at 2:33 am
((1-Flag)*Qty*(CF2/CF1) + Flag*Qty) * nullif (1-abs(sign((1-Flag)*Flag)),0)
Qty*(CF2/CF1) when Flag==0,
Qty when Flag==1,
NULL otherwise.
September 17, 2014 at 2:36 am
What's wrong with CASE? Yes, you can, but it becomes significantly less readable than the equivalent using CASE.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 17, 2014 at 2:38 am
See above. Tried to edit and posted once again instead. 🙁
September 17, 2014 at 7:18 am
Agree with Gail, but there is a shorted, more "cryptographic" version:
declare @t table (Qty decimal(12,3),CF1 Decimal(12,3),CF2 Decimal(12,3),Flag TinyInt)
insert @t select 10,2,6,0
insert @t select 10,2,6,1
select *, Qty * ((CF2/CF1) * ~cast(Flag as bit) + Flag) from @t
September 17, 2014 at 9:48 am
The simplest version I could think of.
declare @t table (Qty decimal(12,3),CF1 Decimal(12,3),CF2 Decimal(12,3),Flag TinyInt)
insert @t select 10,2,6,0
insert @t select 10,2,6,1
SELECT Qty* POWER(CF2/CF1, Flag)
FROM @t
EDIT: Unless someone proves me wrong, it cannot be made without functions or a CASE statement. At least not in SQL Server (other languages don't need a POWER function).
September 17, 2014 at 10:58 am
Try this:
declare @t table (Qty decimal(12,3),CF1 Decimal(12,3),CF2 Decimal(12,3),Flag TinyInt)
set nocount on
insert @t select 10,2,6,0
insert @t select 10,2,6,1
insert @t select 40,2,8,1
insert @t select 40,2,8,0
set nocount off
SELECT
Qty, Flag, CF2 / CF1 AS [CF2 / CF1],
Qty + (Qty * CF2 / CF1 * (1 - Flag)) - (Qty * (1 ^ Flag)) AS [desired_result?]
FROM @t
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 18, 2014 at 12:53 am
Luis Cazares (9/17/2014)
The simplest version I could think of.EDIT: Unless someone proves me wrong, it cannot be made without functions or a CASE statement. At least not in SQL Server (other languages don't need a POWER function).
declare @t table (Qty decimal(12,3),CF1 Decimal(12,3),CF2 Decimal(12,3),Flag TinyInt)
set nocount on
insert @t select 10,2,6,0
insert @t select 10,2,6,1
insert @t select 40,2,8,1
insert @t select 40,2,8,0
insert @t select 40,2,8,2
set nocount off
SELECT Flag, Qty, expr = Qty*CF2/CF1
, result = (SELECT (1-Flag)*Qty*(CF2/CF1) + Flag*Qty WHERE Flag IN (0,1))
FROM @t
September 18, 2014 at 2:23 am
shahi.alokchandra (9/17/2014)
Actually i am having more then 100000 rows in table and if i use functions then my index might not be called.,that why want to avoid cases and functions.
Just as a note... you realize that as soon as you adjust the value coming out of the index in any way it's no longer SARGable, right? No matter if you use multiplication or ISNULL(NULLIF()) or stuff it into a CASE, you've adjusted it. Some conversions can get away (I think, have to doublecheck) with it but that's about it.
Either you do all your adjustment on the parameter side of the equation or you write off the index searching.
There's a way to avoid the issue though: Since all your information is contained in a single row, use a calculated column, persist it, and index THAT for your where clauses.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 18, 2014 at 2:33 am
shahi.alokchandra (9/17/2014)
Actually i am having more then 100000 rows in table and if i use functions then my index might not be called.,that why want to avoid cases and functions.
You realise that by 'function', they mean any mathematical expression applied to the column, any built in function, any user-defined function?
Something as simple as Column + 0 is enough to not allow indexes to be used. So every single option posted here, plus the CASE version you didn't want is classified a function and hence prevents index usage
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 18, 2014 at 4:37 am
GilaMonster (9/18/2014)
shahi.alokchandra (9/17/2014)
Actually i am having more then 100000 rows in table and if i use functions then my index might not be called.,that why want to avoid cases and functions.You realise that by 'function', they mean any mathematical expression applied to the column, any built in function, any user-defined function?
Something as simple as Column + 0 is enough to not allow indexes to be used. So every single option posted here, plus the CASE version you didn't want is classified a function and hence prevents index usage
Basically, OP question is "How to perform mathematical formula without using any function?"
As I understand the formula is a method to calculate something, the function is a named/well known formula to calculate something.
What OP thinks the difference between the two are?
I would say the answer to OP question is: It is impossible, as formula and function is basically the same thing.
Or, if you don't like negative answer, I have apositive one for you:
In order to perform mathematical action/method without performing it, you should simply not perform it.
:hehe:
September 18, 2014 at 8:25 am
Can you provide the context where you plan to use that formula?
Bringing out the golden hammer to categorically stamp out any exceptions to "no functions, etc" tends to leave a lot of collateral damage with little or no gain. Often enough that kind of blanket policy tends to lead to premature optimization and other bad outcomes. Might be worth putting together a workable solution first and then seeing what kind of performance issue you actually have before trying to solve for one.
There are lots of ways to use functions etc.. that won't compromise performance. For example - using functions on content you're simply returning as part of the SELECT isn't going to change whether your indexes will get used or not (so the rule doesn't even apply); if this is the ONLY formula you're using and you use it a lot - consider creating a calculated value witht e formula in your table def: if you persist it - it can be referenced in indexes and/or included.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 18, 2014 at 10:08 am
shahi.alokchandra (9/17/2014)
Actually i am having more then 100000 rows in table and if i use functions then my index might not be called.,that why want to avoid cases and functions.
Are you using your calculation in your WHERE clause?
If it's just in the column list, you shouldn't have a problem with the index when using functions or formulas.
September 18, 2014 at 10:13 am
I believe my code above met your (implied) challenge ;-).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply