Dynamic Query Problem

Question

Dynamic Query Problem

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Now wait a second here...

you've now jumped onto something entirely different by using irrational numbers and decimal numbers as if they were interchangeable. Naughty naughty there Sergiy - you of all people ought to know better than that.

You cannot take an apple and an orange, and hope to end up with lemonade....

Back to basic algebra for all of you....hehe

Also - for "precision losing" operations, you need to first EXPAND the precision BEFORE the operation, and then DECREASE the PRECISION when you're done.... There are rules about this stuff....(and no - I don't remember them off hand either). The one thing I do recall is that you're supposed to do the "precision losing operation" (a.k.a. the division) AFTER the multiplication, otherwise you're compounding the error you introduce. Floating point arithmetics principles.....

----------------------------------------------------------------------------------
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?

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

you need to first EXPAND the precision BEFORE the operation, and then DECREASE the PRECISION when you're done....

Heh... and what better way to do that than to use FLOAT as Sergiy suggests?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Jeff Moden (11/30/2007)
you need to first EXPAND the precision BEFORE the operation, and then DECREASE the PRECISION when you're done....
Heh... and what better way to do that than to use FLOAT as Sergiy suggests?

That's the correct way, as long as you cast it back to something smaller. So - you'd want to cast it to , say decimal(20,4), first, use it in a calculation, then cast it back to (18,2).

Also - your test has a flaw in it, since you start by trying to cram 4 digits after the decimal point into 2 digits after the dot. So you already start "behind the 8-ball", because you're not even looking at the right "spot". Once you've done something like that (losing precision in the process), one or more digits no longer is significant.

For example - in your case - by casting it to 2 digits first, then performing the operation, you're down to only 1 significant digit (since you only had 2 to begin with).

It's not that the decimal arithmetic is "off", you're comparing two entirely different operations. After all, 0.33 is not at all the same as 1/3.

So - giving you a "more valid" scenario...

use testing

go

DECLARE @F FLOAT

SET @F = .3333

DECLARE @D DECIMAL(6,4) --you were truncating half of the digits by using 4,2

SET @D = .3333

--Simulates precision/scale lost during a calculation

SELECT N,

[N*@F] = N*@F,

[N*@D] = N*@D,

[STR(N*@F,6,4)] = STR(N*@F,6,4),

[ROUND(N*@F,4)] = ROUND(N*@F,4),

[CONVERT(DECIMAL(6,4),N*@F)] = CONVERT(DECIMAL(6,4),N*@F),

[CONVERT(DECIMAL(6,4),N*@F)] = CONVERT(DECIMAL(6,4),N*@F)

FROM dbo.Tally WHERE N <= 100

----------------------------------------------------------------------------------
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?

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Also - your test has a flaw in it, since you start by trying to cram 4 digits after the decimal point into 2 digits after the dot. So you already start "behind the 8-ball",

Nope... wasn't a flaw... was a demonstration of a "flaw". 😀 Was meant to show what happens if some calculation results in .3333 but the only place it has to land is a DECIMAL(x,2). That's why I put the comment on the row...

[font="Courier New"]--Simulates precision/scale lost during a calculation [/font]

That's the correct way, as long as you cast it back to something smaller

Preaching to the choir... the demo I wrote does exactly that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Cool - Just wanted to be sure we were all there:)

I'll be the one yodeling in the back....

----------------------------------------------------------------------------------
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?

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Jeff Moden (11/30/2007)
Heh... oh yeah... almost forgot...
"NO... I am NOT smarter than a 5^th grader!" 😛

In our country it's "I am NOT smarter than 10 years old".

But it still true. 😉

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

Lets see... displaced Russian living in New Zealand... which country would that be?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Not quite Russian...

In Russian it would be "Sergey".

😉

But key word is "living in".

Of course. 🙂

http://tvnz.co.nz/view/page/1180693

_____________
Code for TallyGenerator

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Sergiy (12/2/2007)
Jeff Moden (11/30/2007)
Heh... oh yeah... almost forgot...
"NO... I am NOT smarter than a 5^th grader!" 😛
In our country it's "I am NOT smarter than 10 years old".
But it still true. 😉

Don't feel bad about that - my eight year old nephew still whips my butt @ most video games, and I'm pretty sure that this guy would beat me too....click here

Now - we're all in trouble if they start trying to teach him T-SQL....

----------------------------------------------------------------------------------
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?

Sergiy SSC Guru Points: 110208 More actions · Answer 10

Video games are OK.

But when after competion of 4th year in University we were invoilved in checking enrolling exams...

Gosh! Just 4 year ago we all completed similar tasks! And completed it at least good! Otherwise we coulds not enroll!

And just after 4 years we looked at those tasks absolutely helplessly.

Well, finally we could find solutions, but it took so much effort! Who could imagine?

That was a good lesson for the whole life.

_____________
Code for TallyGenerator