Are the posted questions getting worse?

Question

Post reply

Are the posted questions getting worse?

Viewing 15 posts - 22,531 through 22,545 (of 66,000 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Alvin Ramard (12/15/2010)

Lynn Pettis (12/15/2010)

I'm not in a position to test, but don't you get the same issue when using decimal(19,4) as you do when usinf money?

Now I may be wrong, but I think you don't have that much to worry about in normal accounting applications. The problem is more likely to surface when you start multiply and dividing money types which really doesn't make much sense to me.

Using money yields 234.50

Using decimal(19, 4) yields 234.56

Using decimal(19, 2) also yields 234.56

This is not good! :w00t:

Actually, it's great. Folks like me get to go in an fix this "terrible problem" for a whole lot of money. But I'll let you in on the secret... always multiply before you divide and even the MONEY datatype will work correctly. 😉

--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)
Intro to Tally Tables and Functions

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

Jeff Moden (12/15/2010)

Brandie Tarvin (12/15/2010)

I think I just found it. Money rounds funny when being used in division and multiplication.

Interesting that I never ran into this before. I should make a SQL Server question out of this...

It's not the Money datatype that's responsible for that problem. Any 4 decimal place datatype would have such a problem. Most handheld calculators calculate to 15 decimal places and will only display up to 13.

Agh... sorry. Lynn already beat me to that observation.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

GSquared (12/15/2010)

Kit G (12/15/2010)

Lynn Pettis (12/15/2010)

I'm not in a position to test, but don't you get the same issue when using decimal(19,4) as you do when usinf money?

Now I may be wrong, but I think you don't have that much to worry about in normal accounting applications. The problem is more likely to surface when you start multiply and dividing money types which really doesn't make much sense to me.

I think the problem comes about if you start doing interest rates which will have multiplication in them and then have to figure out a monthly payment which will probably include some division. Not that i have tested it, but I seem to remember reading a thread/article on it where the errors in using MONEY for actual financial transactions was gone over. It was concluded that using DECIMAL was a much better solution and more likely to keep you out of fraud investigations. But this is from a somewhat dim memory noted as a curiosity as I don't deal with the MONEY data type at all.

I remember one from a couple of years ago (I think it was that long) with Matt Miller, Jeff, and a few others. I don't remember all the details, but I do remember Matt essentially proving that the money datatype will break normal banking applications, while decimal will work just fine. I think this was based on actual work for actual banks. Can't find the conversation now, but I do remember it.

I can't find it either but I also remember it... most mortage and interest calculations simply require more than 4 decimal places for accuracy before rounding to 2 decimal places for the final display and that's the only reason why the Money (or DECIMAL(x,4)) datatype isn't good for banking.

--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)
Intro to Tally Tables and Functions

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

Heh... I just can't keep up. It would appear you folks found the thread we were talking about.

BTW... I don't use FLOAT anymore either. Too many people were copying my code and using it the wrong way. Now I do it "calculator style"... 15 decimal places for anything having to do with banking.

--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)
Intro to Tally Tables and Functions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Brandie Tarvin (12/15/2010)

...Money rounds funny...

That's an Abba song. Now I can't get it out of my head - darn, that's a Kylie song...we gotta get out of this place....aaaaagghhh!!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 6

Chris Morris-439714 (12/16/2010)

Brandie Tarvin (12/15/2010)

...Money rounds funny...

That's an Abba song. Now I can't get it out of my head - darn, that's a Kylie song...we gotta get out of this place....aaaaagghhh!!

And it has a Genesis version too :w00t:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 7

So, I have this query that yesterday is working fine and quick when I left outer join three tables. But when I change them over to inner joins takes forever to run before killing the tempdb. Cannot for the life of me figure out what's wrong. Today, I take a look at the estimated execution plan and find one of the inner joins saying "Warnings: No Join Predicate".

I'm freaking out here because I know the key I joined on exists in both tables 2 & 3 and why won't the damn thing realize it has a predicate?

So I move the three joins to a separate query on their own (away from the rest of the query) to see what's going on. I nix the first table because that's not where the problem is, try to run a simple select on my join columns...

And get a warning that alias used on my third (now second) join doesn't exist. Because, apparently, I joined table 2 back to table 1 instead of table 3 (all of which have the same column in them).

It's going to be one of those weeks, isn't it?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 8

Brandie Tarvin (12/16/2010)

So, I have this query that yesterday is working fine and quick when I left out......

<Snip>

It's going to be one of those weeks, isn't it?

Sounds dangerously like a technical issue. We've already been told off once this week for technical chat in 'The Thread' :-). Post to the forums with code , DDL ,execution plans

Clear Sky SQL
My Blog[/url]

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 9

Dave Ballantyne (12/16/2010)

