I have data in one column that I want to separate into two columns. The data is separated by a comma if present. This field can have no data, only one set of data or two sets of data saperated by the comma. Currently I pull the data and save as a comma delimited file then use an FoxPro to load the data into a table then process the data ...
In SQL Server, you can do things like this: INSERT INTO some_table (...) OUTPUT INSERTED.* VALUES (...) So that you can insert arbitrary sets of columns/values and get those results back. Is there any way to do this in Oracle? The best I can come up with is this: INSERT INTO some_table (...) VALUES (...) RETURNING ROWID INTO :out_r...
This is three different selects using same subquery. How can I use subquery result instead of doing sub query again. SELECT * FROM Address WHERE address_key IN ( SELECT address_key FROM person_address WHERE peson_key IN (person_list) ); -- person_list := '1,2,3,4' SELECT * FROM ...
I inherited a system that stores default values for some fields in some tables in the database. These default values are used in the application to prepopulate control values. So, essentially, every field in every table in the database can potentially have a default value. The previous developer decided to store these values in a single ...
Given a query like the following: DELETE FROM FOO WHERE ID in (1,2,3,4,....); Is there an upper limit to the number of values in the inclusion? (I've seen Oracle complain at 1000, but that was a long time ago. I have no idea if that was installation-dependant, oracle-version dependant, etc. etc., or how mysql limits this. What are th...
I want to make sure I'm not inserting a duplicate row into my table (e.g. only primary key different). All my field allow NULLS as I've decided null to mean "all values". Because of nulls, the following statement in my stored procedure can't work: IF EXISTS(SELECT * FROM MY_TABLE WHERE MY_FIELD1 = @IN_MY_FIELD1 AND MY_FIELD2 = @IN...
I inherited a bugzilla-like page that allows users to look at the bug list. I'd like to extend it so it can be possible to answer and close them. The problem is I don't know much about the database structure. All I have is connection details and few sample sql queries, which gives good hints about few tables. How can I find out the data...
Hi all, I have a table (table variable in-fact) that holds several thousand (50k approx) rows of the form: group (int) isok (bit) x y 20 0 1 1 20 1 2 1 20 1 3 1 20 0 1 2 20 0 2 1 21 1 ...
How critical are transaction logs after a full backup of a SQL2005 database? Example: I have a database called Test and I don't care about point in time recovery using transaction logs except that I might want to revert back to the database version taken at the time of the last FULL backup. Now, in the backups directory I have a FULL b...
I'm trying to take a VARCHAR(MAX) with data in it as follows: "00001001010001010111010101..." etc. Then encode it as hexadecimal for more efficient return to the client. Is it possible to do this? Either directly or converting the string into a real binary column first before calling master.dbo.fn_varbintohexstr? As an example, given ...
I want to pull down information from two tables from a db. One row from A, and the rows in B with an FK to the row I pulled from A. I'd like to make this a single stored proc with two select statements, rather than having to make two calls to the DB. I know several ways to pull the information from a single select...but can't remember...
I'm writing a high volume trading system. We receive messages at around 300-500 per second and these messages then need to be saved to the database as quickly as possible. These messages get deposited on a Message Queue and are then read from there. I've implemented a Competing Consumer pattern, which reads from the queue and allows for...
Are there any tools to quickly test network reliability to a SQL server? We are receiving error reports from a particular customer who has a random "connection failure" message popup throughout the day. We have identified a few spots where this error might occur, but at different times of the day the code executes perfectly. Some other ...
How can I achieve the following in oracle without creating a stored procedure? Data Set: question_id element_id 1 7 1 8 2 9 3 10 3 11 3 12 Desired Result: question_id element_id 1 7,8 2 9 3 10,11,12 ...
Since I believe this should be a basic question I know this question has probably been asked, but I am unable to find it. I'm probably about to earn my Peer Pressure badge, but I'll ask anyway: Is there a way in SQL Server that I am not aware of for using the wildcard character % when using IN. I realize that I can use OR's like: sele...
Say I have an array of table DDL queries and I want to get the name of each table being created. What would the best approach be for extracting the table name? So for example: $ddl = array( 'CREATE TABLE tableOne ...', 'CREATE TABLE tableTwo ...', 'CREATE TABLE tableThree ...' ); foreach($ddl as $tableDef) { $tableName...
I have a csv file, which is generated weekly, and loaded into a mysql database. I need to make a report, which will include various statistics on the records imported. The first such statistic is how many records were imported. I use PHP to interface with the database, and will be using php to generate a page showing such statistics. ...
Is there any way to get the query text - i.e. plain SQL - of an NHibernate query from a DetachedCriteria object (or any NHibernate object, I just want to be pointed in the right direction) BEFORE it is sent to my server? If so, can I prevent it from executing? ...
Hi Guys, I am using Datareader in my C# code to get data from db and dumping it into excel sheets. I am able to do it for everyrow correctly (based on the data from the sql view). I have a problem in one of the sql views. rolename roledesc roletype roleuser Team Leader (required) xyzxyz Primary Mike Team Leader (r...
I'm trying to write a SELECT statement to select the sum of field but I want to return the sum of numbers less than 30 and also the sum of numbers greater than 30. I realize I can do this with two selects joined together, but I was hoping to find a "neat" way of doing it. ...