I'm trying to perform the following within a view. I'm not getting the
values which I would expect. I'm assuming I have a data type problem where
some precision is being dropped. Could someone give me a heads-up on what
I'm doing wrong? I need full precision.
SUM(((MarketPrice+MarketPriceUp100-(2*MarketPriceUp50))/(MarketPriceUp50*((1
00/10000)^2))/100)
* MarketValue)/ SUM(MarketValue) as [Convexity],
Thanks,
Tom Woods
The Baker Group, LP"Tom Woods" <twoods@.jamesbaker.com> wrote in message
news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
> I'm trying to perform the following within a view. I'm not getting the
> values which I would expect. I'm assuming I have a data type problem
> where some precision is being dropped. Could someone give me a heads-up
> on what I'm doing wrong? I need full precision.
>
> SUM(((MarketPrice+MarketPriceUp100-(2*MarketPriceUp50))/(MarketPriceUp50*(
(100/10000)^2))/100)
> * MarketValue)/ SUM(MarketValue) as [Convexity],
>
What are the types of those columns?
Integral types use integer division.
David|||All columns in the formula are 9(12,8).
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uyQL80TvFHA.1560@.TK2MSFTNGP09.phx.gbl...
> "Tom Woods" <twoods@.jamesbaker.com> wrote in message
> news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
> What are the types of those columns?
> Integral types use integer division.
> David
>|||Hi Tom
This piece of the expression (100/10000) will be carried out as integer
division, and yield 0. If you want decimal results, make at least one of the
operands a decimal (100.0/10000)
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tom Woods" <twoods@.jamesbaker.com> wrote in message
news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
> I'm trying to perform the following within a view. I'm not getting the
> values which I would expect. I'm assuming I have a data type problem
> where some precision is being dropped. Could someone give me a heads-up
> on what I'm doing wrong? I need full precision.
>
> SUM(((MarketPrice+MarketPriceUp100-(2*MarketPriceUp50))/(MarketPriceUp50*(
(100/10000)^2))/100)
> * MarketValue)/ SUM(MarketValue) as [Convexity],
> Thanks,
> Tom Woods
> The Baker Group, LP
>|||When I change it to the following I get an error. Could the problem be with
the exponent? Is there a different way of performing the exponential?
((100.0/10000)^2)
BTW, I get the following error when changing 100 to 100.0
--
Microsoft SQL-DMO (ODBC SQLState: 42000)
--
Error 403: Invalid operator for data type. Operator equals boolean XOR, type
equals numeric.
--
OK
--
Thanks,
Tom
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:usIhD4TvFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi Tom
> This piece of the expression (100/10000) will be carried out as integer
> division, and yield 0. If you want decimal results, make at least one of
> the operands a decimal (100.0/10000)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tom Woods" <twoods@.jamesbaker.com> wrote in message
> news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
>
>|||^ operator is bitwise exclusive OR operation for integers. For getting
exponential values as results you might want to try POWER function like:
SELECT POWER( 100.0/10000, 2 )
Anith|||Can you use (1.0/10000) instead of ((100.0/10000)^2)?
Perayu
"Tom Woods" <twoods@.jamesbaker.com> wrote in message
news:uVvmS$TvFHA.596@.TK2MSFTNGP12.phx.gbl...
> When I change it to the following I get an error. Could the problem be
> with the exponent? Is there a different way of performing the
> exponential?
> ((100.0/10000)^2)
>
> BTW, I get the following error when changing 100 to 100.0
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 403: Invalid operator for data type. Operator equals boolean XOR,
> type equals numeric.
> --
> OK
> --
> Thanks,
> Tom
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:usIhD4TvFHA.1032@.TK2MSFTNGP12.phx.gbl...
>|||I was able to get it to work by using the POWER function and also changing
the integer values to have precision.
Thanks,
Tom
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:uPKrC0UvFHA.908@.tk2msftngp13.phx.gbl...
> Can you use (1.0/10000) instead of ((100.0/10000)^2)?
> Perayu
> "Tom Woods" <twoods@.jamesbaker.com> wrote in message
> news:uVvmS$TvFHA.596@.TK2MSFTNGP12.phx.gbl...
>
Showing posts with label perform. Show all posts
Showing posts with label perform. Show all posts
Thursday, March 29, 2012
Monday, March 12, 2012
Formatted procs
Can somebody provide me an argument on how nicely formatted and indented procs perform better. I am currently formatting a 1000 line proc just to understand what it does ... Maybe your argument will help convince my development team to write neat procs :)Originally posted by Enigma
Can somebody provide me an argument on how nicely formatted and indented procs perform better. I am currently formatting a 1000 line proc just to understand what it does ... Maybe your argument will help convince my development team to write neat procs :)
As far as my knowledge goes, indentation and formatting of the proc has nothing got to do with the performance. Its just that, u improve the readability of the code, and believe me it is very much required. I had stumbled upon a stored proc which was not working properly, it was about 1500 lines and the code was so badly written without any comments, that it took hours first to format it, then to understand the whole thing. Properly formatted code performs better in the long run, meaning after six months of coding if u have to debug the proc, it wouldn't be a mess.|||Can somebody supply me with an argument as to why my kids should help keep the house clean? They claim that it provides just as much shelter with crap strewn all over the place, because it is the roof that counts. I say that when the place is cluttered it makes it harder to find things they have lost and it is more likely that things will get accidentally broken, but they aren't buying this. So I am currently cleaning up a 1000 sqr foot floor in order to make the area a more practical and inviting place to live.
I think I will just tell my development team (oops, I mean kids) that if they don't keep the place clean they will not get their allowance.|||Make the sentence for writing undocumented, poorly formatted code be a stint doing nothing but maintenance? At least in the shops that I've worked in, that would make them consider suicide as a viable alternative!
-PatP|||Oh, I like that one!|||Buy a big bat....
I gotta find that thread where we had a war about this...
don't forget change conmtrol in the comments up front...
does anyone comment out old code and leave it if it's being altered?|||I don't even want to start this one...Naming conventions, coding standards, change control, release management, and the most dreaded one, - documentation...|||I've got a little Perl script that shrouds T-SQL. It removes all the extraneous whitespace and renames the variables, cursors, and temp tables @.v1, @.v2, @.v3... The thing is a work of pure evil, but you could always ask them to debug a 1500 line stored procedure that you'd passed through the shrouder.
-PatP|||Ha ha .. i would love to have that script ;)
Can somebody provide me an argument on how nicely formatted and indented procs perform better. I am currently formatting a 1000 line proc just to understand what it does ... Maybe your argument will help convince my development team to write neat procs :)
As far as my knowledge goes, indentation and formatting of the proc has nothing got to do with the performance. Its just that, u improve the readability of the code, and believe me it is very much required. I had stumbled upon a stored proc which was not working properly, it was about 1500 lines and the code was so badly written without any comments, that it took hours first to format it, then to understand the whole thing. Properly formatted code performs better in the long run, meaning after six months of coding if u have to debug the proc, it wouldn't be a mess.|||Can somebody supply me with an argument as to why my kids should help keep the house clean? They claim that it provides just as much shelter with crap strewn all over the place, because it is the roof that counts. I say that when the place is cluttered it makes it harder to find things they have lost and it is more likely that things will get accidentally broken, but they aren't buying this. So I am currently cleaning up a 1000 sqr foot floor in order to make the area a more practical and inviting place to live.
I think I will just tell my development team (oops, I mean kids) that if they don't keep the place clean they will not get their allowance.|||Make the sentence for writing undocumented, poorly formatted code be a stint doing nothing but maintenance? At least in the shops that I've worked in, that would make them consider suicide as a viable alternative!
-PatP|||Oh, I like that one!|||Buy a big bat....
I gotta find that thread where we had a war about this...
don't forget change conmtrol in the comments up front...
does anyone comment out old code and leave it if it's being altered?|||I don't even want to start this one...Naming conventions, coding standards, change control, release management, and the most dreaded one, - documentation...|||I've got a little Perl script that shrouds T-SQL. It removes all the extraneous whitespace and renames the variables, cursors, and temp tables @.v1, @.v2, @.v3... The thing is a work of pure evil, but you could always ask them to debug a 1500 line stored procedure that you'd passed through the shrouder.
-PatP|||Ha ha .. i would love to have that script ;)
Formating XML field in report
Hi:
The database we are reporting from (DB2 running on AS/400) has a field with
the data type of CLOB. We would like to perform an XSLT on this field within
the same report that contains more "normal" data (strings, numeric, etc.)
types.
This database contains error and status messages that are generated as
various processes are run. We would like to be able to present the CLOB data
on the report based on the type of process that produced the data. In other
words, if process A produced the entery in to the database, on the report for
process A, format the CLOB data this way, if process B, then the CLOB data is
represented in another way. The data in the CLOB is XML and each process has
its own schema.
Right now I am stumped. Is this possible? Can you point me in the right
direction?
Thanks.You're probably going to need to write a custom function (or perhaps a
custom assembly if you're going to need to read XSLTs from disk) to do this.
You could pass the data along with the process to your custom function which
would apply the transform and return the string to display in the textbox.
E.g. =Code.CLOBClass.Convert(Fields!CLOBData.Value,Fields!Process.Value)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:95B8ADFD-2A23-4DD5-A039-971AC84B5AF9@.microsoft.com...
> Hi:
> The database we are reporting from (DB2 running on AS/400) has a field
with
> the data type of CLOB. We would like to perform an XSLT on this field
within
> the same report that contains more "normal" data (strings, numeric, etc.)
> types.
> This database contains error and status messages that are generated as
> various processes are run. We would like to be able to present the CLOB
data
> on the report based on the type of process that produced the data. In
other
> words, if process A produced the entery in to the database, on the report
for
> process A, format the CLOB data this way, if process B, then the CLOB data
is
> represented in another way. The data in the CLOB is XML and each process
has
> its own schema.
> Right now I am stumped. Is this possible? Can you point me in the right
> direction?
> Thanks.
The database we are reporting from (DB2 running on AS/400) has a field with
the data type of CLOB. We would like to perform an XSLT on this field within
the same report that contains more "normal" data (strings, numeric, etc.)
types.
This database contains error and status messages that are generated as
various processes are run. We would like to be able to present the CLOB data
on the report based on the type of process that produced the data. In other
words, if process A produced the entery in to the database, on the report for
process A, format the CLOB data this way, if process B, then the CLOB data is
represented in another way. The data in the CLOB is XML and each process has
its own schema.
Right now I am stumped. Is this possible? Can you point me in the right
direction?
Thanks.You're probably going to need to write a custom function (or perhaps a
custom assembly if you're going to need to read XSLTs from disk) to do this.
You could pass the data along with the process to your custom function which
would apply the transform and return the string to display in the textbox.
E.g. =Code.CLOBClass.Convert(Fields!CLOBData.Value,Fields!Process.Value)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:95B8ADFD-2A23-4DD5-A039-971AC84B5AF9@.microsoft.com...
> Hi:
> The database we are reporting from (DB2 running on AS/400) has a field
with
> the data type of CLOB. We would like to perform an XSLT on this field
within
> the same report that contains more "normal" data (strings, numeric, etc.)
> types.
> This database contains error and status messages that are generated as
> various processes are run. We would like to be able to present the CLOB
data
> on the report based on the type of process that produced the data. In
other
> words, if process A produced the entery in to the database, on the report
for
> process A, format the CLOB data this way, if process B, then the CLOB data
is
> represented in another way. The data in the CLOB is XML and each process
has
> its own schema.
> Right now I am stumped. Is this possible? Can you point me in the right
> direction?
> Thanks.
Subscribe to:
Posts (Atom)