I have a following query (simplified): SELECT Id FROM dbo.Entity WHERE 1 = ALL ( SELECT CASE WHEN {Condition} THEN 1 ELSE 0 END FROM dbo.Related INNER JOIN dbo.Entity AS TargetEntity ON TargetEntity.Id = Related.Ta...
Hi I have the following problem and I am trying to work out what is the best way to solve. I have a table say called [Kpi] and the data would be like this:- [ContractId] [KpiId] [NotificationIntervalInMonths] 1000 1 3 1000 2 5 I have a [Contract] table which contains:- [Contr...
I am loading / inserting data into oracle table , in case of special characters like chinese language characters, i am getting an error like row rejected because maximum size of column was exceeded, i am not getting this error for rows which have english characters which appear to be of same length for same column. I am using SUBSTR and ...
Hoho there. I was just trying to enhance the performance of my application (.NET 3.5, C#) with the use of stored procedures. So I wrote a little test-app to see how much faster these are in comparison to normal queries which looks like this: private static long _StartStored; private static long _EndStored; private static l...
Hello, I've got a question for my SQL query I've got too write. It's a long time ago since I've written an query so I could use some help with mine. I've tried looking for examples but didn't find the right result. Ive written an query but its really isn't working for me.. What im trying to do is get the sum of the total power consumptio...
I want to write a SQL statement something like insert into mytable column1=value1 and ((column2,column3,column4) (select filed1, filed2,filed3 from anothertable where filed4=a_varible)) I am using Mysql. The statement above only expresses what I am going to achieve. Is it possible? Is there a way to achieve this? ...
So I have a question regarding the best practice for a PHP page inserting a new row into a table. There are some required fields (aka NOT NULL) and some optional fields (can be NULL). Is it better to insert everything with one query, or just do the required fields, get the inserted ID and update the other fields with one or more other ...
How to you typically handle roll backs for data edits in a multi-user environment? Do you identify the transaction and build a graph of any subsequent dependent transactions and then roll them all back ? Do most RDBMS's provide an interface or mechanism to do this sort of thing? Naive as I am, I thought about restoring from backup, but ...
Hello everybody! Is it possible to have the WHERE as a wildcard in a SQL query? I have this simple query: SQL = "SELECT ID, Firstname, Lastname, Company, City, Email FROM table WHERE * LIKE '%" & q & "%';" lets say the variable q equals "John Doe". Then I want the query to search for q in both Firstname AND/OR (??) Lastname. I just...
Hi folks, I have a database-table with the following structure: id1 | id2 | weight Now I want to sum up the weight for all entries, that have in id1 or id2 a given value. SELECT id, SUM(weight) FROM myTable WHERE id1=@id or id2=@id Usually, I need to get the sum for more than one id. To speed things up, I first load the ids for wh...
I have two tables, one of them has weeks of year, and the second table has categories. I need to create a table that contains each week and each category, but there's no fields/keys that intersect in two tables: Table 1: week1 week2 week3 week4 Table 2: Cat1 Cat2 Resulting table: week1 cat1 week1 cat2 week2 cat1 week2 cat2 ... we...
I'm writing an application which tracks views of various items across time, and I've hit a bit of a snag. I need to get the difference in views between two adjacent updates. The updates are identified by a UNIQUE key, containing the columns time and id. How would I be able to do this, without running multiple queries? SELECT updates.vi...
For DB2... table1.a is varchar(30) and has an index on it. table2.b is varchar(45) and has an index on it. Will table1.a = table2.b use the index on table1, table2, or both? Although it would seem obvious that it should use both indexes, I'm asking because I believe on sybase, this would only use one of the indexes (I'm not sure which...
I've run into an issue that I need clearer heads to think through. Occasionally this stored procedure (and many others similar to it): CREATE PROC [dbo].[add_address1] @recno int OUTPUT, @clientID int, @street varchar (23), @city varchar (21), @state varchar (2), @zip varchar (9) AS declare @s int; select @s = updated from is_c...
I get this error on many of the TQuery connected to Interbase via BDE. Exact message is: General SQL Error. Dynamic SQL Error, SQL error code = -804, SQLDA missing or incorrect version, or incorrect number/type of variables. The message appears as soon as i try to open/edit the TQuery at design time, or when a Post is done at run tim...
SELECT * FROM dbo.tbh_table WHERE TopicID IN ( SELECT value FROM dbo.fn_split('19,',') ) I have to change above query to execute result like below SELECT * FROM dbo.tbh_table WHERE TopicID LIKE '%19,%' My topicID values are like this 15,19,12,1 But split will give values are 15 19 12 1. because of which i am not able to ex...
I have a query where I select a few columns from each of 5 left outer joined tables. I did an execution plan in SQL Server 2008, and there are basically table scans on all of the joined tables, but the cost is all 0% for them - I'm assuming because there aren't many records in these tables. Then at the last 2 steps of the execution p...
Is it possible to do this in LINQ to SQL in a single command? /* Hello, everyone */ Insert into Messages ( Message, ContactID ) Select Message='Hello', ContactID=ContactID From Contacts (I know I could iterate through Contacts, InsertOnSubmit many times, and SubmitChanges at the end; but this generates one Insert comman...
I have a existing database that needs some changes done. I need to decide whether to alter a table to record the extra data or use the existing table that already records that data for a separate test and link it to the other table with new table. Existing: tSubTest(ixSubTest (pk), ixTest (fk)) tPressureVolume( ixPressureVolume (pk), i...
I'm writing some migration scripts for an Oracle database, and was hoping Oracle had something similar to MySQL's IF EXISTS construct. Specifically, whenever I want to drop a table in MySQL, I do something like DROP TABLE IF EXISTS `table_name`; This way, if the table doesn't exist, the DROP doesn't produce and error, and the script ...