I have the following query: select field1,field2 from #table1 left join (select field3, max (field4) as field2 from #table1 group by field3) a on #table1.field3 = a.field3 I want to change it so #table1 is only used once (and preferably the most efficient way also) Any ideas? Thanks! ...
Hi Guys, Ive got a couple of scripts that I need to run on my databse. So I have a few questions. Im simply using Connection, CommandText,CommandType and CommandTimeout when opening a connection to the databse. First question - Does anyone know if through this method, I can create permantent tables, not temporary tables? Secondly - H...
For example, suppose I have a TINYINT column that I want to change into an ENUM. Is it possible to write a MySQL query that changes the datatype of the column while mapping the existing data so that (for example) 0 becomes No, 1 becomes Yes, and 2 becomes Maybe? ...
ExecuteNonQuery requires an open and available Connection. The connection's current state is closed. What am I doing wrong here? I'm assuming you can reuse the connection? Thanks for any help! using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString())) { cn.Open(); // If ...
I want to save user image into a database in C#. How do I do that? ...
This is a contrived example, but consider a scenario where employees have working locations, and where employees have managers who also have working locations: create table WorkingLocation ( ID int not null primary key identity(1,1), Name varchar(50) ) create table Employee ( ID int not null primary key identity(1,1), ...
I am trying to figure out how to get the following code to return the row that it just inserted - a co-worker pointed out and suggested running ALTER FULLTEXT CATALOG uiris_production REBUILD but that cannot be run within a user transaction. The reason this has to be in a transaction is that this is coming from a test framework where th...
I just need pass WHERE condition, like: CREATE DEFINER=`root`@`localhost` PROCEDURE `productpricing2`( IN cond CHAR(200) ) BEGIN SELECT * FROM tbl_products WHERE cond LIMIT 1; END and call it like: CALL productpricing2("productName IS NOT NULL"); Where productName is column in table tbl_products Thanks ...
Hi there, I'm using a parametized query to get records that will display as a grid of products on a page. My design has 4 products listed in each row, but i'd like to apply a class to every 4th item, so that I can clear any margins/padding. Here is my current code; <% Set conn = Server.CreateObject("ADODB.connection") ...
i found this site: here its very well described why it works and why not. but my question is a little different. select 'true' from dual where 'test' not in ('test2',''); why does this query not returing a row? is '' handled like null? thx for your help ...
I am attempting to store over 200 million key value pairs. The value for over 50% of the keys will change over the course of the week, and about 5% of the rows will be permanently removed. Using traditional SQL solutions, this has proven to cause a large amount of fragmentation, causing table bloat (4x the original table size), and som...
I am trying to get row numbers to show up in my LINQPad query. SELECT ROW_NUMBER() OVER(ORDER BY TestNum) AS 'Row Number', TestNum FROM Test I get: Error 195: 'ROW_NUMBER' is not a recognized function name. I also tried ROWNUM() { fn ROW_NUMBER() } { fn ROWNUM() } among others... I am using LINQPad v4.20 Thanks. ...
I'm banging my head on this SQL puzzle since a couple of hours already, so i thought to myself : "Hey, why don't you ask the Stack and allow the web to benefit from the solution?" So here it is. First thing, these are my SQL tables: Fields FieldID INT (PK) FieldName NVARCHAR(50) (IX) FormFields FieldID INT (FK) FormID INT (FK) Va...
I have a simple EntityFramework application that accesses SQL Server 08 with a single table. I want to get a count of the rows like this: Dim x = (From y in _Ctx.Table1).Count Here's the SQL generated from this EF: SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Table1] AS [Extent1] ) AS [GroupBy1...
I've got ORDER BY part of the statement and I have to split it at logical parts my $sql_part = <<EOH IF(some_table.some_field <> 3, 1, 0), some_table.other_field, CASE other_table.third_field WHEN IS NULL THEN 2 ELSE 1 END, other_table.third_field EOH and, you know, original string doesn't contain newlines and IF's c...
What does the (+) mean in the Where Clauses in this SQL statement? SELECT p.FIRSTNAME, p.LASTNAME, p.LOGINNAME, a.DESCRIPTION, a.PERIPHERALNUMBER, a.SUPERVISORAGENT, t.ENTERPRISENAME, t.DIALEDNUMBERID, sp.FIRSTNAME AS SUPER_FIRSTNAME, sp.LASTNAME AS SUPER_LASTNAME,...
There are 2 tables: report (which has a primary key of reportId and a bit field called migrated) and report_detail (which has a foreign key of reportId). I want to delete all the rows from report_detail that have a reportId which, in the report table, has migrated = 1. This is the select query that selects all the rows I want: select * ...
I have two DB's one is feed by filtered data from another, now i'm using perl script witch executes query on foreign DB, stores a result in a csv file, and loads it to local DB using \COPY sytnatx Is there a way to write plpgsql function witch will connect to foreign DB and load filtered data in local DB ( I know it can be done in ie. p...
Hello everyone i want search data from invoices and client by today date I'm using DateDiff() GETDATE() functions for example two tables 1 Client - ID int - Name Varcher 2 Invoice - ID int - ClientID int - date Datetime - Total money query Select * from client c inner join invoice i on c.id = i.ClientID WHERE DateD...
I have a float value that i need to do some calculations on and insert into a numeric(9,2). However in rare (and likley erronous) cases there are some data anomolies and I end up with a value that will not fit into numeric(9,2). What is a good solution to this problem? Maybe just use 9999999.99 if the number is 9999999.99 or greater? A...