|
Thanks for the suggestions. This morning, I finally got the view to do what I wanted. With the knowledge that the portfolio_code column of the portfolio table is a unique identifier for each row, I got the following SQL to run and do exactly what I was looking for.
select pm.portfolio_code master_code
,pp.portfolio_code part_code
,( select count(c.portfolio_code) from portfolio c
where c.portfolio_code <= pp.portfolio_code
and c.master_account = pm.portfolio_code
) inc_count
from portfolio pm
,portfolio pp
where pm.master_account_indicator = 1
and pp.master_account = pm.portfolio_code
order by pm.portfolio_code, pp.portfolio_code
Essentially, as each row is returned, another query runs and returns the count of portfolio_code that is less than or equal to the value in the row being returned.
Chris Meech
|
|
|
|
|
I always used the SELECT Count(*) AS nCount syntax to select the number of records which have the specified requirements. But yesterday, while giving a look to some example code, I found the SELECT Count(1) AS nCount syntax. Is this different from the first one? If yes, which one is the faster?
Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)
|
|
|
|
|
select count(1) is quicker, there is less data to shift about. compare the query plan for each one in query analyser and you'll see the point.
only really makes a difference on large-ish tables (100,000+ records or so)
Signature space for rent. Apply by email to....
|
|
|
|
|
|
my mistake, i meant server trace.
there's not that much difference between the two forms though.
And, in sql server, the quickest way to get a rowcount of a whole table is to use sp_spaceused
Signature space for rent. Apply by email to....
|
|
|
|
|
What's this Query Analyser? Is it included in SQL Server? Because I'm using SQL on an Access DB via ADO... but if it's a standalone tool it could be very useful!!!
Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)
|
|
|
|
|
Oh, only in SQL Server, and it uses some nice SQL Server features, like showing the query plan, and so on...
If you go serious about database development, you should really consider using SQL Server. There's even a free version of SQL Server, named MSDE or SQL Desktop Engine. Try it, is nice...
My latest articles:
Desktop Bob - Instant CP notifications
XOR tricks for RAID data protection
|
|
|
|
|
I have tried to store more than 255 chars in an Access 2000 database, but it isn't allowed!
How can I stor more than 255 chars in Access?
I beleive it's not possible!?
Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!
|
|
|
|
|
Hi Rickard.
Rickard Andersson wrote:
I beleive it's not possible!?
No.Use Memo type.
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Mazdak wrote:
Use Memo type
Okay, but how do I do that?
I opened my table in Access 200 in Design mode and checked if Memo type could be used for my field, but no....
Can you explain more?
Thank you very much!
Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!
|
|
|
|
|
Rickard Andersson wrote:
I opened my table in Access 200 in Design mode and checked if Memo type could be used for my field, but no....
The Memo field is listed under the data type section of your database.
Nick Parker
The greatest lesson in life is to know that even fools are right sometimes. - Winston Churchill
|
|
|
|
|
Nick Parker wrote:
The Memo field is listed under the data type section of your database.
Found it!
In the Swedish edition Memo = PM (which is the same anyway )
Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!
|
|
|
|
|
I have a treeview that is populated from a self joined table in a way analogus to that detailed by Gevik in his article:
http://www.codeproject.com/useritems/2dtreeview.asp#xx281692xx[^]
Now I've been trying to work out the most efficent way to delete a from the treeview and have the corresponding node in the database, plus any children, be deleted. I tried this:
private void CascadeDelete(int iParent)
{
DataView dvView = new DataView(dsData.Tables[0]);
dvView.RowFilter = "ParentID = '" + iParent + "'";
while (dvView.Count>0)
{
DataRowView thisRow = dvView[0];
int thisID = Int32.Parse(thisRow["TypeID"].ToString());
CascadeDelete(thisID);
MessageBox.Show("DELETING: " + thisRow["strType"].ToString());
thisRow.Delete();
}
}
The problem, I think, is that as the recursion unwinds the DataRowView of the caller is out of date because the callee has changed it, but trying to update it again (by uncommenting the above lines) doesn't seem to work. The end result is that when I try to call dsData.GetChanges() it returns null.
Here's a example of the messed up recursion, give the nodes:
1
|-3
|-2
|-6
|-5
| |-7
4
I get this series of "deletion": 3,6,7,7,5,4,6,5,4,2,3,2
Any ideas on an efficient (or even just working!) way to do this? I could just issue a bunch of individual SQL statements to delete each row, but that seems horribly inefficient.
Thanks
|
|
|
|
|
|
Sorry, can you explain what you mean?
|
|
|
|
|
I would have to insert around 500 records every 10 seconds into a table in Access 2000. I am using ADO. What is the most efficient way to do this with good performance.
Is it possible to pass all of the 500 records to a query using some kind of SafeArray parameters and Insert them all at once?
Any tips how this can be accomplished ?
Thanks
|
|
|
|
|
I think stored procedure is the best way.
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Could you please help me in with a small sample code.
Do I have to send the records as an array and loop the INSERT statement for all records? I am not quite sure if all this is possible in ACCESS.
I guess my question would be can I pass an array to Stored proceedure, if Yes how?
Thanks for your time.
|
|
|
|
|
I am trying to reset the PK on a SQL Server table. The key is auto populated and automatically increments by 1. I have cleared all the data and want to repopulate. Is there a way to have the first Id be equal to 1? Currently numbering starts where the last data ended.
Second question is there a way to print the row number in a select statement?
Jason W.
|
|
|
|
|
If I remember correctly, to reset the row number field you need to TRUNCATE TABLE.
Michael
"I've died for a living in the movies and tv.
But the hardest thing I'll ever do is watch my leading ladies,
Kiss some other guy while I'm bandaging my knee."
-- The Unknown Stuntman
|
|
|
|
|
Exactly what I was looking for thanks.
Jason W.
|
|
|
|
|
you can also do this by changing your identity to "NO" in the design view, saving the table and then re-instating the identity to YES
|
|
|
|
|
The only mode that I know, is removing the PK state, repopulating the fileld and setting like PK again...
Carlos Antollini.
Sonork ID 100.10529 cantollini
|
|
|
|
|
To reseed your identity column you can use the following dbcc command. Not too sure if it's supported though:
dbcc checkident ( <tablename>, RESEED, <newvalue> )<br />
example:
<br />
dbcc checkident(authors, RESEED, 100000)<br />
|
|
|
|
|
Oh, and for ther row number you might want to check out this link:
http://www.sqlteam.com/item.asp?ItemID=1491
|
|
|
|