|
I have a table consisting of 3 columns: Parent varchar(50), Child varchar(50), Pop int.
The table is setup as follows:
Parent Child Pop
----------------------------------
Europe France 0
France Paris 1
New York New York City 10
North America United States 0
North America Canada 0
United States New York 0
United States Washington 0
Washington Redmond 200
Washington Seattle 100
World Europe 0
World North America 0
This is just some sample data modified a tiny bit from an example of a hierachical print out sample that is a stored procedure that allows me to pass any place and see all of that place's children/grandchildren.
I need to figure out how to write a query to show me cumulative sums (ROLLUP?) of the whole tree. So the output should basically be something like this (it can include parent and child columns too):
World Null 311
World Europe 1
Europe France 1
France Paris 1
World North America 310
North America United States 310
North America Canada 0
United States New York 10
United States Washington 300
New York New York City 10
Washington Redmond 200
Washington Seattle 100
Hopefully you understand what i'm looking for. I've tried using WITH ROLLUP and I also tried using an Inner Join but I'm not really sure what I need to do to pull this off. I seem to only be able to get it to work 1-2 levels deep but not through the whole tree.
Any help/ideas would be appreciated! Thank you.
|
|
|
|
|
Make a function:
CREATE FUNCTION dbo.GetPopTotal(@cParent varchar(50))
RETURNS int AS
BEGIN
declare @nTotal int
declare @nGrandTotal int
declare @cChild varchar(50)
set @nTotal = 0
DECLARE childcursor CURSOR FOR
SELECT DISTINCT child,pop
FROM travel
WHERE parent = @cParent
OPEN childcursor
FETCH NEXT FROM childcursor INTO @cChild, @nGrandTotal
WHILE @@FETCH_STATUS = 0
BEGIN
select @nGrandTotal = @nGrandTotal + @nTotal + dbo.GetPopTotal(@cChild)
FETCH NEXT FROM childcursor INTO @cChild, @nTotal
END
CLOSE childcursor
DEALLOCATE childcursor
return isnull(@nGrandTotal,0)
END
Call it like this:
select parent,child, dbo.GetPopTotal(child) + pop as PopTotal from travel
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hello there,
ich have an interrogation from a database on my screen. Now I will put it into a Report. It works but now I will make these data connection within the runtime. So I make a blank Report and my code is like this:
<br />
oleDbConnection1.Open();<br />
string strConn = "Provider=SQLOLEDB;Data + <br />
+ Source=AD061978NB\\WINCC;InitialCatalog=Northwind;Trusted_Connection=Yes;";<br />
<br />
string strSQL = "SELECT CustomerID, CompanyName, City FROM Customers";<br />
<br />
OleDbDataAdapter daCr = new OleDbDataAdapter(strSQL,strConn);<br />
DataSet dsCr = new DataSet();<br />
daCr.Fill(dsCr);<br />
oleDbConnection1.Close();<br />
<br />
CrystalReport1 myDataReport = new CrystalReport1();<br />
myDataReport.SetDataSource(dsCr);<br />
crystalReportViewer1.ReportSource = myDataReport;
So but the Report is empty. Sure, because there is no connection to a database or dataset how it exits if I use the Wizzard.
I cant add a field which declare the position of an column. I hope you know what I mean.
Does anybody can help me?
Thank you.
|
|
|
|
|
Now I have another code, a litte bit changed. But it doesn´t work too:
<br />
oleDbConnection1.Open();<br />
string strConn = "Provider=SQLOLEDB;Data Source=AD061978NB\\WINCC;Initial Catalog=Northwind;Trusted_Connection=Yes;";<br />
string strSQL = "SELECT CustomerID, CompanyName, City FROM Customers";<br />
OleDbDataAdapter daCr = new OleDbDataAdapter(strSQL, strConn);<br />
DataSet dsCr = new DataSet();<br />
daCr.Fill(dsCr, "Data");<br />
oleDbConnection1.Close();<br />
<br />
<br />
ReportDocument doc = new ReportDocument();<br />
doc.Load(@"C:\Documents and Settings\ADBLDA1\My Documents\Visual Projekte\Print DataGrid\CrystalReport1.rpt");<br />
doc.SetDataSource(dsCr);<br />
crystalReportViewer1.ReportSource = doc;<br />
<br />
I was searchin in the hole net, but any code looks like mine.
I am wondering, that nobody has an idea 
|
|
|
|
|
OK, another Question. Is there any opportunity to show a filled report without using these stupid Wizzard? I mean I want to make it by myself lik in the upper code. But it doesnt work. If I use the Wizzard to connect to the dataSet an the tables and then I fill it with the code, it work. But I don´t understand this. Because, I filled the Dataset with the wizzard already. Why I must use my code?
The important question: Is thera any way to use the reports without the wizzard? (a blank report is OK)
|
|
|
|
|
hi
can anyone please help me
I have
declare @Z decimal
set @Z=0.1
select @Z
the out put comes to be 0 WHY ? it shud be 0.1
Thanx
|
|
|
|
|
You haven't specified the precision or scale parameters of the decimal type. Precision specifies the total number of decimal digits that can be stored, while scale specifies the number of digits to the right of the decimal point. The maximum number of digits to the left of the decimal point is then (precision - scale).
If you don't specify these parameters, the default scale is 0 - so anything to the right of the decimal point is discarded and not stored.
To store a number between -9.9 and 9.9, with one decimal place, you would say
declare @Z decimal(2,1) -- modified at 6:08 Wednesday 23rd August, 2006 (forgot closing bold tag)
|
|
|
|
|
I am developing a system using ASP.NET with VB script.
I have a function to import CSV file into my SQL server database. As i found out the comma will effect my data placement in my tables, so i think of to develop a function to convert the CSV file to Tab Delimiter file.
Is it possible to develop the function?
As i found out, there are many converter outside that need to buy for the converter, it is not convenient for me to buy it.
Please give me any suggestion or solution, thank you.
|
|
|
|
|
Well, your problem here is that your import data is ambiguous. You don't know whether a comma represents a field separator or a digit grouping symbol. The best thing to do is to change the import data format, for example to put quotes around numeric fields that can have a digit grouping symbol in them, or indeed to put quote marks around all fields. It's then traditional to double quote marks that actually appear within a field.
Bulk insert is typically easiest to handle with fixed field widths rather than the use of separators.
There's what looks to be a good CSV file reader class at http://www.codeproject.com/cs/database/CsvReader.asp[^] (disclaimer: I haven't used it). You could use this to write a convertor to tab-separated format once you've sorted out the ambiguity, if you can't get the source data generated in a usable format.
|
|
|
|
|
hi i have one table which contains employeename, employeeID and ManangerID
but manager is also an employee.
now i want to retrive employeeID and employeename based on managerID
|
|
|
|
|
Is this what you're after?
select e.employeeID,e.employeename,m.employeeID as ManagerID, m.employeename as ManagerName
from Employees e left join Employees m on e.ManagerID = m.employeeID
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
how to configure a SQL Server 2000 that syncronize their data with the SQL Server 2000.i.e how to use Replication through Microsoft SQL Server Enterprise Manager.
I am a fresher so,please help me. 
Thankyou
Vartika
-- modified at 8:10 Thursday 24th August, 2006
Vartika
-- modified at 8:14 Thursday 24th August, 2006
|
|
|
|
|
While i use the bulk insert to import the csv file into my SQL server database, i am facing problem as the csv file have column with money data such as 1,111,111.00.
As i set the FIELDTERMINATOR =',', it will separate out the value 1,111,111.00 to few of columns as the value have comma in it.
Any suggestion to solve this problem?
Thank You.
|
|
|
|
|
I have installed and configured MS SQL 2005 Express. Both SQL Server and SQL Server Browser login set to "Local System", Shared Memory, Named Pipes and all the stuff set correctly.
If I want to connect to database created in the SQL Server Management Studio, everything is OK, no User ID or Password needed, because of the trusted connection.
Then I've added a database in my project and set Windows Authentication in the "Modify Connection...". After running the program, I've got exception number 4060:
Cannot open database "AddressBook" requested by the login. The login failed. Login failed for user '<my system="" user="" name="">'.
This is the connection string I've used:
SqlConnection myConnection = new SqlConnection(
"server=.\\SQLEXPRESS;" +
"Trusted_Connection=yes;" +
"database=AddressBook;");
Connection test in VS is also OK, only accessing the database (.mdf) programatically using SqlConnection goes wrong.
Maybe it's something easy to fix, but I tried books, Google and MSDN without any progress, so I feel desperate from this
|
|
|
|
|
It sounds like you're trying to work with a detached file, one that isn't a permanently configured database. If that's what you're trying to do, you need to specify the filename in the connection string, using the AttachDBFilename keyword.
For more information, see the documentation for SqlConnection.ConnectionString.
|
|
|
|
|
I'm just looking for a bit of education here. I was reviewing an old piece of code and found the something like the following:
SELECT NAME NAME
FROM TABLE
Where NAME would be a field name.
This doesn't throw an error and returns a single column of data (just NAME).
I'm just wondering why? And why doesn't it fall over? I had a quick search under books online, but (as you can probably see from the thread title), i'm not sure how to phrase that search..
-- modified at 9:16 Tuesday 22nd August, 2006
Just figured it out - second NAME becomes an alias for the first NAME, d'oh!
|
|
|
|
|
Paddy Boyd wrote: I'm just wondering why?
You can create aliases for column names. I tend to write them explicitly so that anyone maintaining the code knows what I was doing. e.g.
SELECT RealColumnName AS AliasName
FROM TableName
The "AS" is optional and can be left out. As you can probably tell, it makes the code much easier to read if the "AS" is left is because it reduces the ambiguity, especially if someone was not aware of aliases, or that they can be declared without the "AS".
Why the guy put "NAME NAME"? No idea, but it just adds to the confusion, doesn't it? Perhaps he wanted to waste the time of those that came after him. Perhaps he was just an idiot. That is something I guess we'll never know. 
|
|
|
|
|
Colin Angus Mackay wrote: I tend to write them explicitly so that anyone maintaining the code knows what I was doing.
Mmm. More used to that. All for unambiguity...
Colin Angus Mackay wrote: Why the guy put "NAME NAME"? No idea, but it just adds to the confusion, doesn't it?
I like to give the benefit of the doubt that it was a simple copy paste error.
|
|
|
|
|
Paddy Boyd wrote: second NAME becomes an alias for the first NAME, d'oh!
 Just noticed you message modification. Oh, well.... I hope it helps others too.
