I am getting an error that an open DataReader associated with this Command, when I'm not using datareader(though probably executereader() is the same thing) how would I close this if I don't have a datareader present? using (SqlConnection conn = new SqlConnection(ConnectionString)) { SqlCommand cmd = new SqlCommand("spSelectAllTypes",...
Just out of curiosity, I was wondering if there are any speed/efficiency differences in using [=] versus [in] versus [like] versus [matches] (for only 1 value) syntax for sql. select field from table where field = value; versus select field from table where field in (value); versus select field from table where field like value; ...
I have to update one column from my user table . Current record in User Table **id , user_name** 1 , sachin rathore 2 , dilip CHOUHAN 3 , GariMA JAIN I want to update user_name column like this 1 , Sachin Rathore 2 , Dilip Chouhan 3 , Garima Jain User column should be in titlize form means first letter of each word should ...
Let's say I have the following simple query SELECT TOP 1 name FROM months which returns name = "march". Is it possible to convert this result? Instead of "march" I want name = "3". Is SQL capable of doing such things? I'm using a MSSQL database. [update] Corrected the query. While writing this simple example I mixed it up with MySQL ...
Now I am working on a project, which has to select top 25 records from a table according to one column, let's call it Num. The case is 1) the table is not sorted by Num; I know this part can be done by using "GROUP ORDER BY" 2) the number of the records in the table might be less than 25. For this question, is there any way to make ...
The complexity of methods in most programming languages can be measured in cyclomatic complexity with static source code analyzers. Is there a similar metric for measuring the complexity of a SQL query? It is simple enough to measure the time it takes a query to return, but what if I just want to be able to quantify how complicated a qu...
This is a particular problem that I have come across many times, but I have never really found a simple solution to this (seemingly) simple problem. How to you ensure that a given parent has a fixed number of children? 1) Example. How do you make sure a given class has only , say, 50 students enrolled..? create table class( class_...
I'd like to be able to set add a field that answers the question "For a value in this record, does that value meet some condition in another table?". I thought I'd try a case-when with an exists, but Teradata (my dbms) does not like it. Any recommendations? select foo, (case when exists (select x.foo from some...
I have a one row table returned from a query that looks something like this [Date1] [Date2] [Date3] [Date4] [Date5] [Date6] and I want all the Dates to stack up like this [Date1] [Date2] [Date3] [Date4] [Date5] [Date6] How would I go about doing this without a bunch of separate queries and union statements? I have tried playing a...
If we select a large number of rows and use an SqlDataReader, will it return the rows as they come or will it wait until the operation is complete? This is with C#.net ...
i am working on a massive site for a client, but their current website (thousands of pages deep) is built on a legacy blogging platform that was built specifically for them in aspx. My job is to take all of the SQL tables filled with data(comments, categories, posts, titles, dates, etc) and convert them to a wordpress ready format. I wan...
These two queries seem to return the same results. Is that coincidental or are they really the same? 1. SELECT t.ItemNumber, (SELECT TOP 1 ItemDescription FROM Transactions WHERE ItemNumber = t.ItemNumber ORDER BY DateCreated DESC) AS ItemDescription FROM Transactions t GROUP BY t.ItemNumber 2. SELECT DISTINCT(t.ItemNumb...
Can I update two identical tables with one query? TABLEA _____________________________ | id | value | |_____________|_____________| | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | |_____________|_____________| ...
Recently I had this question, and everything worked properly until I sent it to my server at DreamHost. The query bellow seems to take too long to execute and I can't figure out why so many rows are processed at once. In my local server the same query was executed in 0.3 seconds. SELECT feed_entries . * FROM feed_entries WHERE id IN (...
Background: I am using Excel 2003 I have 2 Excel files (Source, list), one is essentially my source data. The second is where I am using the excel "Import External data" function to get the data in teh second sheet. I am then using the modifiy query to allow me to use SQL to query my data and limit the data I am displaying. My SQL query...
Hello, I have a table with a 'timestamp' column and a 'value' column where the values are roughly 3 seconds apart. I'm trying to return a table that has daily average values. So, something like this is what i'm looking for. | timestamp | average | | 2010-06-02 | 456.6 | | 2010-06-03 | 589.4 | | 2010-06-04 | 268.5 | etc... ...
Hi, I'm having a problem with my MySQL statement. I need a query that counts the number of comments and the number of topics a user has created. My table structure is something like this: Table 'users' ------------- user_id user_name ... Table 'topics' -------------- topic_id topic_user_id ... Table 'topiccomments' -----------------...
How can I convert a day [1-31] and a month [1-12] and a year (all int), to a date serial IN SQL (without converting to varchar)? ...
Chasing down some DB performance issues in a fairly typical EclipseLink/JPA application. I am seeing frequent queries that are taking 25-100ms. These are simple queries, just selecting all columns from a table where its primary key is equal to a value. They shouldn't be slow. I'm looking at the query time in the postgres log, using the...
I'm analyzing some code that utilizes empty OVER clauses in the contest of Count(). Example: SELECT ROW_NUMBER() OVER (ORDER BY Priority DESC) AS RowID, CAST((COUNT(*) OVER() / @pagesize) AS Int) AS TotalPages, I'm trying to understand why the empty OVER clause is being used here. There are other standard select ele...