Brandie Tarvin (12/16/2010)

So, I have this query that yesterday is working fine and quick when I left out......

<Snip>

It's going to be one of those weeks, isn't it?

Sounds dangerously like a technical issue. We've already been told off once this week for technical chat in 'The Thread' :-). Post to the forums with code , DDL ,execution plans

Oh, I've got the issue resolved. I'm just feeling really really foolish right now because I know better.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Dave Ballantyne (12/16/2010)

Brandie Tarvin (12/16/2010)

So, I have this query that yesterday is working fine and quick when I left out......

<Snip>

It's going to be one of those weeks, isn't it?

Sounds dangerously like a technical issue. We've already been told off once this week for technical chat in 'The Thread' :-). Post to the forums with code , DDL ,execution plans

Heh careful Dave, you know what Brandie's like when geeks start to get all masterful 😉 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 11

Dave Ballantyne

SSC-Dedicated

Points: 33556

December 16, 2010 at 6:29 am

#1263412

Brandie Tarvin (12/16/2010)

Oh, I've got the issue resolved. I'm just feeling really really foolish right now because I know better.

Feel foolish too as i didnt read the rest of you post after the technical content :doze: 🙂

Clear Sky SQL
My Blog[/url]

Kit G SSCrazy Points: 2888 More actions · Answer 12

Luke L (12/15/2010)

Any chance this is what you were looking for?

http://qa.sqlservercentral.com/Forums/Topic454501-373-1.aspx

That thread actually doesn't ring a bell, but it's a good one. It was another thread, also might have been noted in a book I read. Just don't remember. The only thing I took away from the thread was not to use MONEY for actual money calculations because of rounding errors. I think that thread also had a reason/theory why MS still included it, perhaps a holdover from Access or something. Or perhaps included so that people could transfer over their Access databases to SQL easier without going through conniption fit at the lack of the MONEY datatype.

-- Kit

GSquared SSC Guru Points: 260824 More actions · Answer 13

Luke L (12/15/2010)

GSquared (12/15/2010)

Kit G (12/15/2010)

Lynn Pettis (12/15/2010)

I'm not in a position to test, but don't you get the same issue when using decimal(19,4) as you do when usinf money?

Now I may be wrong, but I think you don't have that much to worry about in normal accounting applications. The problem is more likely to surface when you start multiply and dividing money types which really doesn't make much sense to me.

I think the problem comes about if you start doing interest rates which will have multiplication in them and then have to figure out a monthly payment which will probably include some division. Not that i have tested it, but I seem to remember reading a thread/article on it where the errors in using MONEY for actual financial transactions was gone over. It was concluded that using DECIMAL was a much better solution and more likely to keep you out of fraud investigations. But this is from a somewhat dim memory noted as a curiosity as I don't deal with the MONEY data type at all.

I remember one from a couple of years ago (I think it was that long) with Matt Miller, Jeff, and a few others. I don't remember all the details, but I do remember Matt essentially proving that the money datatype will break normal banking applications, while decimal will work just fine. I think this was based on actual work for actual banks. Can't find the conversation now, but I do remember it.

Any chance this is what you were looking for?

http://qa.sqlservercentral.com/Forums/Topic454501-373-1.aspx

Not the one I'm thinking of, but good nonetheless.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 14

Brandie Tarvin (12/16/2010)

So, I have this query that yesterday is working fine and quick when I left outer join three tables. But when I change them over to inner joins takes forever to run before killing the tempdb. Cannot for the life of me figure out what's wrong. Today, I take a look at the estimated execution plan and find one of the inner joins saying "Warnings: No Join Predicate".

I'm freaking out here because I know the key I joined on exists in both tables 2 & 3 and why won't the damn thing realize it has a predicate?

So I move the three joins to a separate query on their own (away from the rest of the query) to see what's going on. I nix the first table because that's not where the problem is, try to run a simple select on my join columns...

And get a warning that alias used on my third (now second) join doesn't exist. Because, apparently, I joined table 2 back to table 1 instead of table 3 (all of which have the same column in them).

<headthunk>

It's going to be one of those weeks, isn't it?

I used to know a DBA who insisted on using 1-letter aliases for all tables in queries, because it made them "faster to type" in the other parts of the query. One day, he brought the production server to its knees because he got lost in his aliases, and joined A to B on A.Col = A.Col. On two multi-million row tables.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 15

GSquared (12/16/2010)

I used to know a DBA who insisted on using 1-letter aliases for all tables in queries, because it made them "faster to type" in the other parts of the query. One day, he brought the production server to its knees because he got lost in his aliases, and joined A to B on A.Col = A.Col. On two multi-million row tables.

YIKES!

At least I caught my issue in Development.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.