Oracle join operator
How to rewrite this: select tab1.id, tab2.id, tab3.id from tab1, tab2, tab3 where tab1.col1 = tab2.col1(+) and tab2.col2 = tab3.col2(+); using OUTER JOIN syntax? ...
sql
Best way to check for current date in where clause of sql query.
I'm trying to find out the most efficient (best performance) way to check date field for current date. Currently we are using: SELECT COUNT(Job) AS Jobs FROM dbo.Job WHERE (Received BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1)) ...
Improve asp script performance that takes 3+ minutes to run
I use an SQL statement to remove records that exist on another database but this takes a very long time. Is there any other alternative to the code below that can be faster? Database is Access. email_DB.mdb is from where I want to remove the email addresses that exist on the other database (table Newsletter_Subscribers) customers.mdb i...
Table of SQL Errors & Stored Procedures..
I have this bit of code I found on the web at the end of each of my stored procedures: ROLLBACK TRANSACTION PRINT '-----START: ERROR DETAILS-----' PRINT ERROR_NUMBER() PRINT ERROR_SEVERITY() PRINT ERROR_STATE() PRINT ERROR_PROCEDURE() PRINT ERROR_LINE() PRINT ERROR_MESSAGE() PRINT '-----E...
Sorting on the server or on the client?
Hello, I had a discussion with a colleague at work, it was about SQL queries and sorting. He has the opinion that you should let the server do any sorting before returning the rows to the client. I on the other hand thinks that the server is probably busy enough as it is, and it must be better for performance to let the client handle the...
using a vector of column names, to generate a sql statement.
A problem that we need to solve regularly at my workplace is how to build sql statements based on user supplied table/column names. The issue I am trying to address is the commas between column names. One technique looks something like this. selectSql = "SELECT "; for (z = 0; z < columns.size(); z++) { selectSql += columns[z]....
How far can you really go with "eventual" consistency and no transactions (aka SimpleDB)?
I really want to use SimpleDB, but I worry that without real locking and transactions the entire system is fatally flawed. I understand that for high-read/low-write apps it makes sense, since eventually the system becomes consistent, but what about that time in between? Seems like the right query in an inconsistent db would perpetuate ha...
MySQL Prepared statements with a variable size variable list
How would you write a prepared MySQL statement in PHP that takes a differing number of arguments each time. An example such query is: SELECT age, name FROM people WHERE id IN (12, 45, 65, 33) The IN CLAUSE will have a different number of id's each time it is run. I have two possible solutions in my mind but want to see if there is a b...
join two tables into one big table
I have two tables with the same columns, and I need to copy one table's rows to the other table's rows to create one big table with all the values from both tables. Right now I am doing this query to return the same thing: SELECT col1, col2, col3 from Table1 union SELECT col1, col2, col3 from Table2 However, it seems horribly ineffic...
Can we have the table name as "option" in MySQL?
I am very, very new to MYSQL.I tried to create a table named "option". My SQL Query is : create table option( id int not null primary key auto_increment, choice varchar(30) ) While executing this query it shows the following error Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MyS...
C# - closing Sql objects best practice
If you have a C# function with Sqlaccess, is it mandatory to close all objects/handles, or is everything cleaned up automatically once you exit the function For example: void DoSqlStuff() { SqlConnection sqlConn = new SqlConnection(...); SqlCommand cmd = new SqlCommand(...); SqlDataReader sqlData= null; sqlConn,Open();...
Which SQL statement is faster? (HAVING vs. WHERE...)
SELECT NR_DZIALU, COUNT (NR_DZIALU) AS LICZ_PRAC_DZIALU FROM PRACOWNICY GROUP BY NR_DZIALU HAVING NR_DZIALU = 30 or SELECT NR_DZIALU, COUNT (NR_DZIALU) AS LICZ_PRAC_DZIALU FROM PRACOWNICY WHERE NR_DZIALU = 30 GROUP BY NR_DZIALU ...
Backup Database from ServerGrid.com
Hello I have a hosting account with servergrid.com. I want to backup my database, they say I have to use Sql Server Integration Service to backup the database and I would need a commercial version of Sql Server management studio. I have Sql Server 2005 Developer Edition. I have no idea how to do SSIS backup. I tried playing around with...
Synchronizing databases
Hi I am developing an Adobe AIR application which stores data locally using a SQLite database. At any time, I want the end user to synchronize his/her local data to a central MySQL database. Any tips, advice for getting this right? Performance and stability is the key (besides security ;)) ...
Cross-table UPDATE in SQLITE3
In SQL Server, I can do something like this: UPDATE tbl1 SET col2 = tbl2.col2 FROM table1 tbl1 INNER JOIN table2 tbl2 ON tbl1.col1 = tbl2.col1 I haven't bothered to look whether this is part of any SQL standard or not, and I'm sure there are other ways to do it, but it is astoundingly useful. Here's my problem. I need to do somethin...
SQL group by day, show orders for each day
I have an SQL 2005 table, let's call it Orders, in the format: OrderID, OrderDate, OrderAmount 1, 25/11/2008, 10 2, 25/11/2008, 2 3, 30/1002008, 5 Then I need to produce a report table showing the ordered amount on each day in the last 7 days: Day, OrderCount, OrderAmount 25/11/2008, 2, 12 26/11/200...
MySQL strict select of rows involving many to many tables
I have three tables in the many-to-many format. I.e, table A, B, and AB set up as you'd expect. Given some set of A ids, I need to select only the rows in AB that match all of the ids. Something like the following won't work: "SELECT * FROM AB WHERE A_id = 1 AND A_id = 2 AND A_id = 3 AND ... " As no single row will have more than on...
SQL select join: is it possible to prefix all columns as 'prefix.*'?
I'm wondering if this is possible in SQL. Say you have two tables A and B, and you do a select on table A and join on table B: SELECT a.*, b.* FROM TABLE_A a JOIN TABLE_B b USING (some_id); If table A has columns 'a_id', 'name', and 'some_id', and table B has 'b_id', 'name', and 'some_id', the query will return columns 'a_id', 'name',...
SQL Division using 'not exists' in mysql
I have the following table: 'committee' table commname profname ======================== commA bill commA jack commA piper commB bill commB piper and I am trying to find the professors who are in every committee that 'piper' is in (answer should be piper and bill): I have the following SQL division query but it'...
Does DB2 have an "insert or update" statement?
From my code (Java) I want to ensure that a row exists in the database (DB2) after my code is executed. My code now does a select and if no result is returned it does an insert. I really don't like this code since it exposes me to concurrency issuses when running in a multi-threaded environment. What I would like to do is to put this l...