I'm getting the infamous invalid number Oracle error. Hibernate is issuing an INSERT with a lot of columns, I want to know just the name of the column giving the problem. Is it possible? I hate Oracle messages, in 15 years they haven't improved a bit (the reason why is beyond my imagination). FYI the insert is this: insert into GEM_INV...
I want to (loosely) have a stored procedure like select * from table where col1 like @var the default value of @var is '%', so if I don't have a value for @var it should return every row. However, the problem is it doesn't return rows where col1 is null. How do I return all rows if @var = '%' and rows that are like @var if it does hav...
A homework assignment I have asks the following question: Show how to express \group by cube (a, b, c, d)" using rollup rather than cube. I really don't have the faintest clue how to do this. Where would I start or where could I look for some help? ...
I have this simple example I can't seems to get working : MERGE INTO mytable mt USING dual ON (mt.id = 'AAA' ) WHEN MATCHED THEN UPDATE SET mt.name = 'updated' WHEN NOT MATCHED THEN INSERT (mt.id , mt.name ) VALUES ('AAA', 'Gooood' ); If a 'AAA' record exists in the table, it is updated successfully. But if does no...
I have a simple table in Sybase, let's say it looks as follows: CREATE TABLE T ( name VARCHAR(10), entry_date datetime, in_use CHAR(1) ) I want to get the next entry based on order of "entry_date" and immediately update "in_use" to "Y" to indicate that the record is not available to the next query that comes in. The kicker i...
I removed some rows from a very large table. Then I ran a query that usually runs within a few seconds and found it to be running very slowly after deleting the rows. I re-built my index and ran my query and found it to be fast again. Could deleting those rows caused the index to be fragmented? ...
I would like to run some ad hoc select statements in the IBM System I Navigator tool for DB2 using a variable that I declare. For example, in the SQL Server world I would easily do this in the SQL Server Management Studio query window like so: DECLARE @VariableName varchar(50); SET @VariableName = 'blah blah'; select * from TableName ...
I have around 150 different databases, with dozens of tables each on one of my servers. I am looking to see which database contains a specific person's name. Right now, i'm using phpmyadmin to search each database indvidually, but I would really like to be able to search all databases and all tables at once. Is this possible? How wou...
I'm trying to do what seems like it should be a simple SQL operation, but I'm just not finding the right syntax to do it quickly. I'm using SQLite. The basic problem is that I have a table whose primary key is (objUid, time). It contains the columns objUid, time, and frame. For the purposes of this question, frame is an opaque value....
I have an SQL query thats runs on the Postgres database of my Django based webapp. The query runs against the data stored by Django-Notifications (a reusable app) and returns a list of email addresses that have not opted out of a specific notice type. What I would really like to be able to do is to build an application that does this o...
Why do these queries return different values? The first returns a result set as expected, but the second (which as far as I can tell is exactly the same) does not. Any thoughts? 1: declare @c varchar(200) set @c = 'columnName' select top 1 * from myTable where @c is not null and len(convert(varchar, @c)) > 0 2: SELECT top...
Hi all. I came across a weird situation when trying to count the number of rows that DO NOT have varchar values specified by a select statement. Ok, that sounds confusing even to me, so let me give you an example: Let's say I have a field "MyField" in "SomeTable" and I want to count in how many rows MyField values do not belong to a dom...
I have been working on porting some Oracle DB code to Postgres, and I am noticing some nomenclature differences (Schemas vs Databases) etc. What are some good resources for dealing with these gotchas? Any tips? What needs to be kept in mind? ...
Today at work we got into a discussion about which is the best way to do a query like this : For instance lets assume a users table : tblUsers ID = Autoint Name = String and a login table : tblLogin ID = AUtoint UserID = Int IP = String Browser = String OS = String timestamp = DateTime What would...
I have been trying to find out what the difference is between the IS and AS keywords in PL/SQL when creating an Oracle function or procedure. I have searched and have been unable to find any information on this. Does anyone know the difference? ...
If I have a simple table where the data is such that the rows contains strings like: /abc/123/gyh/tgf/345/6yh/5er In SQL, how can I select out the data between the 5th and 6th slash? Every row I have is simply data inside front-slashes, and I will only want to select all of the characters between slash 5 and 6. ...
I need to find if /3GB switch and /PAE is enabled on a server. Also, I want to know the size of page file and physical RAM on the server. I can check them manually but how can I check them using TSQL on both SQL 2000 and SQL 2005? ...
How do I delete only the top row of a table in SQL Server? I did: set rowcount 1; delete * from table; set rowcount 0; But I am not sure if its a good way to do it. Is there any better way to accomplish that? ...
string queryStr = "select max(patient_history_date_bio) " + "as med_date, medication_name from biological where " + "(patient_id = " + patientID.patient_id + ") " + "group by medication_name;"; using (var conn = new SqlConnection(connStr)) using (var cmd = new SqlCommand(queryStr, conn)) { conn.Open(); using (SqlD...
Hi, I have one flat MAIN_TABLE. I need to insert the records from this table to multiple TABLES. eg. MAIN_TABLE col1, col2, col3, col4, col5 PARENT1_TABLE PT1_ID(PK), col1,col2 PARENT2_TABLE PT2_ID(PK), col3,col4 PARENT2_CHILD_TABLE P2C_ID(PK), PT2_ID(FK), col5, col6 so on. The goal is, I have to move the record from that flat M...