I have a rather weak understanding of any of oracle's more advanced functionality but this should I think be possible. Say I have a table with the following schema: MyTable Id INTEGER, Col1 VARCHAR2(100), Col2 VARCHAR2(100) I would like to write an sproc with the following PROCEDURE InsertOrUpdateMyTable(p_id in integer, p_co...
Given this data: CREATE TABLE tmpTable( fldField varchar(10) null); INSERT INTO tmpTable SELECT 'XXX' UNION ALL SELECT 'XXX' UNION ALL SELECT 'ZZZ' UNION ALL SELECT 'ZZZ' UNION ALL SELECT 'YYY' SELECT CASE WHEN fldField like 'YYY' THEN 'OTH' ELSE 'XXX' END AS newField FROM tmpTable The expected resultset is: XXX XXX XXX XXX OTH ...
I'm using sql*plus 9.2 on Oracle 10g enterprise. I have created some scripts that do basic inserts using parameters that I pass through the command prompt. It seemed logical that I should be able to run a bunch of inserts in a loop. So I tried the following: --begin DECLARE TYPE va_orgs IS TABLE OF nbr.lien_item.lien_item_name%type; ...
Say I have a table in a SQL 2005 database with 2,000,000+ records and a few indexes. What advantage is there to using index hints in my queries? Are there ever disadvantages to using index hints in queries? ...
I have a database issue that i currently cannot wrap my head around with an easy solution. In my db I have a table that stores event values.. 0's and 1's with a timestamp. Issue being that it is possible for there to be the same event to occur twice as a business rule. Like below '2008-09-22 16:28:14.133', 0 '2008-09-22 16:28:35.233', ...
Given a simple (id, description) table t1, such as id description -- ----------- 1 Alice 2 Bob 3 Carol 4 David 5 Erica 6 Fred And a parent-child relationship table t2, such as parent child ------ ----- 1 2 1 3 4 5 5 6 Oracle offers a way of traversing this as a tree with some custom syntax ex...
I've used WWW SQL Designer several times to design databases for applications. I'm now in charge of working on an application with a lot of tables (100+ mysql tables) and I would love to be able to look at the relations between tables in a manner similar to what WWW SQL Designer provides. It seems that it comes with the provisions to hoo...
I have two tables containing Tasks and Notes, and want to retrieve a list of tasks with the number of associated notes for each one. These two queries do the job: select t.TaskId, (select count(n.TaskNoteId) from TaskNote n where n.TaskId = t.TaskId) 'Notes' from Task t -- or select t.TaskId, count(n.TaskNoteId) 'Notes'...
i have two tables, both with start time and end time fields. i need to find, for each row in the first table, all of the rows in the second table where the time intervals intersect for example: <-----row 1 interval-------> <---find this--> <--and this--> <--and this--> please phrase your answer in the form of a SQL where-c...
In SQL you can use SELECT * FROM INFORMATION_SCHEMA.TABLES etc to get information about the database structure. I need to know how to achieve the same thing for an Access database. ...
I'm having trouble trying to map nested conditions onto an intuitive interface. eg. How would you represent ((Condition1 AND Condition2) OR (Condition1 AND Condition5)) AND Condition4 ...
What's the most efficient way to select the last n number of rows in a table using mySQL? The table contains millions of rows, and at any given time I don't know how large the table is (it is constantly growing). The table does have a column that is automatically incremented and used as a unique identifier for each row. ...
I have a SQL script that creates a package with a comment containing an ampersand (&). When I run the script from SQL Plus, I am prompted to enter a substitute value for the string starting with &. How do I disable this feature so that SQL Plus ignores the ampersand? ...
I AM SO TIRED OF READING THE WHOLE SQL STATEMENT / STORED PROCEDURE IN FULL CAPS. WHAT THE HELL WERE THE INITIAL DEVELOPERS THINKING? What's the best style (in terms of cap, indentation, lines breaks, etc) to write SQL in? ...
I have an application that tracks high scores in a game. I have a user_scores table that maps a user_id to a score. I need to return the 5 highest scores, but only 1 high score for any specific user. So if user X has the 5 highest scores on a purely numerical basis, I simply return the highest one and then the next 4 user scores. I ...
(Sorry the title isn't more help. I have a database of media-file URLs that came from two sources: RSS feeds and manual entries. I want to find the ten most-recently added URLs, but a maximum of one from any feed. To simplify, table 'urls' has columns 'url, feed_id, timestamp'. feed_id='' for any URL that was entered manually. How would ...
I wrote a Java program to add and retrieve data from an MS Access. At present it goes sequentially through ~200K insert queries in ~3 minutes, which I think is slow. I plan to rewrite it using threads with 3-4 threads handling different parts of the hundred thousands records. I have a compound question: Will this help speed up the prog...
I have a huge database with 100's of tables and stored procedures. Using SQL serve 2005 How can i get the list of Stored procedures that are doing a insert or update operation on a table. ...
I have a VARCHAR column in a SQL Server 2000 database that can contain either letters or numbers. It depends on how the application is configured on the front-end for the customer. When it does contain numbers, I want it to be sorted numerically, e.g. as "1", "2", "10" instead of "1", "10", "2". Fields containing just letters, or lette...
I've got an ASP.NET app using NHibernate to transactionally update a few tables upon a user action. There is a date range involved whereby only one entry to a table 'Booking' can be made such that exclusive dates are specified. My problem is how to prevent a race condition whereby two user actions occur almost simultaneously and cause m...