sql

explain these select statements!

Hi, I can not get the difference betwwn these statements? would you please help me,I have read some sample of select statements but I did not get these ones. SELECT 'B' FROM T WHERE A = (SELECT NULL); SELECT 'C' FROM T WHERE A = ANY (SELECT NULL); SELECT 'D' FROM T WHERE A = A; I use MySQL EDITED: also it has a conditional part t...

load balance timeout SQL connection string

It seems that if there is a sql memory leak somewhere and you dont have time to find it you can use the load balance timeout option in a sql connection string to destory the connection after x seconds. Am i right in assuming I can set the load balance time out to 30-40 seconds and then hunt for the leak latter, while in the mean time th...

SQL Query - Can I compare using LEN in SELECT clause?

I basically want to do this: SELECT HasComments = CASE (LEN(Comments) > 1) WHEN 1 THEN 1 ELSE 0 END FROM TableName In other words, return a boolean telling me whether the length of Comments is greater than 1. This gives me a syntax error. How can I accomplish this? ...

DTS packages, SQL Server 2000

I'm currently in the process of cleaning up a sql server 2000 instance. I found 10 Local Packages that were created 4-5 years ago. In an effort to clean up old processes, I want to delete these packages. My question is, how can I determine if they are still in use. Does the start and end time on the log file suffice? Thanks! ...

FreeText Query is slow - includes TOP and Order By

The Product table has 700K records in it. The query: SELECT TOP 1 ID, Name FROM Product WHERE contains(Name, '"White Dress"') ORDER BY DateMadeNew desc takes about 1 minute to run. There is an non-clustered index on DateMadeNew and FreeText index on Name. If I remove TOP 1 or Order By - it takes less then 1 second to run...

How to retrieve the rows (with maximum value in a field) having a another common field ?

I have a table; let it be called table1; with the following fields and data I need a query that returns the record with the maximum value in Field3 for each group of records having the same value in Field2. So that the query returns: How could this be done using SQL queries ? ...

Select Query Joined on Two Fields?

I've got a few tables in an access database: ID | LocationName 1 | Location1 2 | Location2 ID | LocationID | Date | NumProductsDelivered 1 | 1 | 12/10 | 3 2 | 1 | 01/11 | 2 3 | 1 | 02/11 | 2 4 | 2 | 11/10 | 1 5 | 2 | 12/10 | 1 ID | LocationID | Date | NumEm...

MYSQL: Display Skipped records after LOAD DATA INFILE?

In MySQL I've used LOAD DATA LOCAL INFILE which works fine. At the end I get a message like: Records: 460377 Deleted: 0 Skipped: 145280 Warnings: 0 How can I view the line number of the records that were skipped? SHOW warnings doesn't work: mysql> show warnings; Empty set (0.00 sec) ...

Difficulty with sql query

I have the following tables: TableA (id, tableB_id, tableC_id) TableB (id, expirationDate) TableC (id, expirationDate) I want to retrieve all the results from TableA ordered by tableB.expirationDate and tableC.expirationDate. How can I do this? ...

Converting SQL with subselect in select to HQL

I have the following SQL that I am having problems converting to HQL. A NPE is getting thrown -- which I think has something to do with the SUM function. Also, I'd like to sort on the subselect alias -- is this possible? SQL (subselect): SELECT q.title, q.author_id, (SELECT IFNULL(SUM(IF(vote_up=true,1,-1)), 0) FROM vote WHER...

How to implement best matching logic in TSQL (SQL Server 2000)

I have two tables X and Y: Table X C1 C2 C3 1 A 13 2 B 16 3 C 8 Table Y C1 C2 C3 C4 1 A 2 N 2 A 8 N 3 A 12 N 4 A 5 N 5 B 7 N 6 B 16 N 7 B 9 N 8 B 5 N 9 C ...

SQL to insert latest version of a group of items

I’m trying to determine a good way to handle the scenario below. I have the following two database tables, along with sample data. Table1 contains distributions that are grouped per project. A project can have one or more distributions. A distribution can have one of more accounts. An account has a percentage allocated to it. The distri...

Select those objects whose related objects IDs are *all* in given string

Hi Django people, I want to build a frontend to a recipe database which enables the user to search for a list of recipes which are cookable with the ingredients the user supplies. I have the following models class Ingredient(models.Model): name = models.CharField(max_length=100, unique=True) slug = models.SlugField(max_length...

How can I fake sql data while preserving statements without commenting my server-side code?

I have to use hardcoded values for certain fields because at this moment we don't have access to the real data. When we do get access, I don't want to go through a lot of work uncommenting. Is it possible to keep this statement the way it is, except use '25' as the alias for ratecode? IF(special.ratecode IS NULL, br.ratecode, speci...

What's the best way to parse an Address field using t-sql or SSIS?

I have a data set that I import into a SQL table every night. One field is 'Address_3' and contains the City, State, Zip and Country fields. However, this data isn't standardized. How can I best parse the data that is currently going into 1 field into individual fields. Here are some examples of the data I might receive: 'INDIANAPOL...

How to create relationship between two tables with revisions using Entity Framework

So I am in the process of redesigning a small database (and potentially a much larger one) but want to show the value of using revisions / history of the business objects. I am switching the data from Access to MSSQL 2008. I am having a lot of internal debate on what version of "revision history" to use in the design itself - and thou...

SQL Distinct keyword in assignment statement

I have a query that works: DECLARE @ProductID int SET @ProductID = '1234' SELECT DISTINCT TOP 12 a.ProductID FROM A a WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE b.ProductID = @ProductID) AND a.ProductID != @ProductID It returns a list of 12 product numbers, all unique. I need to store these results in a variable, comm...

Copying a Table in MySQL and including the Primary Key

I have an ancient code snippet I use for copying tables in MySQL. CREATE TABLE new_table (select * from old_table); This works great, with one exception. It doesn't copy the primary key or other table indexes. Is there any way to copy a table in MySQL AND include the indexes/primary key?? ...

Export MySQL Data as Insert Statements

Hi All, I'm working in Ubuntu with MySql and I also have Query Browser and Administrator installed, I'm not afraid of the command line either if it helps. I want simply to be able to run a query and see a result set but then convert that result set into a series of commands that could be used to create the same rows in a table of an id...

Convert Unix Epoch Time to Seconds in SQL or .NET

First let me thank you all for your help. What I need is a function that takes in a EPOCH time stamp, like 1452.235687 and converts it to a readable timestamp like '01-01-1970 00:00:00'. More specifically I only need the time not the date. If at all possible I would prefer a .NET function instead of a SQL stored procedure. However an S...