Lets say I have two separate databases, X and Y, on the same physical server. All of my queries currently run out of X. I find I have one table in Y I would like to be available to X for JOINS. So... now I keep a copy of the one table I need for both X and Y in both X and Y, but the data in Y is constantly changing, so the copy soon b...
For an invoicing app I'm working on my PHB has decided that the parts sold listed on the invoice need to go in a very unique order. I would like to accomplish this with a single sql statement if possible. Essentially the order needs to be as such The most expensive part (but only if there is another part listed at $0) All parts lis...
I am currently handling concurrency on my page using a DateTime field. The data is passed through ASP.NET using an XML which comes with Timezone. So to adjust timezone I am adding 4 hrs to time received (EST) in my procedure and then comparing. Now it was all working fine till Nov 1st. Now the time I am receiving is 5 hrs behind the t...
Hi, I have a sql statement which I want to move it into hibernate criteria, but I am not clue how to do this. The problem is one of the WHERE clause which looks like the following ...(skip other parts)... ? ILIKE strprefix||'%' ...(continue) strprefix is the column name, the ? is the place i need to fill in the value. With criteria...
I've done this before but can't find where :/ I want to create a variable inside a stored proc and return its value which will be set depending on whether or not other selects return results. basically something like this: @myVar int = 0 BEGIN IF SELECT SomeThing FROM SomeTable @myVar = 1 ELSE IF SELECT SomeOther From SomeO...
Hi, I have a server (SQL Server 2005) with multiple archive databases (1 per quarter stretching back 8 years) that are all structurally identical. I frequently need to query back over a certain date range that spans n databases, usually n is small 1-3 but it's possible I need to query the whole set. Any thoughts n the most efficient w...
We have a pile of old databases in archives that have a column named RANGE in one of the tables. These database could have been created in MySQL 4.1 or 5.0. In MySQL 5.1+, RANGE is apparently a keyword for the query language. So now we have to change our schema for that table or we won't have any data at all. Just to be clear, its not...
Hi, all I have tried to cast to float numbers from string in the database fields to compare with another numbers. The field in the database was String type. I have tried to use BETWEEN criteria using cast() as " cast(field, float) BETWEEN 1.003 AND 100.00)" in the where statement. however, it does not help. however, when I tried to ex...
On Query 1 a full table scan is being performed even though the id is an indexed column. Query 2 achieves the same result but much faster. If Query 1 is run returning an indexed column then it returns quickly but if non-indexed columns are returned or the entire row is then the query takes longer. In Query 3 it runs fast but the column...
Is there a way to select multiple values with prepared statements in (My-)SQL? I'm trying to select a couple of rows from a table with the IN-keyword, something like: SELECT * FROM table where id IN (1, 2, 3) The "1, 2, 3" should be passed as a parameter of the statement. Is this possible with PHP/PDO or do I have to concaterate...
Hello, I want to learn more about replication and want to turn it on for my local copy of SQL2008. I watched one video that said I can create a publication on my local machine and then replicate to a different database on the same machine. Their example works and I want to try it. Is there anything I should be concerned about regardi...
Using sqlite i need a table to hold a blob to store a md5 hash and a 4byte int. I plan to index the int but this value will not be unique. Do i need a primary key for this table? and is there an issue with indexing a non unique value? (I assume there is not issue or reason for any). ...
Is it possible through SQL in oracle to compare two tables and list the columns that exist in one but not the other. I have two tables, one (table A) that receives the data from an authoritative source with a specific code and the second is the rest of the data from that import without that specific code (Table B). I was hoping there w...
I'm a frequent SQL Server Management Studio user. Sometimes I'm in situations where I have an update or delete query to run, but I'm afraid some typo or logic error on my part is going to cause me to make undesired, massive changes to a table (like change 1000 rows when I meant to change 2). In the past, I would just clench my fists ...
I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or is it better to do a query like this: SELECT * FROM table1 WHERE ... LIMIT 1 and check to see if any rows were returned? In both queries, ...
SQL Server 2008 database design problem. I'm defining the architecture for a service where site users would manage a large volume of data on multiple websites that they own (100MB average, 1GB maximum per site). I am considering whether to split the databases up such that the core site management tables (users, payments, contact detail...
We use the Fluent nHibernate repository model in a .net MVC project that I'm working on. While running a sql profile to check for areas of improvement we noticed that some objects were getting UPDATEd without an explicit Save. Does anybody know why nHibernate would choose to update an object when I select it? Class: public class Req...
I have a query (A) that can returns multiple rows in date order: SELECT encounter_id, department_id, effective_time FROM adt WHERE event_type IN (1,3,7) ORDER BY effective_time I have another query (B) that returns a single row: SELECT encounter_id, department_id, arrival_time FROM ed WHERE event_type = 50 I would like to joi...
Let say I have a simple Stored Procedure: ALTER PROCEDURE [dbo].[myProc] AS BEGIN SELECT * FROM myTable END How can I do a WHERE statement in Microsoft SQL Server Management Studio to the stored procedure? Something like that: SELECT * FROM myProc WHERE x = 'a'; -- But that doesn't work... ...
I'm having trouble coming up with a query that will find all customers who have purchased both PROD1 and PROD2. Here's a pseudo-query that kind of looks like what I want to do: (obviously this wouldn't work) SELECT COUNT(DISTINCT userid) FROM TRANSACTIONS WHERE product_id = 'prod1' AND product_id = 'prod2' So basically I'm t...