|
|
|
|
|
Hi all,
I got a table with trigger. this trigger will raise an error when certain validation is not met. but i'm not able to catch this error in stored produre when i try to insert an record to this table. Need your help. any idea on this?
Thanks in advance! 
|
|
|
|
|
@@ERROR[^]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Folks,
I have a DTS package which loads a text file into a table on SQL server 2000 database. The source column is numbers but occaisionally has "N.A."
The target column is FLOAT and nullable. How do I get DTS to translate "N.A." to NULL?
Code that does the transfer is:-
Public Sub CustomTask_Translate_BidPrice(ByVal oCustomTask1 As Object, ByVal ColumnPosition As Integer)
Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask1.Transformations.New("DTSPump.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__Bidprice"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
Set oColumn = oTransformation.SourceColumns.New("PX_BID", 1)
oColumn.Name = "PX_BID"
oColumn.Ordinal = ColumnPosition
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("BidPrice", 1)
oColumn.Name = "BidPrice"
oColumn.Ordinal = 4
oColumn.Flags = 8
oColumn.Size = 50
oColumn.DataType = DBDataType.DBTYPE_NUMERIC
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
'oTransformation.
Set oTransProps = oTransformation.TransformServerProperties
Set oTransProps = Nothing
oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub
|
|
|
|
|
Hi friends.. This is selvaraj.K.My requirement is set of pending records will be sent automatically concerned person from SQL Server 2000.The set of records get from one stroed procedure.I hope you catch my point.If anyone know regarding this please suggest or advice or send example as soon as possible....
Thanks in advance
Selvaraj.K
|
|
|
|
|
I have a single client application that physically connects to a number of instruments in a lab. These instruments transmit large amounts of data (via RS232 & TCP/IP) which are all stored on our SQL server.
My first question is, performance wise, will it be better to create a seperate "Instrument##MessagesIn" table for every instrument or to have a common table for all instruments and only distinguish between them using an InstrumentID column?
My second question is, what are the advantages of using a seperate DB for certain data on a single server, the amount of history data recorded by this system is literally millions of records in tblHistory. Will a second database (on the same server) dedicated to history tables show any performance gain, or maybe relieve overhead, memory or CPU on my main database?
Regards
you can't forget something you never knew...
|
|
|
|
|
It's always a bad idea to dynamically create tables. It invariably means creating dynamic SQL to access and modify data in the database, which will cause many more query plans to be cached. Also, it's difficult to know when to drop a given table.
Keeping your history data in a separate database is not really necessary, but you should consider keeping it on a separate disk (or disk array) from the transactional data, so that overhead of long running queries on the history does not impact the I/O performance of the immediate data. Also, you can get locking problems if you don't separate historical and live data: if your history queries tend to read a lot of rows, that can cause updates or inserts to block until the history query's transaction is committed or rolled back. This can also impact any OLTP work which uses table scans (this should be avoided if at all possible) since the cost of the table scan to find a given row is on average the cost of reading half the number of rows in the table.
To do this in a single database requires understanding additional data files and filegroups so that you can place a specific table in a specific file or group of files which will be on a particular disk. It's often simpler to keep it in a completely separate database. This will also generally be easier to migrate to a separate server later, if required.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
