|
I know that the TOP operator can't be within the where clause, but is there a way to achieve this behavior in a single select call? For example, if I want to select all books where publisher = 3 or 5; but I want to limit the results to only getting the top 5 for publisher 3 and the top 10 for publisher 5. Is this achievable on a single select call?
Something along the lines of:
select * from books
where (publisher = 3 and top 5) or (publisher = 5 and top 10)
Thanks in advance if anyone knows.
Ron Ward
|
|
|
|
|
You could use a union...
SELECT TOP 5 FROM books WHERE publisher = 3
UNION
SELECT TOP 5 FROM books WHERE publisher = 5
|
|
|
|
|
That's a really good idea. Thanks for the quick response.
|
|
|
|
|
hi all
just wonder if anyone have done any relatively complex project in oracle database. it seems hard to make an app for oracle database using ADO.NET dataset. in my app, I bind all the fields to all kind of controls and there are parent/child tables. so I create datarelations and all that for the datatable in a same dataset.
Q1. when inserting new parent/child records, oracle database doesn't support scop_indentity(),
how does one get the real id from the database and update the child records before they get inserted into the database?
Q2. when adding new child records for existing parent entry, does the datarelation automatically add the parent foreign key into the newly added child record? it seems that's not the case, since it doesn't work for me when I try to add new child record.
I posted a similar thread in the ms newsgroup, one of the ms guy showed me some of the sample how things are done in sql server 2000 and access, but not much with oracle. he also mentioned the use of sequence. get the some-sequence.currval to get the real id, but that's not connection oriented, because multiple users might insert record at the same time, how can one guarantee it is the correct id for the record.
anyone have any idea?
thx
kevin
|
|
|
|
|
Oracle doesn't have autonumeric fields. You can achive a similar behavior with sequences.
Sequences guarantee that the number generated is unique.
Add a trigger to your table that inserts the sequence value on the table.
Free your mind...
|
|
|
|
|
sorry, Gillermo
I am way pass that level, in fact select max(someColumn) + 1 will do. it seems like you misunderstand my question.
kevin;)
|
|
|
|
|
I hope you're doing some locking, otherwise you'll get bitten by concurrency bugs.
Example: Say the current maximum is n. Client A's connection performs SELECT MAX() + 1, then, before client A's connection performs an INSERT, client B's connection also does SELECT MAX() + 1. Both clients now think that their insert should be numbered n + 1. The second client to INSERT will either cause a unique constraint violation if you've defined that someColumn should be unique, or you'll get two values.
SELECT MAX() also causes more work for the database. Instead of looking up and updating a single value, it has to seek through the index every time.
(Speaking as someone who's used this technique in SQL Server.)
|
|
|
|
|
Hello,
I am trying to generate some statistics from my SQL Server 2000 database. I have two UDFs, one which determines which days have complete data for a range of dates, and the other generates the statistics for a particular day.
I am fustrated because SQL Server doesn't seem to want to allow me to use these functions as I would like.
I even dumped the contents of the dates function into a table variable so I do something like:
DECLARE @dates TABLE([date] smalldatetime)
INSERT INTO @dates
SELECT [date] FROM dbo.dates_with_complete_data(@start_date, @end_date)
The above works fine, but if I try an use that as input to the other function I get errors.
SELECT calc.*
FROM @dates as fd
INNER JOIN (dbo.calculation(fd.[date])) as calc
ON cc.[date] = fd.[date]
Can anyone shed some light on how I might tackle this? If I remain stuck I might have to do the unthinkable and create my first ever cursor! 
--Colin Mackay--
EuroCPian Spring 2004 Get Together[^]
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
|
|
|
|
|
I suspect the problem is in the inner join statement. Try this:
SELECT calc.*
FROM @dates as fd
INNER JOIN (
SELECT *
FROM (dbo.calculation(fd.[date]))) as calc
ON cc.[date] = fd.[date]
Barbara 
|
|
|
|
|
Thanks for your response, however that doesn't work either.
I think it may because of the circular nature of what I am trying to do. The calculation does not return any rows unless it has a date, the join doesn't know what dates match because the calculation has not returned any rows, so no dates can be plugged into the function.
The error message I get is "Incorrect syntax near 'fd'." giving the line number of where I call the UDF.
--Colin Mackay--
EuroCPian Spring 2004 Get Together[^]
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
|
|
|
|
|
oh - ok I see the problem. You can't use a variable in the FROM clause. You may need to put the function in place of @dates or use a derived set.
Barbara
|
|
|
|
|
The only advice I can give is to dump the dbo.calculation function, and compute the various parts in the SELECT clause instead, probably using separate functions.
I believe SQL Server is able to look inside a function when compiling a query plan, so it may be able to perform whatever operations you have specified in parallel anyway.
|
|
|
|
|
I am trying to access the Connection String in a Linked MS Access table.
This is easy inside of Access - but I can not seem to do it through ADO.NET.
I have been able to get as far as using ADOX and listing out the Tabledef properties,
The connection string is property index 5 - but I can not write anything back.
I need to be able to change this setting.
Help - anybody - Been wracking my brain on this one - so close - but I haven't found
any articles on this.
Kurt
|
|
|
|
|
Has anyone else experienced problems with Access accessed through ODBC?
I'm using JDBC and the JDBC-ODBC bridge from Sun. The process is this:
1. A row, let's call it <code>R</code>, is updated using SQL an not the updateXXX API.
2. <code>R</code> is fetched into a resultset and a <code>long</code> value, L, is retrieved.
3. <code>L</code> is incremented and <code>R</code> gets updated, again using simple SQL.
The problem is in step 3. The executeUpdate() reports that 1 row is updated, and if I had executed the SQL in Access directly that would have been the case.
But the row is actually not updated at all, the Statement object is set to AutoCommit and everythings seems fine, so this is really the most horrible of all errors: One that just silently let's the user, and the developer, think that all is OK while the data just gets corrupted.
One solution could be to use MSDE instead, but I'm sure that a lot of users would want to use Access.
I really would like to know if this is an ODBC problem or an error in the JDBC-ODBC bridge.
"After all it's just text at the end of the day. - Colin Davies
"For example, when a VB programmer comes to my house, they may say 'does your pool need cleaning, sir ?' " - Christian Graus
|
|
|
|
|
hello ,everyone!
I bind datatable to datagrid,and edit datarow value from the datagrid.
the value modified in datagrid and changed in datatable via debugging code.
My question is why the datarow's rowstate property value can't be changed to
modified automatically by this mothed ,however if i changed the datarow value from datatable directly ,it did? so that i can't update database
who can help me ? thinks
lu6
|
|
|
|
|
Hi, I got the following question.
I got 2 tables in Lets say A and B
Structures of the tables are:
"A" table "B" Table
OldName Number New_name Number Value Time
Time field in "B" table in following format "YYYY-MM-DD HH24:MI:SS"
So knowing the alias I can easily create a query:
select A.New_Name,A.Old_Name,B.Value,B.Time from A,B where A.Number = B.Number AND B.Time BETWEEN '2003-12-01' AND '2004-01-30' AND (A.New_Name like '%mw%' OR A.New_Name like '%mv%');
Everything worked so far. But, cause Value probe in "B" table happening every 3 minutes, and Name entries are about 10000 in the table,identified by '%mv" and '%mw', I simply cannot get some much data (imaging 60 days * 60min/3min * 24 * 10000 and you will get the magic number of rows. Tried to run it throught Database client or Vbscript ADO connections it is taking about 4 hours to complete. Cause I'm having 20 tables like that on 20 independent servers it is simply unacceptable for a time frame. So, we dicided instead of each minute to get an average for an each hour. I cannot get an average throught Vbscript (programmatically), cause it will not improve the database dump performance, I've to do it throught SQL. And here is the problem, I'm very familiar with MySQL logic and syntaksis, but this is my first time I'm querying Microsoft SQL server. Tried to construct the query - didn't work.
So result of my query should look like that
Time Name Value
01-30-04 00:00 abbmw 10
01-30-04 01:00 bbccmv 20
01-30-04 02:00 cccmvar 30
and so on.
Can somebody help me with this ?
Thank you very much.
Sincerely
vladfrenkel
|
|
|
|
|
I'm not sure whether this may help, but it doesn't hurt to try:
1. Index your tables, especially A.Number, B.Number, B.Time. I don't think indexing A.New_Name will help if you're using wildcard. Indexing your tables will help the performance when you're doing a search.
2. Do not write your SQL statements in the VBScript. Write them as Views (queries) in the SQL Database.
Views are precompiled (correct me if I'm wrong) and runs faster than a statement passed through VBScript.
You can then call the views from your VBScript (e.g. 'SELECT * FROM qryAB').
3. I'm guessing the reason the query is taking too long is the wildcard used. You may want to create a query just to join tables A and B (SELECT A.New_Name, A.Old_Name, B.Value, B.Time FROM A INNER JOIN B ON A.Number = B.Number WHERE B.Time BETWEEN '2003-12-01' AND '2004-01-30'). If this query doesn't take a long time then the wildcard is the cause.
After that, you can split the next View into two to get the final result, i.e.
"SELECT * FROM firstQuery WHERE New_Name LIKE '%mw%'"
"SELECT * FROM secondQuery WHERE New_Name LIKE '%mw'"
If you need the results as one then you don't need to split the Views. But if you're fine with it, there are ways to split the query so you can work on smaller portions of the data.
I hope it helps  .
|
|
|
|
|
I am trying to read an XML document into a .NET DataSet object. I used the XSD.EXE utility to generate a schema file for this XML document. This schema has re-ocurring elements that I would like to load as records into my DataSet. However, I only want to load certain child elements as fields into the DataSet record.
Is there a way I can modify this schema file such that I can use the DataSet.ReadXml method to parse this XML file and create the proper table rows??
Thanks
|
|
|
|
|
I don't know if this is good way or not, DataSet.ReadXML() accept stream of XML or XML Text, with XML classes such as XMLReader or XPath classes for XML first load your proper nodes into XMLReader or String and then pass this parameter into DataSet.ReadXML().
Mazy
No sig. available now.
|
|
|
|
|
Hi Gang,
Here's what I've got,
I've got information stored in one table/database that I'd like to move to another table/database.
Sounds pretty simple doesn't it?
Well, here's where the pickle comes into play.
The tables could potentially be dynamic. So I can't used a predefined DataSet to do the moves, so I have to recreate the datasets each time. That part isn't even necessarily the problem.
The problem I'm running into is:
What happens when my tables have memo fields in them?
Some of our tables have Ole embedded (Not my choice, but I have to live with it) in them that also need to be mapped.
I'm trying to do this all with ado.net using C#.
If I can't I'll probably just end up writing it in VB6. I know we can do it in there, we have. But then again, that's using DAO, not ADO.Net.
Anybody have any suggestions?
Thanks,
Nick
Env: Studio 2003, MS:Access 2000 Databases, WinXP Pro+Patched
This are my own opinions. You know the rest.....
|
|
|
|
|
Hi, I´m using the CDao classes from the MFC to manipulate and store data in an Access 97 db, using the Jet 3.5. engine. I was wondering if there are any good books that someone could suggest for reading. Any thing I´ve found sofar is all to to with VB. Even the various help files use VB examples. (I´m using Dev Studio 97 VC++ 5.0).
Normal opererations like deleteing,adding,modifying,
querydef´s and the like is quite simple, but creating relationshiups between tables is quite annoying, and the msdn does not help that much.
Thanks in advance
Phil
bum... and I thought I´d got rid of all the bugs 
|
|
|
|
|
Phil
Most of the samples you'll find will be VB. Many of the Office related technologies are promoted (at least, their automation models are!) through use of VB, VBA or VBS. In the case of JET this isn't exactly a surprise, given the use of VB as the scripting language.
However, I assume you've already got the DAO SDK (I believe it's on the VC++ CD). You could try getting hold of the JET Programmers Guide, which is (or was) an MS Press book.
Another thing to do is get hold of the VB->VC++ automation conversion tool that was on the MSDN site some time ago. It doesn't do 100% conversion of course, but usually enough to get you started on the right road.
Steve S
|
|
|
|
|
Thanks Steve, I had almost given up getting a reply
I´ll look into what you have suggested, and thanks once again for your time.
Phil
bum... and I thought I´d got rid of all the bugs
|
|
|
|
|
Hi Steve, me again....
I´ve managed to find the book you mentioned, I´ll have to see if I can order it.
As for the automation conversion tool you mentioned, I have not been able to find it, do you still have a link to it?
Thanks in advance
Phil
bum... and I thought I´d got rid of all the bugs
|
|
|
|
|
Hi,
Is there any article available here which gives me an idea of connecting a c++ application to the database. I want to write a console database application. I don't want to use the MFC classes.
Kindly help.
regards,
vini
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
