sql

SQL Replace Into question

With Replace Into, if I have two fields. FirstName LastName. The table has John Smith in it, if I was to run REPLACE INTO tblNames (FirstName, LastName) VALUES (John, Jones) Would that replace Smith with Jones, or create a new name? What determines if its an Update or and Insert? ...

Query multiple currencies

I need store multiple currencies on my database... Here's the problem: Example Tables: [ Products ] id (INT, PK) name (VARCHAR) price (DECIMAL) currency (INT, FK) [ Currencies ] id (INT, PK) name (VARCHAR) conversion (DECIMAL) # To U$ I'll store the product price with the currency selected by the user... Later I need to search the ...

Is there a single query that can update a "sequence number" across multiple groups?

Given a table like below, is there a single-query way to update the table from this: | id | type_id | created_at | sequence | |----|---------|------------|----------| | 1 | 1 | 2010-04-26 | NULL | | 2 | 1 | 2010-04-27 | NULL | | 3 | 2 | 2010-04-28 | NULL | | 4 | 3 | 2010-04-28 | NULL | To th...

uniqueidentifier Equivalent DataType In C#

what is uniqueidentifier (Sql server 2005) equivalent in C# 3.5 datatype ? ...

mysql query to find every occurrence of a non-ascii character in a table column and replace it with its HTML equivalent

I have a column that contains HTML strings and has characters like ® and ™ in it. I want to replace all such characters with their HTML equivalent. Is this possible? ...

Linq To Sql - SQL Default Constraint Problem

I have a USER table in database. The table has a RegistrationDate column which has a default constraint as GETDATE(). When using LINQ, I don't provide any data for RegistrationDate column to make it default. But SQL Server raises error. I think LINQ tries to insert NULL into the column. How can I make LINQ not to try to insert in the c...

How to return empty cells for repeated values and keep only the cells which are different ?

I have a query that selects some fields to display from a table SELECT Field1, Field2, Field3, Field4 FROM table1 I want instead of returning : To return: How could I modify my SQL statement to return the second figure ? Or at least how to change the gridview properties of .Net to do so (if this is possible) ? ...

how to convert sql union to linq

I have the following Transact SQL query using a union. I need some pointers as to how this would look in LINQ i.e some examples wouldbe nice or if anyone can recommend a good tutorial on UNIONS in linq. select top 10 Barcode, sum(ItemDiscountUnion.AmountTaken) from (SELECT d.Barcode,SUM(AmountTaken) AmountTaken FROM [Aggregation].[dbo...

SQL Server: How to iterate over function arguments

I'm trying to learn SQL and for demonstration purposes I would like to create a loop which iterates over function arguments. E.g. I would like to iterate over SERVERPROPERTY function arguments (propertynames). I can do single select like this: SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS P...

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ' '.

I am trying to query from a temp table and i keep getting this message: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ' '. Can somebody tell me what the problem is? Is it due to convert? The query is select compid,2, convert(datetime, '01/01/' + CONVERT(char(4),cal_yr) ,101) ,0,  Update_dt, th1, th2, th3_pc , Update_id,...

Table transformation / field parsing in PL/SQL

I have de-normalized table, something like CODES ID | VALUE 10 | A,B,C 11 | A,B 12 | A,B,C,D,E,F 13 | R,T,D,W,W,W,W,W,S,S The job is to convert is where each token from VALUE will generate new row. Example: CODES_TRANS ID | VALUE_TRANS 10 | A 10 | B 10 | C 11 | A 11 | B What is the best way to do it in PL/SQL without u...

SQL reporting services beginner.

Hi everyone I am not clear with SQL reporting services can someone pls give me breef explanation or post some tutorial with examples and concept explanations. ] Thanks a lot. ...

SQL Server 2008 - Search Query

Hello, I am not a SQL Expert. I’m trying to elegantly solve a query problem that others have had to have had. Surprisingly, Google is not returning anything that is helping. Basically, my application has a “search” box. This search field will allow a user to search for customers in the system. I have a table called “Customer” in my SQL...

what is the output of this code?

Hi,I have wriiten a part of code for you and I want to know the output ,I need your help because there is not any body for helping me also I think that the out put is A ,is this correct? thanks. declare @v1 varchar(20),@v2 varchar(20) select @v1 = 'NULL' if @v1 is null and @v2 is null select 'A' else select 'B' EDITED: also what is th...

SQL: HAVING clause

See the following SQL statement: SELECT datediff("d", MAX(invoice.date), Now) As Date_Diff , MAX(invoice.date) AS max_invoice_date , customer.number AS customer_number FROM invoice INNER JOIN customer ON invoice.customer_number = customer.number GROUP BY customer.number If the the following was added: HAVIN...

SQL Server replication question.

I had inherited this SQL Server where we put data in a table (Call TableA) on a database (DB-A). I can see the tableA in another database on the same server ( DB-B) gets the same data right away. Any ideas how this is implemented? I am trying to see the trace but so far no luck. Any one has an idea? At this stage I am not sure if its r...

How to detect padding on an integer and treat it as a string?

I have this function to prepare variable to be used in a SQL query: function sqlize($mInput) { if (!isset($mInput)) $mInput = "null"; elseif (strtolower($mInput) == "null") { } elseif (is_numeric($mInput)) { } elseif (is_string($mInput)) { $mInput = trim($mInput); $mInput = addslashes($mInp...

Selecting min date excluding 0000-00-00

I am running a query in SQL, and I need to select a minimum date that is not 0000-00-00. Is there any way to exclude this value and choose the next minimum date? ...

SQL partial max

Struggling with the following SQL problem. Assume a three dimensional table with entries (h,t,q) 1,A,20 1,A,10 1,B,5 2,A,10 2,B,3 2,B,8 3,C,50 4,A,10 etc. I would like to extract 1,30 2,11 3,50 etc. group by the first element and then return the maximum q value of the same type, i.e. for header number 2 there...

How can I treat a sequence value like a generated key?

Here is my situation and my constraints: I am using Java 5, JDBC, and DB2 9.5 My database table contains a BIGINT value which represents the primary key. For various reasons that are too complicated to go into here, the way I insert records into the table is by executing an insert against a VIEW; an INSTEAD OF trigger retrieves the NE...