Duplicate result
I am writing a query in SQL server2005. This is returning me a duplicate rows in the result. Can i eliminate this duplication with a particular column as the key? ...
sql
Optimising a SELECT query that runs slow on Oracle which runs quickly on SQL Server
I'm trying to run the following SQL statement in Oracle, and it takes ages to run: SELECT orderID FROM tasks WHERE orderID NOT IN (SELECT DISTINCT orderID FROM tasks WHERE engineer1 IS NOT NULL AND engineer2 IS NOT NULL) If I run just the sub-part that is in the IN clause, that runs very quickly in Oracle, i.e. SELECT DISTINCT orde...
Case-insensitive search using Hibernate
I'm using Hibernate for ORM of my Java app to an Oracle database (not that the database vendor matters, we may switch to another database one day), and I want to retrieve objects from the database according to user-provided strings. For example, when searching for people, if the user is looking for people who live in 'fran', I want to be...
SQL Server - Partitioned Tables vs. Clustered Index?
Let's assume you have one massive table with three columns as shown below: [id] INT NOT NULL, [date] SMALLDATETIME NOT NULL, [sales] FLOAT NULL Also assume you are limited to one physical disk and one filegroup (PRIMARY). You expect this table to hold sales for 10,000,000+ ids, across 100's of dates (easily 1B+ records). As with m...
JDBC Database Connections in a Web App DAL
I am building a small website for fun/learning using a fairly standard Web/Service/Data Access layered design. For the Data Access Layer, what is the best way to handle creating Connection objects to call my SQL stored procedures and why? Bearing in mind I am writing a lot of the code by hand (I know I could be using Hibernate etc to do...
Stored Procedure; Insert Slowness
I have an SP that takes 10 seconds to run about 10 times (about a second every time it is ran). The platform is asp .net, and the server is SQL Server 2005. I have indexed the table (not on the PK also), and that is not the issue. Some caveats: usp_SaveKeyword is not the issue. I commented out that entire SP and it made not difference...
Why does a T-SQL block give an error even if it shouldn't even be executed?
I was writing a (seemingly) straight-forward SQL snippet that drops a column after it makes sure the column exists. The problem: if the column does NOT exist, the code inside the IF clause complains that it can't find the column! Well, doh, that's why it's inside the IF clause! So my question is, why does a piece of code that shouldn't b...
SQL - fetch the row which has the Max value for a column
Table: UserId, Value, Date. I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date. Is there a way to do this simply in SQL? (Preferably Oracle) Thank in advance! [Update:] Apologies for any ambiguity: I need to get ALL the UserIds. But for each UserId, only that ...
Simplest way to create a date that is the first day of the month, given another date
In SQL Server what is the simplest/cleanest way to make a datetime representing the first of the month based on another datetime? eg I have a variable or column with 3-Mar-2005 14:23 and I want to get 1-Mar-2005 00:00 (as a datetime, not as varchar) ...
Inner join vs Where
Is there a difference in performance (in oracle) between Select * from Table1 T1 Inner Join Table2 T2 On T1.ID = T2.ID And Select * from Table1 T1, Table2 T2 Where T1.ID = T2.ID ? ...
What Exception should be thrown when an ADO.NET query cannot retrieve the requested data?
In an attempt to add some parameter validation and correct usage semantics to our application, we are trying to add correct exception handling to our .NET applications. My question is this: When throwing exceptions in ADO.NET if a particular query returns no data or the data could not be found, what type of exception should I use? Psue...
Setting a key field on a view
I am creating an SQL view for a file that strips out the spaces in a particular field. My question is if there is a why to set a key on that new view so a person can still CHAIN the file. We are on V5R3. ...
Most efficient T-SQL way to pad a varchar on the left to a certain length?
As compared to say: REPLICATE(@padchar, @len - LEN(@str)) + @str ...
Sql query to determine status?
I have a table in a MSSQL database that looks like this: Timestamp (datetime) Message (varchar(20)) Once a day, a particular process inserts the current time and the message 'Started' when it starts. When it is finished it inserts the current time and the message 'Finished'. What is a good query or set of statements that, given a par...
Why is a SQL float different from a C# float
Howdy, I have a DataRow pulled out of a DataTable from a DataSet. I am accessing a column that is defined in SQL as a float datatype. I am trying to assign that value to a local variable (c# float datatype) but am getting an InvalidCastExecption DataRow exercise = _exerciseDataSet.Exercise.FindByExerciseID(65); _AccelLimit = (float...
Insert into ... Select *, how to ignore identity?
I have a temp table with the exact structure of a concrete table T. It was created like this: select top 0 * into #tmp from T After processing and filling in content into #tmp, I want to copy the content back to T like this: insert into T select * from #tmp This is okay as long as T doesn't have identity column, but in my case it does....
How to return multiple values in one column (T-SQL)?
I have [UserAliases] (UserId, Alias) table with multiple Aliases per user. I need to query it and return all aliases for a given user, the trick is to return them all in one column. Example: UserId/Alias 1/MrX 1/MrY 1/MrA 2/Abc 2/Xyz I want the query result in the following format: UserId/Alias 1/ MrX, MrY, MrA 2/ Abc, Xyz Thank you...
Timed events with php/MySQL
I need a way to modify a value in a table after a certain amount of time has passed. My current method is as follow: insert end time for wait period in table when a user loads a page requesting the value to be changed, check to see if current >= end time if it is, change the value and remove the end time field, if it isn't, do nothing...
how to determine if a record in every source, represents the same person
I have several sources of tables with personal data, like this: SOURCE 1 ID, FIRST_NAME, LAST_NAME, FIELD1, ... 1, jhon, gates ... SOURCE 2 ID, FIRST_NAME, LAST_NAME, ANOTHER_FIELD1, ... 1, jon, gate ... SOURCE 3 ID, FIRST_NAME, LAST_NAME, ANOTHER_FIELD1, ... 2, jhon, ballmer ... So, assuming that records with ID 1, from sources 1 a...
Paging in Pervasive SQL
How to do paging in Pervasive SQL (version 9.1)? I need to do something similar like: //MySQL SELECT foo FROM table LIMIT 10, 10 But I can't find a way to define offset. ...