|
Assuming you have 3 tables DEED, SHARE, OWNER (although if it is badly designed there may not be an owner table).
Select (put in the fields from the 3 table you want to display and a count(*))
from Deed
inner join Share on Deed.deedno = share.deedno
inner join Owner on Owner.OwnerNo = Share.OwnerNo
where ?
group by (put in the display fields)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There is no table for shares. I have only a table for deeds and this table consists of 4 columns (DEED_ID 'identity', DEED_NUMBER, START_SHARE, END_SHARE). For example if we have a deed consists of a 50 shares the values in these columns will be (1, 100, 1, 50) for DEED_ID, DEED_NUMBER, START_SHARE and END_SHARE respectively. What I need is to get all shares numbers, more clearer, if we apply on the above example of a deed of 50 shares starting for share number 1 to share number 50 I need to get the values 1, 2, 3, 4, 5 ..... 50. I need to do that for all deeds I have within my database. So if I have 150,000 deeds each of which consists of 50 shares. then I have 150,000 * 50 = 7500000 shares. I need to get values starting from 1 up to 7500000. I already make a solution by using a cursor for all deeds, getting each deed separately and then getting its start and end share numbers then making a while loop from the start share number to the end share number of that deed, then get the next deed and so on. However, this solution taking a considerable amount of time to load the the shares values and this time is unacceptable.
|
|
|
|
|
So it is a badly designed table, damn.
As David asked why do you need the individual records, what are you going to do with 7.5m records now that you have them!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The client needs to see all the shares he has within a DataGridView, then he can select from these DataGridView two share numbers one as a start share number and another as end share number to get all shareholders data who own shares within the range of the share numbers selected from the DataGridView. Lets say that client select 20 as from share number and 5000 as to share number, so the client now needs to see shareholders information who own shares within the range 20 to 5000. I hope you understand what I want
|
|
|
|
|
How are the tables defined? That might help us defining a solution.
there are some assumptions we could make like is the share id unique? are they sequential? etc...
However let's get a few things straight.
- Looping a resultset to check for results is not a very good idea and will be slow.
- Returning a million records let's say will be slow, no matter what. Even if your database could build the resultset in milliseconds it still needs to send that data through a wire somehow. (that's why they invented paging  ) IOW if you can improve the queries, you might still be in trouble and in need of an alternate solution.
|
|
|
|
|
There is no table for shares. There is only a table for deeds and this table structure or what is important to us in this table are 3 columns Deed_NUMBER, START_SHARE and END_SHARE respectively. DEED_NUMBER including the number of the deed, START_SHARE including the value of the start share number within a deed and END_SHARE including the value of the end share number within a deed. Let's say that we have a deed containing 50 shares and that deed number is 100. So the values in the 3 columns will be 100 for DEED_NUMBER, 1 for START_SHARE, and 50 for END_SHARE. I hope this explanation will help
|
|
|
|
|
so basically you have this?
DEED_NR START_SHARE END_SHARE
1 1 50
2 1 25
3 1 75
If you need get them out the result should be 1 - 150.
If that´s the case you could use the SUM function:
Select sum(END_SHARE) from DEEDS
if your START_SHARE = END_SHARE+1 (1-49, 50-74, 75-154 eg) of the previous record you just need to select that last record of the deed containing the shares.
Select END_SHARE from DEEDS HAVING DEED_NR = max(DEED_NR)
hope this gives you an idea.
|
|
|
|
|
Amr Muhammed wrote: more than 100,000 to 150,000 deeds the time my solution takes to retrieve shares numbers
So you are going to be returning at a minimum 100,000 rows. And more than likely you are going to be returning more like 10 million rows.
So exactly what is going to be using those 10 million rows or even 100,000? Certainly won't be a human.
|
|
|
|
|
That is what the client want and I'm at the client service 
|
|
|
|
|
I hear that line all the time, accepting it is just plain lazy, you are not doing your client a service unless you investigate the requirement and see if you can meet that requirement closer to their needs. They almost never understand their own needs from a data/IT perspective, that is your job to interpret their need and supply it. Delivering 7.5m records is almost certainly not doing that.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The client needs to see all the shares he has within a DataGridView, then he can select from these DataGridView two share numbers one as a start share number and another as end share number to get all shareholders data who own shares within the range of the share numbers selected from the DataGridView. Lets say that client select 20 as from share number and 5000 as to share number, so the client now needs to see shareholders information who own shares within the range 20 to 5000. I hope you understand what I want
|
|
|
|
|
In other words, you need to learn to interpret the requirements, just like Mycroft said. There is no need to give them numbers. Just a list of names. If they want numbers, you can simply supply a range, or a total, or both:
Person A: 40-90, 200-250 Total: 100 shares.
That most certainly doesn't require you to create an intermediary list of all of the numbers; you already have all the required information.
The only thing you haven't really addressed in your discussion is whether people can sell parts of their deed? If that is the case you will have a lot more work to get the logic right, and from where you are you will probably have to add a 'SPLIT_DEED' table, or something like that, with the appropriate information.
|
|
|
|
|
Amr Muhammed wrote: I hope you understand what I want
Please do the math...You present the user with 10 million rows. They spend 0.1 second looking at each row. How long will it take them to get to the last row?
This should demonstrate to you and even to the user that there must be some other solution.
Usually that solution is that user does in fact know what they are looking for in that 10 million rows. And if you asked for that and then used that information then you could give the user 1000 rows, or even 10 rows.
|
|
|
|
|
I have two tabs shown below:
<ul>
<li><a href="#tab-1">This is Tab 1</a></li>
<li><a href="#tab-2">Tab Two</a></li>
</ul>
tab one contains summary records, like userid, firstname, lastname, etc
Userid is hyperlinked like:
return " " + oObj.aData["RequestID"] + " ";
when a user clicks any of these links, we would like detailed records associated with to display on tab2.
Any ideas how to do this?
|
|
|
|
|
It will be hard to get answer here - as the question is totally unrelated to DB...
Move it to other forum, like web development...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Thanks for letting me know.
Silly of me to have posted such a question on db forum.
|
|
|
|
|
Hi,
I'm looking for a way to delete a row and it's related rows in other tables(aka linked by foreign keys), but without using cascade. I want to prohibit the user from doing a cascade delete, but want to support it so the admin can remove things that don't belong there such as test data etc.
I was looking for something in the direction of a script that searches the system databases for foreign keys so I can find out what is related, as I got too many tables to do it manually. Anyone who can send me in the right direction towards such a script? Using MS SQL Server 2012.
Many thanks in advance.
|
|
|
|
|
Is there any reason why you can't allow the admins cascade delete (never used it myself). I almost always find there are unrelated tables and orphaned data towards the end of a development especially if foreign keys are not implemented, I often leave this till the end of the development. I find the diagram extremely useful in the clean up phase before UAT.
I think there are a number of dependency discovery tools out there including Red-Gate.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Never heard of foreign keys that were dependent on user role. Afaik, foreign keys are independent of user roles. I always implement foreign keys when I create the database structure.
|
|
|
|
|
Nico Haegens wrote: Never heard of foreign keys that were dependent on user role
Wat - where did you get that from? I assume cascade delete is application initiated so controlling who can use it is trivial.
Nico Haegens wrote: I always implement foreign keys when I create the database structure
I wish - I usually have to start the development before the specs are even half done, the data structure ALWAYS changes as the users find out what they want and what they can have so FKs tend to be done when they have stopped running with the goal posts, this usually happens 2 weeks AFTER the app goes to UAT.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I thought delete cascade was only possible at foreign key constraint level. Afaik, Foreign key constraints don't care who is using it, be it a sql admin or an application. Could you explain a bit more, what you mean by saying cascade delete is application initiated? We might be talking about 2 different things.
|
|
|
|
|
A couple of points - I stated I never use cascade delete.
Yes I know that FKs are required for it to be implemented.
You said you wanted to allow admin to use it but prevent users from doing so (or find an alternative). I assume when you say users they are accessing the DB via an application with no direct access to the database(note ASS U ME) which would make it a simple design issue to manage the delete functions within the app.
If your users have direct access to the database then you may be able to manage it via permissions.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have set my foreign key constraints to disallow cascade effects so there are no accidents in what gets deleted.
Here is how my application works:
- users can't delete, they can only archive, meaning I set a boolean field to true and the item in the database won't appear in anything in the application to the normal user.
- admins can delete, but the problem is that my client doesn't have any IT minded people, so I want to prevent them to accidentily delete a whole bunch of data. Your signature suggests to me, that you are familiar with people who aren't IT minded.
So what I basically want to do is:
- show the admin user what the object is connected to(the list of data connected to the object) so the admin knows exactly what he is deleting
- then let the admin delete the data if he is sure the data can be deleted from the database. Do you know a good solution for my problem?
|
|
|
|
|
I would build a dedicated admin delete view where the admin can see a list of items flagged for delete, possibly a treeview if they are deep enough and allow the admin to confirm delete. Chasing down the dependency tree will be a PITA unless you know the structure intimately, the diagram should help.
It does seem a lot of work to get rid of junk when you already filter it out with a flag, I presume your flag already does the cascade thing (unless you cheated and only did the top levels). Why not change the flag to a datetime and delete anything flagged older than n months/years in a purge function.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi. So I know I've done this before but I can't get it to work despite every example on the internet telling me it should... I'm trying to call a procedure from code. I keep getting an "Invalid SQL statement" exception. Any ideas why?
create procedure DoSomething
as
begin
end DoSomething;
I try to execute from code like so:
Using conn = New OracleConnection(MyConnectionString)
Using cmd = conn.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "DoSomething"
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Using
End Using
At which point I get the aforementioned exception.
What am I doing wrong?
Sam.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
