Add Column With Fixed Number Of Values (text) To The Select Statement
Hello,
I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.
View Complete Forum Thread with Replies
Related Forum Messages:
Select Boolean Values Merged In A Text Statement
Hi,I have boolean values in a table for ex. Federation. And I want toselect followingSelect 'Insert into' + member + 'test' as test1from federationThen I get error messageServer: Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals bit.Someone can help me out of it.Thanks an advance- Loi -
View Replies !
Adding Column Values Together In SQL SELECT Statement
I have an SQL Select statement that I need to add a column to called SalePrice, the SalePrice column needs to be calculated by adding together the values of 12 columns, then multiplying that value by the value in a another column to calculate margin. My issue is that I can only get it to add 7 column values together, if I add any more columns to the equation it just returns and null result. My DB is SQL 2005 Express SP2. My select statement is below: SELECT dbo.MFG_DATA_Machines.ID, dbo.MFG_DATA_Machines.MachineName, dbo.MFG_DATA_Parts_CPU.PartDescription AS CPU, dbo.MFG_DATA_Parts_CPU.PartCost AS CPUCost, dbo.MFG_DATA_Parts_Motherboard.PartDescription AS Motherboard, dbo.MFG_DATA_Parts_Motherboard.PartCost AS MotherboardCost, dbo.MFG_DATA_Parts_RAM.PartDescription AS RAM, dbo.MFG_DATA_Parts_RAM.PartCost AS RAMCost, dbo.MFG_DATA_Parts_HDD.PartDescription AS HDD, dbo.MFG_DATA_Parts_HDD.PartCost AS HDDCost, dbo.MFG_DATA_Parts_OpticalDrive.PartDescription AS OpticalDrive, dbo.MFG_DATA_Parts_OpticalDrive.PartCost AS OpticalDriveCost, dbo.MFG_DATA_Parts_Video.PartDescription AS Video, dbo.MFG_DATA_Parts_Video.PartCost AS VideoCost, dbo.MFG_DATA_Parts_OS.PartDescription AS OS, dbo.MFG_DATA_Parts_OS.PartCost AS OSCost, dbo.MFG_DATA_Parts_Modem.PartDescription AS Modem, dbo.MFG_DATA_Parts_Modem.PartCost AS ModemCost, dbo.MFG_DATA_Parts_FloppyDrive.PartDescription AS FloppyDrive, dbo.MFG_DATA_Parts_FloppyDrive.PartCost AS FloppyDriveCost, dbo.MFG_DATA_Parts_CardReader.PartDescription AS CardReader, dbo.MFG_DATA_Parts_CardReader.PartCost AS CardReaderCost, dbo.MFG_DATA_Parts_PowerSupply.PartDescription AS PowerSupply, dbo.MFG_DATA_Parts_PowerSupply.PartCost AS PowerSupplyCost, dbo.MFG_DATA_Parts_CaseType.PartDescription AS CaseType, dbo.MFG_DATA_Parts_CaseType.PartCost AS CaseTypeCost, dbo.MFG_DATA_Machines.Notes, dbo.MFG_DATA_Machines.MarginPercent, dbo.MFG_DATA_Machines.PriceOverride, (dbo.MFG_DATA_Parts_CPU.PartCost + dbo.MFG_DATA_Parts_Motherboard.PartCost + dbo.MFG_DATA_Parts_RAM.PartCost + dbo.MFG_DATA_Parts_HDD.PartCost + dbo.MFG_DATA_Parts_OpticalDrive.PartCost + dbo.MFG_DATA_Parts_Video.PartCost + dbo.MFG_DATA_Parts_OS.PartCost + dbo.MFG_DATA_Parts_Modem.PartCost + dbo.MFG_DATA_Parts_FloppyDrive.PartCost + dbo.MFG_DATA_Parts_CardReader.PartCost + dbo.MFG_DATA_Parts_PowerSupply.PartCost + dbo.MFG_DATA_Parts_CaseType.PartCost) * ((dbo.MFG_DATA_Machines.MarginPercent + 100) / 100) AS SalePrice FROM dbo.MFG_DATA_Machines LEFT OUTER JOIN dbo.MFG_DATA_Parts_CaseType ON dbo.MFG_DATA_Machines.CaseType = dbo.MFG_DATA_Parts_CaseType.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Motherboard ON dbo.MFG_DATA_Machines.Motherboard = dbo.MFG_DATA_Parts_Motherboard.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Video ON dbo.MFG_DATA_Machines.Video = dbo.MFG_DATA_Parts_Video.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_RAM ON dbo.MFG_DATA_Machines.RAM = dbo.MFG_DATA_Parts_RAM.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_PowerSupply ON dbo.MFG_DATA_Machines.PowerSupply = dbo.MFG_DATA_Parts_PowerSupply.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_OS ON dbo.MFG_DATA_Machines.OS = dbo.MFG_DATA_Parts_OS.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_OpticalDrive ON dbo.MFG_DATA_Machines.OpticalDrive = dbo.MFG_DATA_Parts_OpticalDrive.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Modem ON dbo.MFG_DATA_Machines.Modem = dbo.MFG_DATA_Parts_Modem.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_HDD ON dbo.MFG_DATA_Machines.HardDisk = dbo.MFG_DATA_Parts_HDD.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_FloppyDrive ON dbo.MFG_DATA_Machines.FloppyDrive = dbo.MFG_DATA_Parts_FloppyDrive.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_CPU ON dbo.MFG_DATA_Machines.CPU = dbo.MFG_DATA_Parts_CPU.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_CardReader ON dbo.MFG_DATA_Machines.CardReader = dbo.MFG_DATA_Parts_CardReader.ID
View Replies !
Concatenating Column Values In SELECT Statement
I'm puzzled as to how to express what I want in a stored procedure. Assume two columns, Surname and GivenName. The surname might be missing. When I originally wrote this app in Access, I used the following expression: SELECT Iif( IsNull(Surname), GivenName, Surname + ", " + GivenName ) AS Agent FROM Agents; I've looked at the syntax for CASE but I can't figure out exactly how to say what I intend, particularly the AS Agent column aliasing. Any help greatly appreciated. Please cc me privately so I receive your assistance at once! TIA, Arthur
View Replies !
Select Fixed Number Of Rows
A table has a column of int type. I need to select a fixed number of rows for each value. For example, if data in that column (c) are 5, 6, 7, and the number I want to select is 2, then I need 2 rows from c=5, 2 from c=6, and 2 from c=7. How to write that query? Any idea? Thanks.
View Replies !
[Resolved] Select A Fixed Number Of Records
I have a sql line that selects records: select * from prodstatistics order by shift_started desc I do not need to read the entire table and want to limit the read to the first 500 records. How would I embedd that in my current statement? Thank you.
View Replies !
Reading A Text File With Fixed Length Column
Hi, I would like to read from a Text File using SSIS Integration Package. The file has a fixed number of columns, let's say 3 columns. There is no row header and each columns length is fixed. There is no delimiter as well. Here is the sample of the file contents: John Doe USA Mary Monroe UK Andy Archibald Singapore Here is the hints to read the file contents 123456789 0123456789 0123456789 ============================== John Doe USA Mary Monroe UK Andy Archibald Singapore If you notice, from the 1st column until the 9th column, it's reserved for the first name. The 10-th column until the 19th column, it's reserved for the last name. Finally the 20-th column until the 29th column is reserved for the Origin Country. Since there's no delimiter inside the flat file contents, i have difficulty in parsing this text using SSIS Package. Please let me know if you need any necessary information. Thanks for all your help. Regards, Hadi Teo.
View Replies !
Count The Number Of Null Values In A Column
Hi, I have a table employee with 4 columns, empno fname lname deptno 1 abc def 10 2 fff hhh 20 3 abc def NULL 4 abc def NULL 5 abc def 50 suppose i want to know the total number of null values in a particular column say deptno how shuld i write a query? select count(deptno) from employee where deptno IS NULL.. When i query this i get the result as 0..
View Replies !
Insert Error: Column Name Or Number Of Supplied Values
In SQL 2005 I created table as SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[logMsg]( [logMsgID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [msg] [nvarchar](256) COLLATE Latin1_General_CI_AS NOT NULL, [AppId] [int] NULL, CONSTRAINT [PK_logMsg] PRIMARY KEY CLUSTERED ( [logMsgID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] and trying to insert values with INSERT INTO [ProxyDB].[dbo].[logMsg] ([msg] ,[AppId]) VALUES ('Text Test',1) Getting error message: Msg 213, Level 16, State 1, Procedure TrgInslogMsg, Line 14 Insert Error: Column name or number of supplied values does not match table definition. Urgent help is required
View Replies !
Number Formatting In A SQL Select Statement
Hi I'm trying to convert and format integer values in a SQL Server select statement to a string representation of the number formated with ,'s (1000000 becomes 1,000,000 for example). I've been looking at CAST and CONVERT and think the answers there somewhere. I just don'tseem to be able to work it out. Anyone out there able to help me please? Thanks,Keith.
View Replies !
How To SELECT By Column Number ?
I want to select with column order without field name . Is it possible ? SQL2000 db ... I want to select a field by its order in table, but without naming it to have a constant name of it, like : select (column01) as L1 from myTable
View Replies !
Parameterized Query That Counts The Number Of Null Values In An Int32 Column
Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work: cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null)); cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn"; cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value)); cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn"; cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null)); cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn"; cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value)); cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn"; cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32)); cmd.Parameters["@IntColumn"].Value = SqlInt32.Null; cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn"; cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32)); cmd.Parameters["@IntColumn"].Value = SqlInt32.Null; cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn"; cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32)); cmd.Parameters["@IntColumn"].Value = DBNull.Value; cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn"; cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32)); cmd.Parameters["@IntColumn"].Value = DBNull.Value; cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn"; cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant)); cmd.Parameters["@IntColumn"].Value = SqlInt32.Null; cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn"; cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant)); cmd.Parameters["@IntColumn"].Value = SqlInt32.Null; cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn"; md.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant)); cmd.Parameters["@IntColumn"].Value = DBNull.Value; cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn"; cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant)); cmd.Parameters["@IntColumn"].Value = DBNull.Value; cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn"; Whenever I use a "is not" in the query I get an exception, otherwise it returns a count of 0 which is not accurate. Any help will be appreciated.
View Replies !
Max Values In A Select Statement
I have a table which has 4 fields, patientid,testdate,testtype,results. I want to select the most recent testdate by a patient regardless of the results, or the testtype. I do however need those fields for my query. I tried the below, but I get more than 1 record if the person has had two different types of tests. For instance if patient 100 has the following 2 records I just want the most recent patientid testdate testype results 100 01/02/2002 TBI ASYMP 100 02/02/2001 PPD 00000 select max(testdate)as testdate,testtype, other_id_number from vw_cms_tb_lasttest What am missing? Thanks group by other_id_number,testtype order by other_id_number,testtype
View Replies !
Getting Different Values From Select Statement
I am running a select statement against a function that is giving me different values depending on how it is called. When I run it through sql server management studio, I get the proper results, the default value column has the parameters default value. When I call it through my web app, I get this in the default value column: *** ERROR OCCURRED level 2 (this is not a default value) *** Why would the same sql statement get different results? Here is the call: select * from dbo.f_GetSProcParameters('webservices_BENEFICIAL_USES_DM_SELECT') Here are the two functions: --------------------------------------------------- USE [si_training_db] GO /****** Object: UserDefinedFunction [dbo].[f_GetSProcParameters] Script Date: 06/13/2008 09:29:21 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO ALTER function [dbo].[f_GetSProcParameters](@StoredProcedureName VARCHAR(128) ) Returns @sProcParameters Table(ParmName VarChar(128), DefaultValue VarChar(128), HasDefault Bit, IsInput Bit) /* * DESCRIPTION : This function returns a table listing all the parameters of a stored *procedure and the default values of those parameters. * * RETURNS : table * * * ORIG AUTHOR : Josh Kinder * DATE WRITTEN : 3/14/2006 * * REVISIONS LOG * *ID/Date PC# Description *------- --- ---------------------------------------------------------- */ As Begin Declare @Count SmallInt, @Index SmallInt, @CurParm VarChar(128), @DefaultVal VarChar(128), @IsInput BIT /*----------------------------------------------------------------------------------------------------------------------------------*/ --EDIT CHECK -sProc only /*----------------------------------------------------------------------------------------------------------------------------------*/ -- Check that the proc name is valid If OBJECT_ID(@StoredProcedureName, 'P') Is Null Begin Goto ScriptErr End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ Declare @ParmTable Table (Id SmallInt Identity Primary Key Clustered, ParmName VarChar(128)) Insert Into @ParmTable Select a.Name From SysColumns a Inner Join SysObjects b On b.Id = a.Id Where b.Name = @StoredProcedureName Select @Count = Count(Id), @Index = 1 From @ParmTable While (@Index<=@Count) Begin Select @CurParm = ParmName From @ParmTable Where Id = @Index Set @DefaultVal = dbo.f_GetsProcParamDefaultValue(@StoredProcedureName, @CurParm) SELECT@IsInput = CASE WHEN params.is_output = 1 THEN 0 ELSE 1 END FROMsys.procedures AS procs INNER JOINsys.all_parameters AS params ON params.object_id = procs.object_id LEFT JOINsys.types AS types ON types.system_type_id = params.system_type_id AND types.user_type_id = params.user_type_id WHEREprocs.is_ms_shipped = 0 AND params.name = @CurParm AND procs.name = @StoredProcedureName Insert Into @sProcParameters ( ParmName, DefaultValue, HasDefault, IsInput ) Values ( @CurParm, @DefaultVal, Case When @DefaultVal = 'NoDefaultExists' Then 0 Else 1 End, @IsInput ) Set @Index = @Index + 1 End ScriptErr: Return End --------------------------------------------------- USE [si_training_db] GO /****** Object: UserDefinedFunction [dbo].[f_GetsProcParamDefaultValue] Script Date: 06/13/2008 09:30:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER function [dbo].[f_GetsProcParamDefaultValue] ( @StoredProcedureName VarChar(128), @ParamName VarChar(128) ) ReturnsVarChar(128) /* * DESCRIPTION : This function returns a table listing all the parameters of a stored *procedure and the default values of those parameters. * The following copyright info is for the parsing algorithm to get the default value. I obtained the code from SQL Farms Solutions and their only stipulation for use is that the copyright info remain with the code. Although I customized it for us, it is still their algorithm. ================================================================================== Copyright © SQL Farms Solutions, www.sqlfarms.com. All rights reserved. This code may be used at no charge as long as this copyright notice is not removed. ================================================================================== * RETURNS : table * * * ORIG AUTHOR : Josh Kinder * DATE WRITTEN : 3/14/2006 * * REVISIONS LOG * *ID/Date PC# Description *------- --- ---------------------------------------------------------- */ As Begin Declare @minRow Int, @maxRow Int, @tmpInt Int, @tmpText VarChar(4000), @tmpCharPos1 Int, @tmpCharPos2 Int, @tmpCharPos3 Int, @ParameterDefault VarChar(128), @sProcTextVarChar(4000), @DelimiterChar(1), @Count SmallInt, @Index SmallInt, @CurTextVarChar(4000) Select @minRow = Null, @maxRow = Null, @Delimiter = char(13) Declare @ProcText Table (Id Int Identity(1, 1) Primary Key Clustered, ProcText VarChar(4000)) Insert Into @ProcText Select a.Text From SysComments a Inner Join SysObjects b On b.Id = a.Id Where b.Name = @StoredProcedureName Select @Count = Count(Id), @Index = 1 From @ProcText While (@Index<=@Count) Begin Select @CurText = Coalesce(@CurText,'') + ProcText From @ProcText Where Id = @Index Set @Index = @Index + 1 End /*----------------------------------------------------------------------------------------------------------------------------------*/ --EDIT CHECKS /*----------------------------------------------------------------------------------------------------------------------------------*/ -- Check that the parameter name is valid for the proc If Not Exists( Select 1 From INFORMATION_SCHEMA.PARAMETERS Where SPECIFIC_NAME = @StoredProcedureName And PARAMETER_NAME = @ParamName ) Begin Set @ParameterDefault = '*** ERROR OCCURRED level 1 (this is not a default value) ***' Goto ScriptErr End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ --Get sProc into a workable temporary table /*----------------------------------------------------------------------------------------------------------------------------------*/ Declare @ProcContent Table (Idx Int Identity(1, 1) Primary Key Clustered, ProcText VarChar(4000)) Insert Into @ProcContent Select Value From dbo.f_Split(@CurText, @Delimiter, 1, 0) -- Make sure that some rows were returned successfully If @@ROWCOUNT = 0 Begin Set @ParameterDefault = '*** ERROR OCCURRED level 2 (this is not a default value) ***' Goto ScriptErr End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ --Get location of parm and get ready to parse /*----------------------------------------------------------------------------------------------------------------------------------*/ -- Get the first line where the parameter is referenced in the proc code. -- (the LIKE here is a little complex since it is possible that multiple parameters -- will start with the same string. Most cases of interest are covered by the -- conditions listed below). Select @minRow = Min(Idx) From @ProcContent Where ProcText Like '%' + @ParamName + ' %' Or ProcText Like '%' + @ParamName + Char(9) + '%' Or ProcText Like '%' + @ParamName + Char(10) + '%' Or ProcText Like '%' + @ParamName + Char(13) + '%' Or ProcText Like '%' + @ParamName + '=%' Or ProcText Like '%' + @ParamName + '%=%' Or ProcText Like '%' + @ParamName + ',%' -- Check that the parameter is referenced in the code If @minRow Is Null Begin Set @ParameterDefault = '*** ERROR OCCURRED level 3 (this is not a default value) ***' Goto ScriptErr End -- Get the proc line where the word 'AS' is declared. 'AS' is required -- upon proc creation to complete the variable declaration. -- Note: This cover most cases of interest. There could be scenarios where -- additional condition should be applied. Select @maxRow = Min(Idx) From @ProcContent Where ProcText Like '% AS' Or ProcText Like '% AS ' Or ProcText Like '% AS' + Char(9) Or ProcText Like '% AS' + Char(10) Or ProcText Like '% AS' + Char(13) Or ProcText Like 'AS %' Or Upper(RTrim(LTrim(ProcText))) = Char(10) + 'AS' Or Upper(RTrim(LTrim(ProcText))) = 'AS' Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(10) Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(13) Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(13) + Char(10) Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(10) + Char(13) -- Check that the 'AS' string was found successfully If @maxRow Is Null Begin Set @ParameterDefault = '*** ERROR OCCURRED level 4 (this is not a default value) ***' Goto ScriptErr End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ --Parse and get the default value /*----------------------------------------------------------------------------------------------------------------------------------*/ -- Get the first proc line of code where the parameter is referenced, for string processing, -- and append to it all proc rows until the 'AS' string Select @tmpText = LTrim(RTrim(Replace(Replace(ProcText, Char(10), ''), Char(13), ''))) + ' ' From @ProcContent Where Idx = @minRow While @minRow < @maxRow Begin Set @minRow = @minRow + 1 Select @tmpText = @tmpText + ' ' + LTrim(RTrim(Replace(Replace(ProcText, Char(10), ''), Char(13), ''))) + ' ' From @ProcContent Where Idx = @minRow End -- Find the position of the parameter name. Delete all text before that position. Set @tmpInt = Null Set @tmpInt = PatIndex('%' + @ParamName + '%', @tmpText) - 1 Set @tmpText = Right(@tmpText, Len(@tmpText) - @tmpInt) -- At this point we are nearly done: -- We check whether the character '=' comes before the 'AS' or ',' string -- If not- the parameter has no default value. -- If so, we continue to find the value of the default parameter Set @tmpCharPos1 = PatIndex('%=%', @tmpText) Set @tmpCharPos2 = PatIndex('%,%', @tmpText) Set @tmpCharPos3 = PatIndex('% AS %', @tmpText) If @tmpCharPos1 <= 0 Or (@tmpCharPos1 > @tmpCharPos2 And @tmpCharPos2 > 0) Or (@tmpCharPos1 > @tmpCharPos3 AND @tmpCharPos3 > 0) Begin -- The column does not have a default Set @ParameterDefault = 'NoDefaultExists' End Else Begin -- Column has a default and it is left to find it. -- First chop the string until the '=' character Set @tmpInt = NULL Set @tmpInt = PatIndex('%=%', @tmpText) - 1 Set @tmpText = LTrim(Right(@tmpText, Len(@tmpText) - @tmpInt)) -- Now, we p*** the remaining string until we get a ',' or a ' ' character Set @tmpCharPos1 = NULL Set @tmpCharPos2 = NULL Set @tmpCharPos1 = PatIndex('%,%', @tmpText) Set @tmpCharPos2 = PatIndex('% %', @tmpText) Set @tmpInt = NULL If @tmpCharPos2 > @tmpCharPos1 AND @tmpCharPos1 > 0 Set @tmpInt = @tmpCharPos1 - 1 Else Set @tmpInt = @tmpCharPos2 - 1 If @tmpInt <= 0 Begin Set @ParameterDefault = '*** ERROR OCCURRED level 5 (this is not a default value) ***' Goto ScriptErr End Set @ParameterDefault = SubString(@tmpText, 1, @tmpInt) -- If the parameter default is a string, then we will have an '' at each side of it. -- These last lines of code will get rid of the ''. If Len(@ParameterDefault) >= 1 If Right(@ParameterDefault, 1) = '''' Set @ParameterDefault = Left(@ParameterDefault, Len(@ParameterDefault) - 1) If Len(@ParameterDefault) >= 1 If Left(@ParameterDefault, 1) = '''' Set @ParameterDefault = Right(@ParameterDefault, Len(@ParameterDefault) - 1) End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ Goto ScriptExit ScriptErr: ScriptExit: Return @ParameterDefault End
View Replies !
Number De Caracters In Select Column
Hello, I have a field of type the Var, the stored data makes about 700 characters. During a select through the query analyser, it tronque in about 200 or 300 characters. How to explain it? thank in advance Pascal
View Replies !
Select Statement When Values Are Not Null
Hi. I have an sql table which contains a number and a name. I would like to create a select statement that will display these two fields in the format : "number | name", but if there is a null value in the number it will display only the name and vice versa. How can I do it ? Any help is appreciated.
View Replies !
Use Listbox Values To Do Select Statement
Hi, I have SQL database 2000 which has one table Sheet1, I retrieved the columns in the ListBox, then chosed some of them and moved it to ListBox2. The past scenario worked great, and I checked the moved values, it was succesfully moved, but when I tried to copy the values in ArrayList to do a select statement it didn't worked at all. public string str;protected void Button3_Click(object sender, EventArgs e) {ArrayList itemsSelected = new ArrayList(); string sep = ","; //string str;for (int i = 0; i < ListBox2.Items.Count; i++) {if (ListBox2.Items[i].Selected) { itemsSelected.Add(ListBox2.Items[i].Value); } int itemsSelCount = itemsSelected.Count; // integer variable which holds the count of the selected items. str = ListBox2.Items[i].Value + sep; Response.Write(str); } SqlConnection SqlCon = new SqlConnection("Data Source=AJ-166DCCD87;Initial Catalog=stat_rpt;Integrated Security=True;Pooling=False"); String SQL1 = "SELECT " + str + " from Sheet1"; SqlDataAdapter Adptr = new SqlDataAdapter(SQL1, SqlCon); SqlCommandBuilder CB = new SqlCommandBuilder(Adptr);DataTable Dt = new DataTable(); Adptr.Fill(Dt); //return Dt; GridView1.DataBind(); SqlCon.Close(); } I did some changes and the new error message is Incorrect syntax near the keyword 'from'. Thank you
View Replies !
Return Select Statement Or Values Using SqlDataSource?
Hello all, I have been working with a DetailsView control for the past week and it is a great control, but also lacks on some departments. Anyhow I need to know what the best approach for this scenerio would be? I have a SqlDataSource" <asp:SqlDataSource ID="SqlUpsertAffiliateDetails" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>" SelectCommand="SELECT am.affiliate_id AS AffiliateId, am.member_id AS MemberId, m.First_Name, m.Last_Name, am.category_id AS CategoryId, ac.category_name, am.profile_web_address AS WebAddress, am.profile_email_1 AS Email, am.comments AS Comments, am.date_modified FROM tAffiliateMaster AS am WITH (NOLOCK) INNER JOIN tAffiliateCategories AS ac WITH (NOLOCK) ON am.category_id = ac.category_id INNER JOIN rapdata..Member AS m WITH (NOLOCK) ON am.member_id = m.Member_Number WHERE (am.affiliate_id = @AffiliateId)" UpdateCommand="spUpsertAffiliateProfile" UpdateCommandType="StoredProcedure"> <SelectParameters> <asp:QueryStringParameter Name="AffiliateId" QueryStringField="affiliate_id" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="Action" Type="Byte" DefaultValue="2" /> </UpdateParameters> </asp:SqlDataSource> And my SP:/* 09-19-07 Used to update affiliate profile */ CREATE PROCEDURE spUpsertAffiliateProfile @Action tinyint, @AffiliateId int, @MemberId int = -1, @CategoryId int, @WebAddress varchar(50), @Email varchar(50), @Comments varchar(1500) AS SET NOCOUNT ON -- Find errors first, check is not needed if deleting IF @Action <> 3 IF NOT EXISTS (SELECT Member_Number FROM rapdata..Member_Association WHERE Member_Number = @MemberId AND Status = 'A' AND Association_ID = 'TRI' AND Bill_Type_Code LIKE '%AF%') BEGIN SELECT retval = 'A qualified member ID was NOT found. Action Failed.', errorcount = 1, 0 AS affiliate_id RETURN END IF @Action = 1 IF EXISTS (SELECT member_id FROM tAffiliateMaster WHERE member_id = @MemberId) BEGIN SELECT retval = 'This member has already been listed. Action Failed.', errorcount = 1, 0 AS affiliate_id RETURN END IF @Action = 1 AND @AffiliateId = 0-- insert BEGIN INSERT INTO tAffiliateMaster (member_id, category_id, profile_web_address, profile_email_1, comments) VALUES (@MemberId, @CategoryId, @WebAddress, @Email, @Comments) SELECT retval = 'Record Entered', errorcount = 0, @@IDENTITY AS affiliate_id RETURN END ELSE IF @Action = 2 AND @AffiliateId > 0-- update BEGIN UPDATE tAffiliateMaster SET category_id= @CategoryId, profile_web_address=@WebAddress, profile_email_1=@Email, comments=@Comments WHERE affiliate_id = @AffiliateId AND member_id = @MemberId SELECT retval = 'Record Updated', errorcount = 0, @AffiliateId AS affiliate_id RETURN END ELSE IF @Action = 3 AND @AffiliateId > 0-- delete BEGIN DELETE tAffiliateMaster WHERE affiliate_id = @AffiliateId SELECT retval = 'Record Deleted', errorcount = 0, 0 AS affiliate_id RETURN END GO My question is how will I be able to return the retval? Will I need to do it within the code behind of the SqlDataSource Updated Event? Thanks!
View Replies !
Putting Commas Between Select Statement Values
Hello, This may be a strange request, but I am going to ask about it anyways. Say for example if I have a table named TEST and in the table there is a column named NUMBERS, such that it is like this: NUMBERS 1 2 3 4 How could I use a select statement in a way that a comma would seperate every return value, such that if I go 'Select NUMBERS from TEST' I would get: 1,2,3,4 Instead of: 1 2 3 4 Any ideas? Thanks
View Replies !
Select Statement For Comma Separated Values
hi, my sample SQL Server DB Tables are like, SID Skill --- ------- 1 JAVA 2 ORACLE 3 C 4 C++ PID Skillset --- --------- 1 1,2,3 2 2,4 3 1,2,3,4 4 3 I need the Query to display Person skills as follows... PID Skillset --- -------------- 1 Java,Oracle,C 2 Oracle,C++ 3 Java,Oracle,C,C++ 4 C and another query for Search.. if i give the search string as Java,C or i will pass the SID 1,3. i need to diplay the person records which contains the SID. output will be... PID Skillset --- -------------- 1 Java,Oracle,C 3 Java,Oracle,C,C++ 4 C or PID Skillset --- --------- 1 1,2,3 3 1,2,3,4 4 3 Plz help meee.. Thanking you in advance for your help.
View Replies !
'combining' Values From A Select Statement Into A String
Hi, I got a really simple question here. Say I have a table with ID, Name 1, A1 2, A2 3, A3 .... 10, A10 Now I want to combine the names into another table grouped by their ID (say 1-5, 6-10), so this new table has two names instead of 10: A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 Is there a function that allows me to 'combine' the names from a select statement? Thanks in advance, Steven
View Replies !
Zero Count Values Not Appearing In SELECT Statement
Hi all I have the following tables: Code Snippet CREATE TABLE #Lkp_Circle ( ID INT , Abbreviation varchar(50) ) GO CREATE TABLE #Lkp_OtherCircles ( Circle varchar(50) ) GO CREATE TABLE #Tbl_User ( ID INT, Name VARCHAR(50), IsActive bit ) GO CREATE TABLE #Tbl_UserDetails ( AssociateID INT, CircleID INT ) GO INSERT INTO #Lkp_Circle VALUES (1,'C1') INSERT INTO #Lkp_Circle VALUES (2,'C2') INSERT INTO #Lkp_Circle VALUES (3,'C3') INSERT INTO #Lkp_Circle VALUES (4,'C4') INSERT INTO #Lkp_Circle VALUES (5,'C5') INSERT INTO #Lkp_Circle VALUES (6,'C6') INSERT INTO #Lkp_Circle VALUES (7,'C7') GO INSERT INTO #Lkp_OtherCircles VALUES ('C3') INSERT INTO #Lkp_OtherCircles VALUES ('C4') INSERT INTO #Lkp_OtherCircles VALUES ('C5') INSERT INTO #Lkp_OtherCircles VALUES ('C6') GO INSERT INTO #Tbl_User VALUES ( 101,'U 1','True') INSERT INTO #Tbl_User VALUES ( 102,'U 2','True') INSERT INTO #Tbl_User VALUES ( 103,'U 3','True') INSERT INTO #Tbl_User VALUES ( 104,'U 4','True') INSERT INTO #Tbl_User VALUES ( 105,'U 5','True') GO INSERT INTO #Tbl_UserDetails VALUES(101,3) INSERT INTO #Tbl_UserDetails VALUES(102,4) INSERT INTO #Tbl_UserDetails VALUES(103,5) INSERT INTO #Tbl_UserDetails VALUES(104,5) INSERT INTO #Tbl_UserDetails VALUES(105,3) GO SELECT ISNULL(Circle,'Total') Circle, ISNULL(COUNT([HeadCount]),SUM(1)) AS [Total] FROM ( SELECT DISTINCT 'Circle' = CASE WHEN #Lkp_Circle.Abbreviation IN (SELECT Circle FROM #Lkp_OtherCircles) THEN #Lkp_Circle.Abbreviation WHEN #Lkp_Circle.Abbreviation NOT IN (SELECT Circle FROM #Lkp_OtherCircles) THEN 'Others' ELSE 'Total' END,ISNULL(#Tbl_UserDetails.AssociateID,0) AS 'HeadCount' FROM #Tbl_User INNER JOIN #Tbl_UserDetails ON #Tbl_User.ID = #Tbl_UserDetails.AssociateID INNER JOIN #Lkp_Circle ON #Tbl_UserDetails.CircleID = #Lkp_Circle.ID WHERE #Tbl_User.IsActive='True' AND #Tbl_User.ID>0 AND #Tbl_UserDetails.AssociateID>0 ) AS PivotTable GROUP BY Circle WITH Cube DROP TABLE #Tbl_User,#Tbl_UserDetails,#Lkp_Circle,#Lkp_OtherCircles ----EXPECTED RESULT --Circle HeadCount --C3 2 --C4 1 --C5 2 --C6 0 --Others 0 --Total 5 -- ----ACTUAL RESULT --Circle HeadCount --C3 2 --C4 1 --C5 2 --Total 5 The criteria for Others is that those circles which are not part of #Lkp_OtherCircles i.e. C1,C2,C3 and C7 clubbed together. I have tried checking for the condition ISNULL when for that circle there is no user but the end result is same. Can someone tell me where I am going wrong and how to correct it?
View Replies !
Overloading Select Statement Return Values
Hey guys, This is what I think and hope is a fairly straight forward SQL question Essentially, I have a table which has the following columns that are relevent to my question: PROJID ACTIVITY_NAME COMPLETION_DATE Rows in this table are, for example: PROJID ACTIVITY_NAME COMPLETION_DATE 1 Prepro 10/12/2007 3:42:30 2 Prepro 10/13/2007 9:16:27 2 QA 10/13/2007 10:00:01 2 Delivery 10/14/2007 09:31:12 etc. So, really the key is the PROJID & the ACTIVITY_NAME (really, there's a unique column ID, but for this question, I'll leave it at that). (Though this should be much easier to accomplish in code, the system is not built that way so) Is there a good way that I could return a status for a given PROJID based on whether a row exists for a given PROJID). In other words, ultimately, I would like to return something like this: PROJID LAST_ACTIVITY ---------------------------------------------- 1 Prepro 2 Delivery where the activity order (in this case) is Prepro, QA, Delivery. So because a Delivery row exists for PROJID 2, then the LAST_ACTIVITY would return "Delivery" and because only Prepro exists for PROJID 1, the LAST_ACTIVITY returned would be Prepro I really appreciate the help Thanks, Steve
View Replies !
Select 'many Values' From One Column
I'm building a textbox with search functionality towards SQL 2000. The textbox is to include various search phrases, like "phrase1 phrase2 phrase3". Sort of like search engines where all words are considered. Do keep in mind that it's SQL2000 and not 2005. I've got all the strings I want to perform the search against in one column but various values in this column. The only thing I've come up with is to create "WHERE columnName LIKE @phrase1 OR columnName LIKE @phrase2" where I inject % as apropiate and chop up the string, but I'm hoping this is not the solution because it doesn't seem really clean (with 10 search words) and today I'm not having a 'bad-hack' day so I want it better. Whats is the best way of doing this? Cheers! /Eskil
View Replies !
Select Row Values To Be Column Name
I am new to sql query and have a question.I would like to display row column values as column names.questions table consisted ofquestion_id, group_id, questionresponse table consisted ofresponse_id, group_id, question_id, answerI would like to display the answer responses under the columns ofquestions : such asresponse_id, question(1), question(2), question(3)...It will be greatly appreciated if someone help me to get this down.Jason
View Replies !
Select Statement Using Multi-list Box Values For WHERE IN SQL Clause
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox: Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly. <asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName"> <SelectParameters> <asp:Parameter Name="ListSelection" DefaultValue="1"/> </SelectParameters> </asp:SqlDataSource> The selListID column is type integer in the database. I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result: Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Dim Item As ListItem For Each Item In ListBox1.Items If Item.Selected Then If Label1.Text <> "" Then Label1.Text = Label1.Text + Item.Value + "," Else Label1.Text = Item.Value + "," End If End If Next Label1.Text = Label1.Text.TrimEnd(",") SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID) End Sub What am I doing wrong here? Thanks!
View Replies !
Return NULL Values In SELECT Statement With INNER JOIN ?
If I try to run the code below, and even one of the values in the INNER JOIN statements is NULL, the DataReader ends up with zero rows. What I need is to see the results even if one or more of INNER JOIN statements has a NULL value. For example, if I want info on asset# 2104, and there's no value in the DriverID field, I need the rest of the data to display and just have the lblDriverName by blank. Is that possible? <code> Sub BindSearchGrid() Dim searchUnitID As String Dim searchQuery As String searchUnitID = tbSearchUnitID.Text lblIDNum.Text = searchUnitID searchQuery = "SELECT * FROM Assets " & _ "INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _ "INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _ "INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _ "INNER JOIN AssetCategories ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _ "INNER JOIN Store ON Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '" & searchUnitID & "'" Dim myReader As SqlDataReader myReader = Data.queryDB(searchQuery) While myReader.Read If Not IsDBNull(myReader("Store Name")) Then lblStrID.Text = myReader("Store Name") If Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text = myReader("AssetCategory") If Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text = myReader("Condition Description") If Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text = myReader("DepartmentName") If Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit ID") If Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year") If Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make") If Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model") If Not IsDBNull(myReader("Mileage")) Then lblMile.Text = myReader("Mileage") If Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text = myReader("Vin Number") If Not IsDBNull(myReader("License Number")) Then lblLicNum.Text = myReader("License Number") If Not IsDBNull(myReader("Name")) Then lblDriverName.Text = myReader("Name") If Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text = myReader("DateAcquired") If Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text = myReader("DateSold") If Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text = myReader("PurchasePrice") If Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text = myReader("NextSchedMaint") If Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR") If Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW") If Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text = myReader("Crane Capacity") If Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text = myReader("Crane Certification") If Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text = myReader("Repair Cost") If Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text = myReader("Estimate Replacement") If Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text = myReader("SalvageValue") If Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text = myReader("CurrentValue") If Not IsDBNull(myReader("Comments")) Then lblCom.Text = myReader("Comments") If Not IsDBNull(myReader("Description")) Then lblDesc.Text = myReader("Description") End While End Sub</code>
View Replies !
Help: Why IN-Operator With Select-Statement It Doesn't Work? But With Given Values It Works
Hello to all, i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me. I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418: declare @IDM int; declare @IDO varchar(8000); set @IDM = 3418; set @IDO = '3430' select * from wtcomValidRelationships as A where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 )) but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select * from wtcomValidRelationships as A where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM)) I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks I use MS SQL 2005 Server Management Stadio Express Thanks a million and Best regards Sha
View Replies !
Query To Get Values From Datetime Column Into Comma Separated Text
Hi All I am working on a query to get all the datetime values in a column in a table into a comma separated text. eg. ColumnDate --------------------------- 2005-11-09 00:00:00.0002005-11-13 00:00:00.0002005-11-14 00:00:00.0002005-11-16 00:00:00.000 I wanted to get something like 2005-11-09, 2005-11-13, 2005-11-14, 2005-11-16 Have just started SQL and hence am getting confused in what I think should be a relatively simple query. Any help will be much appreciated. Thanks
View Replies !
Combine Fields And Text In Select Statement
Is it possible to combine fields and text in a select statement? In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this: SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe FROM GE_Data WHERE (DIST = @DIST) I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets. If this is possible, or there's a better way to do it, I'd love some more info. Thanks! -Mathminded
View Replies !
Appending Text To The Beginning Of A Select Statement.
I wish I had a better idea about how to describe this but here goes. I'm trying to display a list of majors/minors/graduate programs for a particular user. Now, I've gotten it display perfectly and was able to append (Graduate), (Major), (Minor) to each of the 3 different sections. My question is is it possible to add like rows that don't actually exist in table to the beginning of the select? For instance I want to add All and General before the rest of the select. I was also wondering where I should do a group by cuz I'd like to keep it in blocks of like graduates then majors and finally minors but currently it is just doing it alphabetically...Anyways below are the tables and at the end the current stored procedure i'm using, which works great...just not to the degree I'm aiming for. Code Snippet USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[GraduateDiscipline] Script Date: 03/30/2008 14:45:55 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[GraduateDiscipline]( [GraduateDisciplineID] [int] IDENTITY(0,1) NOT NULL, [DegreeID] [nchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [GraduateID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DisciplineName] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_GraduateDiscipline] PRIMARY KEY CLUSTERED ( [GraduateDisciplineID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[GraduateDiscipline] WITH CHECK ADD CONSTRAINT [FK_GraduateDiscipline_DegreeShortName] FOREIGN KEY([DegreeID]) REFERENCES [dbo].[Degree] ([DegreeID]) GO ALTER TABLE [dbo].[GraduateDiscipline] CHECK CONSTRAINT [FK_GraduateDiscipline_DegreeShortName] GO ALTER TABLE [dbo].[GraduateDiscipline] WITH CHECK ADD CONSTRAINT [FK_GraduateDiscipline_GraduateName] FOREIGN KEY([GraduateID]) REFERENCES [dbo].[Graduate] ([GraduateID]) GO ALTER TABLE [dbo].[GraduateDiscipline] CHECK CONSTRAINT [FK_GraduateDiscipline_GraduateName] USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Student_Graduates] Script Date: 03/30/2008 14:46:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Student_Graduates]( [GraduateDisciplineID] [int] NOT NULL, [StudentID] [int] NOT NULL, CONSTRAINT [PK_Student_Graduates] PRIMARY KEY CLUSTERED ( [GraduateDisciplineID] ASC, [StudentID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Student_Graduates] WITH CHECK ADD CONSTRAINT [FK_Student_Graduates_GraduateDisciplineID] FOREIGN KEY([GraduateDisciplineID]) REFERENCES [dbo].[GraduateDiscipline] ([GraduateDisciplineID]) GO ALTER TABLE [dbo].[Student_Graduates] CHECK CONSTRAINT [FK_Student_Graduates_GraduateDisciplineID] GO ALTER TABLE [dbo].[Student_Graduates] WITH CHECK ADD CONSTRAINT [FK_Student_Graduates_StudentID] FOREIGN KEY([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) GO ALTER TABLE [dbo].[Student_Graduates] CHECK CONSTRAINT [FK_Student_Graduates_StudentID] USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[MajorDiscipline] Script Date: 03/30/2008 14:46:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[MajorDiscipline]( [MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL, [DegreeID] [nchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MajorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DisciplineName] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED ( [MajorDisciplineID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[MajorDiscipline] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID]) REFERENCES [dbo].[Degree] ([DegreeID]) GO ALTER TABLE [dbo].[MajorDiscipline] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID] GO ALTER TABLE [dbo].[MajorDiscipline] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID]) REFERENCES [dbo].[Majors] ([MajorID]) GO ALTER TABLE [dbo].[MajorDiscipline] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID] USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Student_Majors] Script Date: 03/30/2008 14:46:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Student_Majors]( [MajorDisciplineID] [int] NOT NULL, [StudentID] [int] NOT NULL, CONSTRAINT [PK_Student_Majors] PRIMARY KEY CLUSTERED ( [MajorDisciplineID] ASC, [StudentID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Student_Majors] WITH CHECK ADD CONSTRAINT [FK_Student_Majors_MajorDisciplineID] FOREIGN KEY([MajorDisciplineID]) REFERENCES [dbo].[MajorDiscipline] ([MajorDisciplineID]) GO ALTER TABLE [dbo].[Student_Majors] CHECK CONSTRAINT [FK_Student_Majors_MajorDisciplineID] GO ALTER TABLE [dbo].[Student_Majors] WITH CHECK ADD CONSTRAINT [FK_Student_Majors_StudentID] FOREIGN KEY([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) GO ALTER TABLE [dbo].[Student_Majors] CHECK CONSTRAINT [FK_Student_Majors_StudentID] USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Minors] Script Date: 03/30/2008 14:46:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Minors]( [MinorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_Minors] PRIMARY KEY CLUSTERED ( [MinorID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Student_Minors] Script Date: 03/30/2008 14:46:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Student_Minors]( [MinorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [StudentID] [int] NOT NULL, CONSTRAINT [PK_Student_Minors] PRIMARY KEY CLUSTERED ( [MinorID] ASC, [StudentID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Student_Minors] WITH CHECK ADD CONSTRAINT [FK_Student_Minors_Minors] FOREIGN KEY([MinorID]) REFERENCES [dbo].[Minors] ([MinorID]) GO ALTER TABLE [dbo].[Student_Minors] CHECK CONSTRAINT [FK_Student_Minors_Minors] GO ALTER TABLE [dbo].[Student_Minors] WITH CHECK ADD CONSTRAINT [FK_Student_Minors_StudentID] FOREIGN KEY([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) GO ALTER TABLE [dbo].[Student_Minors] CHECK CONSTRAINT [FK_Student_Minors_StudentID] ALTER PROCEDURE [dbo].[Student_Deciplines] @studid int AS BEGIN SELECT RTRIM(gradDis.DisciplineName) + ' (Graduate)' FROM GraduateDiscipline gradDis, Student_Graduates sGrads WHERE gradDis.GraduateDisciplineID = sGrads.GraduateDisciplineID AND sGrads.StudentID = @studid UNION SELECT RTRIM(majDis.DisciplineName) + ' (Major)' FROM MajorDiscipline majDis, Student_Majors sMajors WHERE majDis.MajorDisciplineID = sMajors.MajorDisciplineID AND sMajors.StudentID = @studid UNION SELECT RTRIM(mins.MinorID) + ' (Minor)' FROM Minors mins, Student_Minors sMinors WHERE mins.MinorID = sMinors.MinorID AND sMinors.StudentID = @studid END Some sample data: GraduateDiscipline GraduateDisciplineID DegreeID GraduateID DisciplineName .... 0 M.B.A. 1 Master of Business Administration Student_Graduates GraduateDisciplineID StudentID 0 0 MajorDiscipline MajorDisciplineID DegreeID MajorID DisciplineName 1 B.S. Computer Science Computer Science Student_Majors MajorDisciplineID StudentID 1 0 Minors MinorID Description Criteria Computer Science blah blah blah blah Student_Minors MinorID StudentID Computer Science 0 Output I'd like for StudentID 0 All General Master of Business Administration (Graduate) Computer Science (Major) Computer Science (Minor) Hope someone can help!
View Replies !
Column With Select Statement
Hi, i have a doubt, can a column have the value of a select? I mean, i'm making a photo gallery and on the categories table i need to know how many photos i have, so i need to count in the table photos the ones associated with the id of the category, the problem is that i'm listing categories with a datalist, is there a way so that a column on the categories table have the result of the count? Thanks in advance, if you don't understood my question feel free to ask me again and i'll try to explain it better, i really need this.
View Replies !
Select Statment On Column That Can Contain Alpha And Numeric Values
Hi, I have a little bit of a problem I cannot seem to figure it out. Is it possible to write a Select statement that contains a WHERE column_name > desired_numeric_value The tricky part it that the column is of CHAR type and can contain numeric grades ranging from 0-100 or the letter I for Incomplete. My SQL was working perfect when this column contained only numbers as soon as a record with I was added I get the following error: Character to numeric conversion error This report will be used to find students who have failing grades. Thanks for any help!
View Replies !
Select Distinct Column Data With Other Values From The Table
I have a table 'wRelated' with the following columns [related_id] [int] [channel_id] [int] [mui] [varchar] [price_group_id] [type_id] [int] [related_mui] [varchar] (100) [date_started] [smalldatetime] [date_ended] [smalldatetime] [date_entered] [datetime] [deleted] [tinyint], [rank] [int] data in column [mui] is repeated as the table has more than one entries for the same [mui], The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui] The recordset expected should be something like this. [mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004' null,rm789,'ar','12-1-2003',26-2-2004' null,rm999,'xy','14-12-2002',12-2-2004' m777,rm889,'pr','12-12-2004',12-12-2004' null,rm785,'yy','1-10-2002',12-12-2004' m888,rm345,'pr','2-8-2003',12-12-2004' null,rm345,'tt','30-7-2002',12-12-2004' I have tried Unions and temporary table inserts.
View Replies !
Select Comma Separated Values From Single Column
Hi, I have a table -- Table1. It has two columns -- Name and Alpha. Alpha has comma separated values like -- (A,B,C,D,E,F), (E,F), (D,E,F), (F), (A,B,C). I need to pick the values of column -- Name , where in values of Alpha is less than or equal to 'D'. I tried <=, but got only values less than 'D', but was not able to get equal to 'D'. Any suggestions??
View Replies !
Possible To Parse A Column In A Select Statement?
I have a column called SEGMENTED_BLOCK sample data:X,X,XXX,XX,XX,TYZC123456,X,X,TOYZ654321,1234,777777I need to do something that has the effect ofSELECT(stuff before first comma) as FIRST_ITEM,(stuff after first comma, but before second) as NEXT_ITEM,(stuff after second comma but before third(if any)) as THIRD_ITEMFROM SEGMENT_XREFWHERE LOOKUP_ITEM = 12345ORDER BY FIRST_ITEMFIRST_ITEM is pretty easy, but it gets uglier fast.My attempts are horrendously ugly nested checkindex and substring statements.Is there an easier way?
View Replies !
Return SP In A SELECT Statement Column
I have a stored procedure which contains a complex scripting that is not an option to rewrite as a single SELECT statement. I want the following output: CatID | CatTitle | CatTree 001 | News | exec sp_DisplayTree(@CatID) My code I tried doesn't work: SELECT C.CatID As CatID, C.CatTitle As CatTitle, CatTree = (exec sp_DisplayTree C.CatID) FROM Cats As C WITH (nolock) I cannot find a solution to my solution, please help...
View Replies !
|
TRACKER
