Hi, Imagine I have table like this: id:Product:shop_id 1:Basketball:41 2:Football:41 3:Rocket:45 4:Car:86 5:Plane:86 Now, this is an example of large internet mall, where there are shops which sell to one customer, so customer can choose more products from each shop and buy it in one basket. However, I am not sure if there is an...
The UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data: if table t has a row exists that has key X: update t set mystuff... where mykey=X else insert into t mystuff... Since Oracle doesn't have a specific UPSERT statement, what's the best way to do this? ...
Background I am working on a legacy small-business automation system (inventory, sales, procurement, etc.) that has a single database hosted by SQL Server 2005 and a bunch of client applications. The main client (used by all users) is an MS Access 2003 application (ADP), and other clients include various VB/VBA applications like Excel a...
I have seen various rules for naming stored procedures. Some people prefix the sproc name with usp_, others with an abbreviation for the app name, and still others with an owner name. You shouldn't use sp_ in SQL Server unless you really mean it. Some start the proc name with a verb (Get, Add, Save, Remove). Others emphasize the entit...
I've worked in shops where I've implemented Exception Handling into the event log, and into a table in the database. Each have their merits, of which I can highlight a few based on my experience: Event Log Industry standard location for exceptions (+) Ease of logging (+) Can log database connection problems here (+) Can build report ...
If this is possible, please provide a sample query or two so I can see how it would work. Both tables will be in the same database. Thanks! ...
Imagine you got an entity in the Google App Engine datastore, storing links for anonymous users. You would like to perform the following SQL query, which is not supported: SELECT DISTINCT user_hash FROM links Instead you could use: user = db.GqlQuery("SELECT user_hash FROM links") How to use Python most efficiently to filter the r...
Let's say I have the following table: CustomerID ParentID Name ========== ======== ==== 1 null John 2 1 James 3 2 Jenna 4 3 Jennifer 5 3 Peter 6 5 Alice 7 5 Steve 8 1 Larry I want to retrieve in one query all th...
I have a table that has a processed_timestamp column -- if a record has been processed then that field contains the datetime it was processed, otherwise it is null. I want to write a query that returns two rows: NULL xx -- count of records with null timestamps NOT NULL yy -- count of records with non-null timestamps Is that...
Hi! We have a situation where a C# application is working with SQL CE 3.5 . To allow for a legacy program to use some of its features we have produced a C++ dll which uses interop to extract the info that it needs from the C# program. For this to work, the C#-program needs to access the database. Its not a very complex scenario. When t...
I'm creating a really complex dynamic sql, it's got to return one row per user, but now I have to join against a one to many table. I do an outer join to make sure I get at least one row back (and can check for null to see if there's data in that table) but I have to make sure I only get one row back from this outer join part if there's ...
I have two databases, one is an MS Access file, the other is a SQL Server database. I need to create a SELECT command that filters data from the SQL Server database based on the data in the Access database. What is the best way to accomplish this with ADO.NET? Can I pull the required data from each database into two new tables. Put thes...
I need a date formula in Oracle SQL or T-SQL that will return a date of the previous week (eg Last Monday's date). I have reports with parameters that are run each week usually with parameter dates mon-friday or sunday-saturday of the previous week. I'd like to not have to type in the dates when i run the reports each week. The data ...
I have the following SQL-statement: SELECT DISTINCT name FROM log WHERE NOT name = '' AND name LIKE '%.EDIT%'; It works fine on Postgres (returns all different names from log, which aren't empty and contain the string '.EDIT'). But on Oracle this statement doesn't work. Any idea why? ...
If I do a Create Table If Not Exists, and a table with the same name exists with fewer rows (or columns), what would happen? ...
I have a table in my pgsql database with columns named "type", "desc", and "start". When I call $dbh->column_info, only "desc" comes back quoted, whereas in pgAdmin3 all three of them are quoted. Is one of them wrong? ...
Hey Stackoverflow, I've been asked by my team leader to investigate MSMQ as an option for the new version of our product. We use SQL Service Broker in our current version. I've done my fair share of experimentation and Googling to find which product is better for my needs, but I thought I'd ask the best site I know for programming answe...
I have tried the following two statements: SELECT col FROM db.tbl WHERE col (LIKE 'str1' OR LIKE 'str2') AND col2 = num results in a syntax error SELECT col FROM db.tbl WHERE page LIKE ('str1' OR 'str2') AND col2 = num results in "Truncated incorrect DOUBLE value: str1" and "Truncated incorrect DOUBLE value: str2" for what looks like e...
I'm running SQL Server 2000 and I need to export the SQL Statement from all the DTS objects so that they can be parsed and put into a wiki documentation if needed. Is there a way to do that? maybe dumping each DTS object out into a text file with the object name as the file name with the name of the process and the date it was extrac...
I've got a lot of similar oracle jobs I need to create, and I'd like to do it programatically. Where does the Oracle store the job library (schema/table)? (yes, I know I might be running with scissors) ...