What is an example of a fast SQL to get duplicates in datasets with hundreds of thousands of records. I typically use something like: select afield1, afield2 from afile a where 1 < (select count(afield1) from afile b where a.afield1 = b.afield1); But this is quite slow. ...
I've got an ASP.NET 2.0 website that connects to a SQL database. I've upgraded the SQL server from 2000 to 2008 and since then, one page refuses to work. I've worked out the problem is that the call to SqlDataReader.HasRows is returning false even though the dataset is not empty and removing the check allows the loop through reader.Rea...
Given a table (mytable) containing a numeric field (mynum), how would one go about writing an SQL query which summarizes the table's data based on ranges of values in that field rather than each distinct value? For the sake of a more concrete example, let's make it intervals of 3 and just "summarize" with a count(*), such that the resul...
I have a products table that contains a FK for a category, the Categories table is created in a way that each category can have a parent category, example: Computers Processors Intel Pentium Core 2 Duo AMD Athlon I need to make a select query that if the selected category is Proc...
Does anyone know here I can find a library of common but difficult (out of the ordinary) SQL script examples. I am talking about those examples you cannot find in the documentation but do need very often to accomplish tasks such as finding duplicates etc. It would be a big time saver to have something like that handy. EDIT: Thanks ever...
The in-house application framework we use at my company makes it necessary to put every SQL query into transactions, even though if I know that none of the commands will make changes in the database. At the end of the session, before closing the connection, I commit the transaction to close it properly. I wonder if there were any particu...
I have a postgres table. I need to delete some data from it. I was going to create a temporary table, copy the data in, recreate the indexes and the delete the rows I need. I can't delete data from the original table, because this original table is the source of data. In one case I need to get some results that depends on deleting X, in ...
I'm curious to know how people are using table aliases. The other developers where I work always use table aliases, and always use the alias of a, b, c, etc. Here's an example SELECT a.TripNum, b.SegmentNum, b.StopNum, b.ArrivalTime FROM Trip a, Segment b WHERE a.TripNum = b.TripNum I disagree with them, and think table aliases sho...
I am interacting with an Oracle DB using SQL over an ODBC connection. Is there an SQL command I can use to get the MAC address of the server, or something that uniquely identifies the server hardware or software installation. This is so I can be sure (or at least fairly sure) that I'm talking to the same database all the time. ...
I'm designing some VB based ASP.NET 2.0, and I am trying to make more use of the various ASP tags that visual studio provides, rather than hand writing everything in the code-behind. I want to pass in an outside variable from the Session to identify who the user is for the query. <asp:sqldatasource id="DataStores" runat="server" connec...
Newish to Oracle programming (from Sybase and MS SQL Server). What is the "Oracle way" to avoid filling the trans log with large updates? In my specific case, I'm doing an update of potentially a very large number of rows. Here's my approach: UPDATE my_table SET a_col = null WHERE my_table_id IN (SELECT my_table_id FROM my_table WHE...
I'm trying to do a domain lookup in vba with something like this: DLookup("island", "villages", "village = '" & txtVillage & "'") This works fine until txtVillage is something like Dillon's Bay, when the apostrophe is taken to be a single quote, and I get a run-time error. I've written a trivial function that escapes single quotes - ...
I have a Stored procedure which schedules a job. This Job takes a lot of time to get completed (approx 30 to 40 min). I need to get to know the status of this Job. Below details would help me 1) How to see the list of all jobs that have got scheduled for a future time and are yet to start 2) How to see the the list of jobs running and ...
Is it possible in a sqlite database to craete a table that has a timestamp column that default to DATETIME('now') ? Like this: CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, t TIMESTAMP DEFAULT DATETIME('now')); This gives an error... How to resolve? ...
i have a bunch of sql scripts that should upgrade the database when the java web application starts up. i tried using the ibatis scriptrunner, but it fails gloriously when defining triggers, where the ";" character does not mark an end of statement. now i have written my own version of a script runner, which basically does the job, but...
Hi, What is the best way to count the time between two datetime values fetched from MySQL when I need to count only the time between hours 08:00:00-16:00:00. For example if I have values 2008-10-13 18:00:00 and 2008-10-14 10:00:00 the time difference should be 02:00:00. Can I do it with SQL or what is the best way to do it? I'm buildi...
If I have a Linq to SQL expression like this: from subscription in dbContext.Subscriptions where subscription.Expires > DateTime.Now select subscription I want this to to use the SQL Servers GETDATE() function instead of the time of the machine running the C# program. The next question would be how to translate DateTime.Now.AddDays(...
I'm trying to create an access control system. Here's a stripped down example of what the table I'm trying to control access to looks like: things table: id group_id name 1 1 thing 1 2 1 thing 2 3 1 thing 3 4 1 thing 4 5 2 thing 5 And the access control table looks like...
Has anyone had luck with removing large amount of issues from a jira database instead of using the frontend. Deleting 60.000 issues with the bulktools isnt really feasible. Last time i tried it, the jira went nuts because of its own way of doing indexes ...
I am using Installshield 2008 Developer Edition and need to run SQL scripts against an existing SQL Server 2005 Express. When I do this without InstallShield, I would have to have some ODBC drivers installed for example. How does InstallShield do this? Does it include some sort of driver? Or does it depend on preexisting drivers? ...