Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 how to write query Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, August 29, 2013 11:38 PM
 Valued Member Group: General Forum Members Last Login: Monday, April 11, 2016 5:34 AM Points: 69, Visits: 224
 HelloI have to generate a report in which amount should be 13 digits.e.g 2000- convert it into paisa 200000 then 13 digit amount is 0000000200000but I want it to do in SQl query..Select (amount*100) from table then need to add 0s as per count 13-len(amount*100) before the amountplease guide me..
Post #1490005
 Posted Thursday, August 29, 2013 11:51 PM
 Valued Member Group: General Forum Members Last Login: Monday, April 11, 2016 5:34 AM Points: 69, Visits: 224
 I got the solutionUse Replicate functionSELECT '200000'SELECT REPLICATE('0',7)+'200000'
Post #1490007
 Posted Friday, August 30, 2013 3:59 PM
 SSC Eights! Group: General Forum Members Last Login: 2 days ago @ 4:45 AM Points: 888, Visits: 861
 SELECT replace(str(amt, 13), ' ', '0') Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490324
 Posted Friday, August 30, 2013 11:34 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 3:23 PM Points: 42,036, Visits: 39,415
 If you're interested in speed, STR isn't the way to do it. And since it does a FLOAT conversion in the process, it can slip into the same approximation "problems" as FLOAT (although not likely with only 13 digits). Then there's the rounding errors that it produces because of the approximations it does.My recommendation is to avoid STR(). --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." Helpful Links:How to post code problemsHow to post performance problems
Post #1490352
 Posted Friday, August 30, 2013 11:42 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 3:23 PM Points: 42,036, Visits: 39,415
 amitsingh308 (8/29/2013)HelloI have to generate a report in which amount should be 13 digits.e.g 2000- convert it into paisa 200000 then 13 digit amount is 0000000200000but I want it to do in SQl query..Select (amount*100) from table then need to add 0s as per count 13-len(amount*100) before the amountplease guide me..One of the fastest ways of doing such a thing is with a little hardcoding. You might want to avoid things like REPLICATE to calculate a certain number of concatenated zero's because its just not as fast as a string literal. You should have good luck with the following...` SELECT RIGHT('0000000000000' + CAST((Amt*100) AS VARCHAR(13)),13) FROM dbo.SomeTable;`Of course, such formatting should probably be done in the front-end, if there is one. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #1490354
 Posted Saturday, August 31, 2013 3:10 AM
 SSC Eights! Group: General Forum Members Last Login: 2 days ago @ 4:45 AM Points: 888, Visits: 861
 Jeff Moden (8/30/2013)If you're interested in speed, STR isn't the way to do it.It's unlikely that the speed difference would be significant. Yes, if you need to do this on 10 million rows on straight SELECT from a single table, you might see something. If you instead query umpteen tables with a suboptimal query plan, this will be one your smallest problem. And since it does a FLOAT conversion in the process, it can slip into the same approximation "problems" as FLOAT (although not likely with only 13 digits).True, the argument will be converted to float, and this could pose problems with you are working with 16-digit bigint numbers you want to pad to 18 digits. An integer value is typically converted to float with the exponent as 0, meaning that there is no loss of precision.I like str() + replace() because is short and concise. Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490363
 Posted Saturday, August 31, 2013 10:15 AM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 3:23 PM Points: 42,036, Visits: 39,415
 Erland Sommarskog (8/31/2013)Jeff Moden (8/30/2013)If you're interested in speed, STR isn't the way to do it.It's unlikely that the speed difference would be significant. Yes, if you need to do this on 10 million rows on straight SELECT from a single table, you might see something. If you instead query umpteen tables with a suboptimal query plan, this will be one your smallest problem. And since it does a FLOAT conversion in the process, it can slip into the same approximation "problems" as FLOAT (although not likely with only 13 digits).True, the argument will be converted to float, and this could pose problems with you are working with 16-digit bigint numbers you want to pad to 18 digits. An integer value is typically converted to float with the exponent as 0, meaning that there is no loss of precision.I like str() + replace() because is short and concise.I used to think the same thing for a very long time. Like me, you might want to change you mind.It may be "concise" but it's far from accurate and far from being performant.It produces rounding errors when working with decimal places simply because FLOAT is based on binary math rather than decimal math and it can't actually precisely contain certain values with it's rather short Mantissa even on small numbers.STR() is also more than twice as slow as RIGHT() concatenation method. While I agree that a 2,300ms difference in CPU time (3,800ms vs 1,500ms) for a single instance of the formula over a million rows on a relatively slow machine isn't much, just imagine if everyone on a project paid attention to such minor nuances... the end result would be code that ran twice as fast as the compeition's code. And, what does it cost? If you know how to use the RIGHT() concatenation method and even if you can't actually type quickly, the answer is measured as just a second or two to get twice the performance and much more accurate rounding.Here's a link for some testing that I did that demonstrates the performance and accuracy differences. Again, it was a while back meaning that I did the testing on my really old desktop and that causes the appearance of a larger difference in time but that doesn't affect things like the rounding errors when working with decimal numbers.http://www.sqlservercentral.com/articles/T-SQL/71565/ --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." Helpful Links:How to post code problemsHow to post performance problems
Post #1490401
 Posted Saturday, August 31, 2013 12:08 PM
 SSC Eights! Group: General Forum Members Last Login: 2 days ago @ 4:45 AM Points: 888, Visits: 861
 Jeff Moden (8/31/2013)[quote][b]It produces rounding errors when working with decimal places simply because FLOAT is based on binary math rather than decimal math and it can't actually precisely contain certain values with it's rather short Mantissa even on small numbers.That assumes decimal values. The case in point was numeric values. If you want to use str() to get right-aligned decimal values, yes, you can get errors, and if it essential that your rounding is consistent there may be a workaround:`SELECT str(1.23955, 10, 4) AS Wrong, convert(float, 1.23955) AS Why, str(round(1.23955, 4), 10, 4) AS Workaround`Note: you need to run the query from SQLCMD to see Why.STR() is also more than twice as slow as RIGHT() concatenation method. Frankly, I couldn't be bothered. I also use things like:convert(char(6), getdate(), 112) + '01'when I want the first day of the month, even if people claim other constructs are faster. I look at them and say, eh? I can do the most common date tricks with style 112 in my sleep.Developer productivity counts some times. Performance in T-SQL is about greater scheme of things. Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490412
 Posted Saturday, August 31, 2013 12:38 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 3:23 PM Points: 42,036, Visits: 39,415
 I don't need to run it from SQLCMD to see why. I've already demonstrated "Why" in the article. So far as "Developer Productivity" goes, I'm all for that. That's why I teach my Developers to learn the more performant methods... so they can do it in their sleep and won't have to rewrite code to fix performance problems that should have been avoided to begin with. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #1490415
 Posted Wednesday, September 11, 2013 9:39 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 Another way that should look familiar:`WITH Amt (Amt) AS (SELECT 2000)SELECT RIGHT(10000000000000+Amt*100,13)FROM Amt;` My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!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?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1493984

 Permissions