|
Don't tell me it is a text report and you are hoping to query the text file using TSQL - not going to work.
First you need to parse the data into logical fields using the correct data types. Then you may query the TABLE and COLUMNs to get the results you need.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
my query like this
cte3_persen (per) as
(select ((cte1.totalcount/cte2.TotaCount)* 100 )
from cte1,cte2)
i am creating one new cte table shown above
from that value '2'coming from cte1.totalcount and value '500' coming from cte2.TotaCount
i want percentage of those value it should be in 0.00% format
i want ans of per from cte3_persen table is =0.40%
please help some one
|
|
|
|
|
What are your data types for the TotalCount variables? are they of Decimal type?
if so then have a look at this
DECLARE @value1 DECIMAL(18,2)
DECLARE @value2 DECIMAL(18,2)
SET @value1 = 2
SET @value2 = 500
SELECT (@value1 / @value2)*100
SELECT CAST((@value1 / @value2)*100 AS DECIMAL(18,2))
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
|
is that the same for TotalCount from the second CTE?
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Sounds like you want to round your result to 2 decimal places before dividing by 100
SELECT
(2/500)* 100 AS not_rounded,
(round(2/500,2))* 100 AS rounded
from dual;
returns
NOT_ROUNDED ROUNDED
----------- -------
0.4 0
Regrads
|
|
|
|
|
Which is the best database to manipulate 100 million records which configures with c# win. form.
|
|
|
|
|
All the known databases - SQL or NoSQL - can easily handle a 100 million records. All of them has also binding to .NET (so to C#)...
You may map your request on that data, store only, OLAP, more read than write...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
actually i am already working on sql but i am not sure that it can manage 100 million record. somebody suggested me oracle is oracle better than SQL.
|
|
|
|
|
I've worked with SQL had table with over 800 million rows - so no problem. The main factor is the usage. You have to find a good PK and as less as possible other indexes and FKs...
However if you aim to even higher you may look for some big-data database...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
I have worked with 300 million rows in an Oracle database and yes it handles them well however... so will a SQL Server database.
What matters is how the indexes and partitions are set up on tables, as access to tables with this number of rows will be considerably affected by these decisions.
With 100+ million rows you are most likely going to need to partition tables.
[edit]grammar correction[/edit]
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 11-Feb-14 13:24pm.
|
|
|
|
|
GuyThiebaut wrote: partition tables
That could be a good idea, especially if you have much to read - however it can work without...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Oracle because it has more security than SQL.
|
|
|
|
|
I'm not familiar with Oracle. Would you care to expand on that statement? What security features does Oracle provide that are missing from MS SQL?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
What's funny but totally expected is that both Oracle and Microsoft claim that their database has the better security.
I'm of the opinion that the safer product is the one with the better DBA.
And since SQLServer is easier to run out of the box...
|
|
|
|
|
Really? Can you elaborate on that?
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Agent_Spock wrote: i am not sure that it can manage 100 million record
According to the Microsoft document "Maximum Capacity Specifications for SQL Server":
Rows per table: Limited by available storage
Database size: 524,272 terabytes
So unless each of your rows is going to exceed 5.36 gigabytes, you should be fine. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Agent_Spock wrote: sql but i am not sure that it can manage 100 million record.
I've worked with a SQL database that had a single table with a few billion rows (Inventory type stuff). So, yea SQL can scale as far as you need it too.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
and sir, is the searching good (fast).
|
|
|
|
|
Yup, never a problem. The table was indexed for the usage.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Does "Indexed" mean using primary key as filter or something else?
|
|
|
|
|
We work with both Oracle and SQL Server in those sort of volumes and I will reluctantly state that Oracle handles the volume better than SQL Server. However the reason may well be that for Oracle you generally need a dedicated DBA to drive the bastard. Also look into the comparative costs of purchase and support of the 2 systems, Oracle is substantially more expensive than SQL Server.
We have a rule of thumb that Oracle is used for enterprise apps and SQL Server for departmental apps.
[pet hate] Oracle also forces you to use UPPER case naming [/pet hate]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: However the reason may well be that for Oracle you generally need a dedicated DBA to drive the bastard
I believe you're onto something there.
Mycroft Holmes wrote: Oracle is substantially more expensive than SQL Server
I wonder how much of that's a myth? For us it was a little bit cheaper actually.
Mycroft Holmes wrote: [pet hate] Oracle also forces you to use UPPER case naming [/pet hate]
Grumble grumble grumble.
|
|
|
|
|
Jörgen Andersson wrote: I wonder how much of that's a myth? For us it was a little bit cheaper actually.
But don't you have to pay something like 22% of the original cost every year in "maintenance charges"? Whereas, once you've bought SQL Server, you only have to pay if you want to upgrade.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
You don't need to pay the maintenance fee. You can keep on using it as long as you want to. What you're missing out on is support and "windows update" aswell as upgrades.
And that support is of a wildly varying quality. If you call in the morning, someone with a very strong Indian accent called "Kevin" answers, and if he understands my problem, I won't understand his answer.
Anyway, if you have SQLServer you still have to pay for support and upgrades, don't you?
It's not an apple to apple comparison.
What you can see is that Oracle is trying to price themselves into smaller systems while MS is trying to gain grounds among larger systems. Because when you have a large server farm Oracle is considerably more expensive.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
