sql

Why does a database query only go slow in the application?

I have a webpage that takes 10 minutes to run one query against a database, but the same query returns in less than a second when run from SQL Server Management Studio. The webpage is just firing SQL at the database that is executing a stored procedure, which in turn is performing a pretty simple select over four tables. Again the code...

Simplify this code

I hate code that looks like its been hacked together. I have just written this: update table1.dbo.totals set @FEE = case when isnull(g.SGROUPS,0) > 1 then @GROUPPRICE * case when CHARINDEX('JMCG', g.GROUPS) > 0 then (g.SGROUPS - 2) else (g.SGROUPS - 1) ...

Converting SQL Query to Access Query - SELECT within SELECT

I have a query in SQL Server that I am trying to convert to a query in MS-Access 2003. The query is designed to be used as the basis for a report. The report has two fields .. 'Cases Assigned' and 'Cases Closed'. SELECT (SELECT COUNT(*) FROM CaseDetail WHERE CaseAssignedDate Between '1/1/2008' AND '1/1/2009') as 'Cases Assigned', (SE...

Conditionally set values in UPDATE statement.

I would like to have a stored procedure that will update values in a table row depending on whether or not the parameters are provided. For example, I have a situation where I want to update all the values, but also a situation where I'm only required to update two values. I was hoping to be able to do this with only one procedure, rathe...

Find the minimum flow in a day and the time it occurs

using MSQL 2005 I have a continuous set of flow measurements (averaged for each 15 minute time slice) I am trying to write a query to find the minimum flow for each day and the time it occurs Finding the minimum flow is easy but getting the time is harder. Currently I do this: select d1.data_point_groupid , min(d1.timeID) [timeI...

How to compute all trigrams of a string in SQL Server

I want to compute and count all trigrams of a string in SQL Server. For example if the string is hello I want the following output: Trigram Count ------- ----- hel 1 ell 1 llo 1 lo- 1 Wikipedia page on n-gram ...

Getting "no join predicate" warning even tho I am using "inner join"

I am using SQL server 2008 R2 and I have a query that inner joins 3 tables: select * from t1 INNER JOIN t2 on t1."Account Key" = t2."Account Key" INNER JOIN t3 on t2."Account Key" = t3."Account Key" The problem is that I am getting a "no join predicate" warning on the second inner join; in addition to that, it is doing cross product ...

How can I retrieve records from one table and related records from another table in one query?

I have two tables, items and itemmeta. items has item_id which links it to itemmeta which has itemmeta_id, item_id, meta_key and meta_value. I did this so if i needed to add a special flag or value to an item i didn't have to keep changing the items table. Now i am paying the price for flexibility. I want to know if i can combine a quer...

Entity Framework - how to join tables without LINQ and with only string?

Hi all, I have a question about Entity Framework. Please answer if you know answer on this. I have such query : String queryRaw = "SELECT " + "p.ProductName AS ProductName " + "FROM ProductEntities.Products AS p " + "INNER JOIN CategoryEntities.Categories AS c " + "ON p.CategoryID = c.CategoryID "; ObjectQuery<Db...

sybase for loop in sql

Hi, I need to update a column value in a table based on values from a Other 2 tables. I was able to write a update statement to achieve this: update T3 set col=T1.x from T1,T2,T3 where ... This takes lot of time since the table T3 has more than million records. I would like to get the value v1 from T1 and loop over them and get co...

Using Perl bind_param with SQL IN statement

Possible Duplicate: Is there SQL parameter binding for arrays? I was wondering if there is anyway to use bind_param with SQL IN statements. According to perl documentation bind_param_array cannot be used as well. Has anyone come across the same situation? http://search.cpan.org/perldoc?DBI#bind_param_array ...

What kind of SQL clause is this? Any way to convert it to SQL?

Hi, what kind of SQL is this? SELECT IFNULL(SUM(prenotazione.VALUTAZIONE),0) AS somma, COUNT(*) AS numero FROM `prenotazione` WHERE prenotazione.USER_ID=18793 AND prenotazione.PRENOTAZIONE_STATO_ID IN (10,11) Im using propel as my ORM. Any way to convert that kind of SQL to Mysql SQL? regards Javi ...

Select N records for each category and order by X

I have a database table that contains blog posts. I want to show on the homepage one (or more) post for each category, ordering by date, for example. So my posts table looks like this: id | title | description | cat | filename | date How would I create such a query? I've thought to use group-by or a subselect but I'm not sure if it's ...

Propel: trying to get the equivalent MySQL clause.

Hi, I have this propel criteria, and then I try to print the equivalent MySQL clause: $c = new Criteria(); $c->add(self::USER_ID, 18793); $result = self::doSelect($c); echo $c->toString(); But tt print this: Criteria: SQL (may not be complete): SELECT FROM prenotazione WHERE prenotazione.USER_ID=:p1 Params: prenotazione.US...

Slow search of minimum in list of maximums with sql with-clause

I have question about this query, a little bit slower for me :( My problem : I'm searching the minimum ticks in a list which contains each maximum of each list of ticks by seed. => Equal to min( max(seed1), max(seed2), max(seed 3), etc. ) PS : Each seed contains ~ 10000 rows (1 row = 1 ticks) PS2 : I have one table with contains all ...

SQL. Query for aggregating certain dependent fields.

Sorry for the vague title, I didn't know how else to phrase this. Suppose you have a table such as the following table of stops on a train and the distances between this stop and the next: Stop NextStop Distance ------------------------------------ Middletown Bloomsbury 101 Bloomsbury Shanksville 36 Shanksville...

Oracle rename columns from select automatically?

I have 2 tables with the following fields. Table1 AA BB CC DD Table2 AA CC EE Query Select t1.*, t2.* from table1 t1, join table2 t2 on table1.DD = table2.EE My data columns back with the following column names: AA, BB, CC, DD, **AA_1**, **CC_1**, EE I don't want the column names like that. I want them to have ...

Simple SQL Query Help - Return Rows Where Criteria Matches ALL

How do I construct my query to return only the values that match a, b, AND c? For example, I would like to return all companies that have financial data with a fiscal year of 2007, 2008, and 2009. SELECT Company from Table WHERE FiscalYear IN (2007,2008,2009) gives me all the companies in which any of the 3 years exists. I need to fi...

Foreign key referencing a view in Oracle

Hi everyone, I'm attempting to reference a view with a foreign key but I am getting this error: "Error: ORA-02270: no matching unique or primary key for this column-list" However, I have created a primary key on this view and verified it in the Constraints tab in TOAD. This is the table I'm attempting to create: CREATE TABLE QUESTI...

SQL Server Url Decoding

I need to run a query against a legacy table that stores URL encoded text. I need this text to be decoded in my results. How to I achieve this? ...