Hi, I've a table on SqlServer where a column type should be undefined, I mean, it should be able to contain any kind of type (string, int, boolean and datetime). This because my users should be able to define which type the column is for each record. In the past when I encountered this problem I decided to create it as nvachar(MAX). Wh...
Is there any way I can do a join between two tables where the resulting table has only the columns of the left table without the need to discriminate all the columns names in the select? ...
Hi, I have a table that looks like this: id, col1, col2 col3 0 "Goat" "10" "0" 1 "Cat" "11" "0" 2 "Goat" "12" "1" 3 "Mouse" "13" "0" 4 "Cat" "14" "2" I want be able to return the UNIQUE values in Col1 AND if there are two identical values in col1 then use col3 to decide which value to use i.e. if i...
I'm trying to import a tab delimited file into a table. The issue is, SOMETIMES, the file will include an awkward record that has two "null values" and causes my program to throw a "unexpected end of file". For example, each record will have 20 fields. But the last record will have only two fields (two null values), and hence, unexpec...
I've done this before but can't find where :/ I want to create a variable inside a stored proc and return its value which will be set depending on whether or not other selects return results. basically something like this: @myVar int = 0 BEGIN IF SELECT SomeThing FROM SomeTable @myVar = 1 ELSE IF SELECT SomeOther From SomeO...
I have a query (A) that can returns multiple rows in date order: SELECT encounter_id, department_id, effective_time FROM adt WHERE event_type IN (1,3,7) ORDER BY effective_time I have another query (B) that returns a single row: SELECT encounter_id, department_id, arrival_time FROM ed WHERE event_type = 50 I would like to joi...
Hi, I want to know if a server has integration,reporting,analysis,notification services irrespective of the version(2000 or 2005). Currently, i am using xp_cmdshell 'net start' but it has too much information. Any help? ...
Let's say I have some data, either in a SQL Server 2008 table or a [table]-typed variable: author_id review_id question_id answer_id 88540 99001 1 719 88540 99001 2 720 88540 99001 3 721 88540 99001 4 722 8...
My table TEST has the following rows: test | 1 test | 2 test | 3 How I query it to get the following result? test | 1 - 2 - 3 ...
I created a proc that will return a list of applicants by lastname. I have a problem searching Applicants with last name that has apostrophe (Example O'Connor). Could you please help finding those applicants: Below is my Search Code: if Rtrim(@FirstName) <> '' begin If(Len(@FirstName) < 30) and (CharIndex('%', @FirstName) = 0) and @...
Is there a way to rewrite a Transact SQL statement that uses a CASE WHEN structure to do the same without using the CASE WHEN? I'm using a product that has a built-in query designer and its own pseudo-SQL. It has limitations on what I can use with SQL Server and Oracle. So I have this column that, when the underlying database is Oracle,...
hi 1) The following query obtains from each film category the cheapest possible DVD with the highest rating: SELECT FilmName, Rating, DVDPrice, Category FROM Films AS FM1 INNER JOIN Category AS C1 ON C1.CategoryId = FM1.CategoryId WHERE FM1.DVDPrice = (SELECT MIN(DVDPrice) FROM Films AS FM2 WHERE FM2.DVDPrice IS NOT...
Why won't this piece of code work for me? It gives the error Must declare the scalar variable "@y". DECLARE @y int DECLARE @dbname VARCHAR(50) SET @y = -1 SET @dbname = 'SomeDb.dbo.' SET @sql = 'SELECT @y=1 from ' + @dbname + 'Respondent' exec(@sql) ...
With a table of the following structure and sample data: TableActivity ------------- Type VARCHAR(8) Activity VARCHAR(8) RelatedActivity VARCHAR(8) Type Activity RelatedActivity ------------------------------------------ Start a - Transfer a b Start b ...
Hi ! I'm trying to transform the SQL Query below into Linq to SQL select Categorias.IdCategoria, Categorias.Nome, SUM(lancamentos.valor) from lancamentos left outer join Categorias on Lancamentos.IdCategoria = Categorias.IdCategoria where Month(DataLancamento) = 11 and Credito = 1 and Lancamentos.Ocultar = 0 group by Categorias...
I've got a content management system that contains a hierarchical structure of categories, with sub-categories subject to different ordering options at each level. Currently, that's retrieved by a (rather large) series of queries...but I'm attempting to speed things up by using a recursive stored procedure. (As I understanding, using CT...
Hi, Can anyone please point out what im doing wrong with this Stored Procedure please. I cant get it to compile and my software isnt giving any useful clues as to what is wrong with it. CREATE PROCEDURE web.createSubscriptions ( @Member_Id BIGINT, @Trans_type VARCHAR(100), @Payment_Status VARCHAR(100), @Payment_Date DATE...
How do you create a temporary table exactly like a current table in a stored procedure? ...
Let's say I have a database table that looks like this: ID name salary start_date city region ----------- ---------- ----------- ----------------------- ---------- ------ 1 Jason 40420 1994-02-01 00:00:00.000 New York W 2 Robert 14420 1995-01-02 00:00:00.0...
I have following data in my table. I want to extract ProductId which has this criteria, FieldValue = 1.0 and FieldValue = 'Y' and FieldValue = 'N' This is not possible using following query select * from MyTable WHERE (FieldId = 50 AND (FieldValue BETWEEN '1.0' AND '1.0')) AND (FieldId = 55 AND FieldValue = 'Y') AND (FieldId = ...