Single SQL Statement Solution….
What is the single SQL statement to truncate the blank space on either side of data.
Ex.
Table1 has Name as column.
I have records filled with blank space on both side for Name field.
With one query I want to correct (truncate the leading and trailing space) the data.
How?
SQL Server 2005 SP2.
Thank you,
Smith
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Need A Single Sql Statement
How can I do it by using two subqueries the second of them to be aggregate and have two left joins from the first to the second?? e.g. How can I left join these two queries with the joinfield1,joinfield2 fields?? 1st query Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value 2nd query Select sum(agfield1) As f1, sum(agfield2) As f2, joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4 where agfield5=Value Group By joinfield1,joinfield2
View Replies !
View Related
Is It Possible In Single SQL Statement
Does anyone know how should I write the sql for getting the following result? Original Table like below. ------------------------------- [WorkDay][AgentCode] 06/12/01 3 06/12/02 2 06/12/02 3 06/12/03 2 06/12/03 3 ------------------------------- Curernt SQL: When I put an "agentcode=2" in 'WHERE' clause, the result does not have '06/12/01' row. Example, SELECT DISTINCT WorkDay, AgentCode FROM MasterScheduleTransaction WHERE AgentCode=2 ------------------------------- [WorkDay][AgentCode] 06/12/02 2 06/12/03 2 ------------------------------- I would like to know the agent is in the specified date. The expected result like below. ------------------------------- [WorkDay][AgentCode] 06/12/01 NULL 06/12/02 2 06/12/03 2 ------------------------------- Please help its urgent
View Replies !
View Related
Single Select Statement
Hi, i have an input parameter @PageloadYN Bit Null if @PageloadYN = 1 then Select top 500 Records from the Table if @PageloadYN = 0 then Select * from the Table i tried like this Select top 500.* From Table where @PageloadYN = 1 Select * From Table where @PageloadYN = 0 Is there any way to get details in Single select statement only.
View Replies !
View Related
Single Statement For Insert Or Update
In VB6 using MDAC 2.8 I could do a single select statement that would act as either an Insert or an update. Is there a way to do this in ADO.net? My old VB6 code Dim dbData As New ADODB.Connection Dim rs1 As New ADODB.Recordset Dim strParm As String Dim strCusNo As String ' strParm = "Provider=SQLOLEDB; Data Source=SQL2000; Initial Catalog=DATA_01; User ID=UserName; Password=password" dbData.Open strParm ' strParm = "Select CusNo from CusFil Where CusNo = '" & strCusNo & "'" rs1.Open strParm, dbData, adOpenStatic, adLockOptimistic, adCmdText If rs1.BOF And rs1.EOF Then rs1.AddNew Else End If With rs1 !CusNo = strCusNo .Update End With rs1.Close ' Set rs1 = Nothing dbData.Close Set dbData = Nothing Is there an ADO.Net equivalent? thanks,
View Replies !
View Related
Help With Creating SQL Statement To Get Data From Single Table...
Hi, I'm having some difficulty creating the SQL Statement for getting some data from a table: I have the following table of data __User___Votes___Month __A_______14______2__A_______12______3__A_______17______4__A_______11______5 __B_______19______2__B_______12______3__B_______15______4 I want to beable to pull out the total number of votes a user has had over a period of months. eg Total up each users users votes for months 4 and 5 that would give: __User____TotalVotes ___A________28___B________15 An added complecation is that user B does not have any data for month 5 Any help or pointers would be fanstatic Many thanks
View Replies !
View Related
Single Statement To Delete Record Into More Tables
Hi , I little question for you ... is it possibile to write a SQL statement to delete records in several tables at the same time? For example if I've two tables involved by join DELETE <...> from Customers A INNER JOIN CustomerProperties B ON A.CustomerID=B.CustomerID I Must use two statement to remove records from both the tables? Thx
View Replies !
View Related
To Alter Multiple Column With Single Statement
It is possible to alter multiple columns within a single alter table statement? I have got the following URL that tells it is not possible to alter multiple columns within in signle alert table statement. http://www.blogcoward.com/archive/2005/05/09/234.aspx[^] Does anyone know about that? Thanks, Mushq
View Replies !
View Related
Can Someone Clarify Why Only A Single-statement Can Be Executed In A Command?
I'm evaluating SQL 2005 Everywhere Edition for use by our desktop application. I'm a traditional SQL Server developer and I rely heavily on stored-procedures to encapsulate basic data manipulations across multiple tables and inside multi-statement transactions. I was excited to see an in-process version of SQL released and my thought was "this is great... now I can ditch the tediousness of individual OLEDB/.NET commands, and write batches of T-SQL and just focus on the data manipulations". But, alas, it seems I cannot. Why is SQL Everywhere Edition limited to executing a single SQL statement at a time? For example, my application would like to update mutlipe rows in one table, delete multiple rows from another, and insert multiple rows into a third. I can do that with 3 T-SQL statements in a single small batch in a very readable way with full blown SQL Server. (and I can put that batch in a stored procedure and re-use it efficiently later.) If I contemplate how to do that with OLEDB and the single statement limitation of SQL Everywhere, it's a lot more code and a lot less appealing/maintainable. I want as much of my app to be using declarative code and as little as possible tied up in tedious OLEDB calls. Is this not possible with SQL Everywhere Edition?
View Replies !
View Related
Update Multiple Varbinary Records With Single Sql Statement
I am renovating an existing application and am converting the existing passwords into hashed values using SHA1. I know how to compute the hashed values as a byte array for each record. What I don't know how to do easily is update all of the records i a single call to the database. Normally, I would just do the following:UPDATE HashedPassword = someValue WHERE UserID = 101; UPDATE HashedPassword = someOtherValue WHERE UserID = 102; ... What I don't know is what someValue and someOtherValue should be. How do I convert my byte array into string representation that SQL will accept? I usually execute multiple statements using Dim oCmd as New SqlCommand(sSQL, MyConn) and then call oCmd.ExecuteNonQuery(). Alternatively, I found the following code that uses the byte array directly but only shows a single statement. How could I use it to execute multiple statements as shown above?'FROM http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx '2. Create a command object for the query Dim strSQL as String = _ "INSERT INTO UserAccount(Username,Password) " & _ "VALUES(@Username, @Password)" Dim objCmd as New SqlCommand(strSQL, objConn) '3. Create parameters Dim paramUsername as SqlParameter paramUsername = New SqlParameter("@Username", SqlDbType.VarChar, 25) paramUsername.Value = txtUsername.Text objCmd.Parameters.Add(paramUsername) Dim paramPwd as SqlParameter paramPwd = New SqlParameter("@Password", SqlDbType.Binary, 16) paramPwd.Value = hashedBytes objCmd.Parameters.Add(paramPwd) 'Insert the records into the database objConn.Open() objCmd.ExecuteNonQuery() objConn.Close()
View Replies !
View Related
How To Identify Special Characters With A Single Case Statement
hi All :I have an insert statement which readsINSERT Into TableA (Col1)SELECT CASE WHEN Col1 LIKE '%[a-z]%' THEN 999999 ELSE Col1 END ASCol1,FROM TableB**********************TableA. Col1 has a datatype of Int.TableB. Col1 has a datatype of varchar.My insert is failing as it is trying to insert special character,identifying a in the input columnHow can i write a similar case statement to filter out specialcharacters and replace with 99999Also can you refer me to a place where i can have a list of specialcharacters and if need be write mulitple case statements to filterthem out.thanksRS
View Replies !
View Related
Can A Single Insert Statement Will Update Multiple Tables
Hai Guys: I have a situation where i need to insert a value called StudentID to several tables with one insert statement. Can you please inform me the SQL statement to acheive the above mentioned task. (by the way iam using SQL Server 2000, i dont need triggers / procedures as they are too tricky and hard to use). Thanks / Regards, Dotnet Geek.
View Replies !
View Related
How To Form A Single SQL Statement In A Datalist For A Challenging And Convoluted Problem ?
Hello, I'm really stuck at trying to figure out how to write out the proper SQL statement for my problem. I'm relatively new to SQL, so this may not be so tough for some of you. Basically, a user logs in (I'm using the default membership provider) and goes to his INBOX to see his list of messages sent to him. The list is presented to him via a datalist. Each item of the datalist contains a table of 2 columns and 1 row as pictured below. The first box contains the user photo and user name of the person who SENT him the message (not the logged in user). The second box contains the subject title of the message. FROM | SUBJECT | User Photo | || | Subject || User Name | | Here is the list of the relevant 4 tables of my database and the relevant fields..... aspnet_Users tableUserId (used to link to Member table)UserName Member tablememberId (int - primary key)UserId (guid - used to link to aspnet_Users table)primaryPhotoId (int - used to link to Photo table) Photo tablephotoId (int - primary key)photoUrl (string - path to file on local drive) Message tablemessageId (int - primary key)fromMember (int - connects with memberId from Member table)toMember (int - connects with memberId from Member table)subject (varchar(max)) So basically, from a simplistic high level point of view, the datalist is going to list all of the messages where Message.toMember = the logged in user. The senders will be determined by the Member.fromMember fields. Intuitive enough so far, I guess. This is the SQL statement I have so far..... SELECT aspnet_Users.UserName, Message.subjectFROM aspnet_Users, Member, MessageWHERE aspnet_Users.UserName = Profile.UserName AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember Note that I'm grabbing the logged in user info from Profile.UserName. So far, this SQL statement should make the datalist crank out all messages that were sent to the logged in user. HOWEVER, how would I modify this so that the datalist generates the username of the sender, NOT the receiver (aka person who logged in)? Do you see the core of my dilemna here? I'm trying to get a resultset based on the Message.toMember (the logged in user), but also want data on the sender (the Message.fromMember so I can use the username and the photo of the SENDER, not the person logged in aka the RECEIVER). Currently, the aspnet_Users in the SELECT statement gets the username of the logged in person, not the sender. And once we solve the issue of retrieving the sender's username, I also have to get his MAIN photo (I say "main" since a user can have multiple photos and the main one is determined by the value in a given member's primaryPhotoId field of the Member table) ?? I'm a newbie to ASP.NET and to databases in general so this may not be as tough for most of you and perhaps you're laughing at the simplicity hehe. The SQL statement so far asks to retrieve information based on the logged in user. But how do I also tell it to now go grab the Message.fromMember data, go back to the Member table to A)get the username after going back to the aspnet_Users table and B) to get the Member.primaryPhotoId, and then finally to the Photo table where the Photo.photoUrl string value is obtained..... and still hang on to the results I have up until now? And since I'm using the provided datalist control, I think I need to get all the results I need with just one SQL statement. This is indeed very very complicated for me lol. This problem has been giving me migraines this whole weekend. Has anyone been through such a problem before? Any help would be greatly appreciated - thanks in advance.
View Replies !
View Related
Combine Data In Single Row From Single Table
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date Code: Employee No Date SALARY 1 10/30/2006 500 1 11/30/2006 1000 2 10/25/2006 800 3 10/26/2006 900 4 10/28/2006 1000 4 11/01/2006 8000 Should Appear Code: EmployeeNo Date1 OLDSALARY Date2 NEWSALARY 1 10/30/2006 500 11/30/2006 1000 2 10/25/2006 800 3 10/26/2006 900 4 10/28/2006 1000 11/01/2006 800 PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT. THANKS IN ADVANCE
View Replies !
View Related
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View Replies !
View Related
Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View Replies !
View Related
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
View Related
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
View Related
Compiler Is Not Recognizing My Using Statement For SglConnection Statement
I am using ASP.NET 2.0, and am attempting to write some code to connect to the database and query a data table. The compiler is not recognizing my SqlConnection statement. It does recognize other commands. And just to make sure, I created other sql objects such as ObjectDataSource and SqlDataSource. The compiler does not find a problem with that code. Basically the compiler is telling me that I am missing a "using" directive. The compiler is wrong though, because I am including the statement "usingSystemData" Can someone please take a look at my code below and to see if you notice what the problem might be? Note that I numbered the lines of code below. Note that I also tried putting lines 3 trhough 6 before line 2(The page directive) but that did not fix the problem The compiler still gives me the same compiler message. Compilation Error Description: An error occurred during the compilation of a resource required to service this request.Please review the following specific error details and modify your source code appropriately. Compiler Error Message: CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)Source Error: Line 21: SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;"); 1 <asp:sqldatasource runat="server"></asp:sqldatasource> 2 <%@ Page Language="C#"%> 3 using System; 4 using System.Data; 5 using System.Collections; 6 using System.Data.SqlClient; 7 8 <script runat=server> 9 10 protected void Page_Load(object o, EventArgs e) 11 { 12 ObjectDataSource dsa; // This works no problems from the compiler here 13 SqlDataSource ds; // This works no problems from the compiler 14 15 if (IsPostBack) 16 { 17 if (AuthenticateUser(txtUsername.Text,txtPassword.Text)) 18 { 19 instructions.Text = "Congratulations, your authenticated!"; 20 instructions.ForeColor = System.Drawing.Color.Red; 21 SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;"); 22 String sqlStmt = "Select UserName from LogIn where UserName='" + txtUsername.Text + "' and password='" + sHashedPassword + "'"; 23 } 24 else 25 { 26 instructions.Text = "Please try again!"; 27 instructions.ForeColor = System.Drawing.Color.Red; 28 } 29 } 30 31 } 32 33 bool AuthenticateUser(string username, string password) 34 { 35 // Authentication code goes here 36 37 }
View Replies !
View Related
Case Statement Error In An Insert Statement
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it. Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB ); The error I'm getting is: Incorrect syntax near '='. I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View Replies !
View Related
Help With Delete Statement/converting This Select Statement.
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement: SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View Replies !
View Related
How To Show Records Using Sql Case Statement Or If Else Statement
i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows as per data in MS Sql my tables are as follows 1)material fields are -- material_id,project_type,project_id,qty, -- 2)AB_Corporate_project fields are-- ab_crp_id,custname,contract_no,field_no 3)Other_project fields are -- other_proj_id,other_custname,po for ex : vales in table's are AB_Corporate_project ===================== ab_crp_id custname contract_no field_no 1 abc 234 66 2 xyz 33 20 Other_project ============ other_proj_id other_custname po 1 xxcx 111 2 dsd 222 material ========= material_id project_type project_id qty 1 AB Corporate 1 3 2 Other Project 2 7 i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project sample query i write :-- select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id, ab.custname ,op.other_proj_id,op.other_custname,op. po case if m.project_type = 'AB Corporate' then select * from AB_Corporate_project where ab.ab_crp_id = m.project_id else if m.project_type = 'Other Project' then select * from Other_project where op.other_proj_id=m.project_id end from material m,AB_Corporate_project ab,Other_project op but this query not work,also it gives errors i want sql query to show data as follows material_id project_type project_id custname other_custname qty 1 AB Corporate 1 abc -- 3 2 Other Project 2 -- dsd 7 so plz help me how can i write sql query for to show the output plz send a sql query
View Replies !
View Related
Using Select Statement Result In If Statement Please Help
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
View Replies !
View Related
If STATEMENT Within Select Statement Syntax
Hi, I am a newbie to this site and hope someone can help.... I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is: if(TL_flag= '1', "yes") as [Trial Leave] it is coming up with an error.... I can use Select case but I should not need to as this should work? Any ideas?
View Replies !
View Related
How To Read SQL Statement In T-SQL Statement Task?
hi all, after convert from DTS to SSIS, how can i open the SQL statement? because i only saw a line in the properties of the task. i open the DTSX in vs2005, but i can open to view the SQL statement. in sql 2000 just double click on the SQL Task, then will popup a dialog form to show the SQL task. please help. thanks a lot
View Replies !
View Related
Need A Single Query
Hi, I have received a text file in the following format: MthYear Customer Quantity Price Total Apr2003 Allan 100 5 500 --------- Austin 25 2 50 --------- George 1500 1 1500 ---------- Jessy 200 2 400 Apr2004 Jerry 600 3 1800 --------- Stella 250 2 500 June2005 XXXX 50 5 250 I am exporting this text file in Sql Server database table. After exporting I need to Update the MtnYear field marked as ------- to appropriate year. ie The MthYear field for Austin, George,Jessy should be updated with Apr2003, The MthYear field for Stella should be updated with Apr2004 iiily, for other records. Let me inform if this is possible with the help of a single query or any better way of doing it. The records that i need to update is more than 2 lakhs. I am sorry if u feel this questionis not apropriate.
View Replies !
View Related
Get Only Single Row Results Per Id?
Hi, is it possible to make an sql query that has an Outer Join but return only one row of results max per id. For example i have an Articles table, and a PicturesForArticles table. The Articles table has an id field(aid), a title field(aTitle) and a content field(aContent). And the PicturesForArticles table has an id field(pid), a PicPath filed and a field linking it to the articles table(aid) Obviously the PicturesForArticles field stores pictures for the articles, and article can have a multiple number of pictures, or no pictures at all. So i want to make a query that will return all of the Articles fields and a picture for each article. Even if the article has many pictures i only want to get a single row for each aid(Articles Id), and if there are no pictures for that article the picture fields will be null. Is there any way to do this, to only return on row of results for each aid? Thanks
View Replies !
View Related
Getting A Single Value From SqlDataSource
Hi, I have a SqlDataSource(named SQLDS1) which retrieves 4 value from database(ProductName,ProductCost,ProductID). I Have a DropDownList(DDL1) control and its DataSource SQLDS1. DDL1 Selected data field to display is ProductNameDDL1 Selected data field to value is ProductCost I did all this in Visual Part without any line of code. But in the code behind , When i select an item from DDL1 i need its ProductName,ProductCost and Also ProductID. It is simple to get first two. But how can i get the ProductID. Is there anyway to get ProductID from SQLDS1. Happy Coding
View Replies !
View Related
Return Single Value
Hello, I created a stored procedure which selects a value according to ContentId. I know that will be only one value returned or none. So if a record is found I want to return the string contained in ContentHtml. Else I want to return the string "NotFound" Could somebody help me out with this? Here is my present stored procedure: -- Specifies the SQL-92 equals compliant behavior SET ANSI_NULLS ON GO -- Specifies the SQL-92 quotation mark rules SET QUOTED_IDENTIFIER ON GO -- Alter procedure ALTER PROCEDURE [dbo].[by27_Content_GetContent] -- Define the procedure parameters @ContentName NVARCHAR(100), @ContentCulture NVARCHAR(5) AS -- Prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; -- Declare and define ContentId DECLARE @ContentId UNIQUEIDENTIFIER; SELECT @ContentId = ContentId FROM dbo.by27_Content WHERE ContentName = @ContentName -- Check if ContentId is Not Null IF @ContentId IS NOT NULL BEGIN -- Select localized content from by27_ContentLocalized SELECT dbo.by27_ContentLocalized.ContentHtml FROM dbo.by27_Content INNER JOIN dbo.by27_ContentLocalized ON dbo.by27_Content.ContentId = dbo.by27_ContentLocalized.ContentId WHERE (dbo.by27_ContentLocalized.ContentCulture = @ContentCulture AND dbo.by27_Content.ContentName = @ContentName); END -- Create procedure GO Thanks, Miguel
View Replies !
View Related
Single Or Multiple Sp ???
greetings,i was wondering is it better to have multiple small stored procedures or one large store procedure ???? example : 100 parameters that needs to be inserted or delete ..into/from a table.............is it better to break it up into multiple small store proc or have 1 large store proc....thanks...............
View Replies !
View Related
The Single OS Nature Of MS SQL
Pros-------* Can optimize the hell out of the database because it does not have toaccomodate different OSes* Code is probably cleaner - not littered with 80 million #ifdefs* No need to write the code to deal with various filesystem conventionsCons--------* Most cheap webhosts are unix-based (I think). You can use Oracle as aprofessional and still do your personal home hacking on oracleregardless of whether personal hosting is done on windows or unix. Notso with MS SQL.
View Replies !
View Related
Single Quote
How do I insert a single quote ' into a table? For example:Insert mytable values (1,''')I get an error message.Any ideas? The server does not recognize double quote (why? is thistsql, ansi?), otherwise I could have written it as:Insert mytable values (1,"'")Thanks.
View Replies !
View Related
Can There Be A Single Index
Can we make a Index on 2 or more tablesTable a:Col1 intcol2 varcharTable b:Col1 intcol2 varcharcan you have a single index for two tables a and b on the column Col1.Is this possible in SQL-Server.As far as i know you can make an index only on one Table.can the index be shared by two tables?index y which is created is shared bytable a , table b and table cI like the idea of a union an putting it in a veiw and having a indexbut i dont want to make 100 views for a special index.first of all i want to know wether the index is only on one table can beon multiple tables.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
View Related
Single Query
i hav a table of the form table EMP ( EMPNAME varchar(50) ) and the data in the table is record #1 abc record #2 abc record #3 abc record #4 abc record #5 abc record #6 abc can i use a single query so as to retain a single record in the table and remove the rest ?
View Replies !
View Related
BCP A Single Table
I've managed to BCP in a single table form a backup DAT file into a database, but it took WAY to long (1 hour+ on a meaty server) and I can't understand why. The table only had a few rows of data and only had a few small dependancy tables. The table has a primary key, hence an index so the BCP becomes a logged operation but it still should not take this long. Here is the BCP command line that I ran :- bcp <dbname>.dbo.<tablename> in <DAT filename> /U sa /P /S <server> /m 1 /n Could anyone please shed some light on this. Is there anyway of 'fast' bcp'ing this table into the database overwriting the existing one. Failing that, is there any way of scripting the transfer of a table from one server to another. Many thanks. Dave
View Replies !
View Related
Single Quotes And Sql
How to deal with single quotes in the contains predicate ? I explain : i have to search strings like this : contains(title,'"ennemi"' AND '"d'etat"') I know that my syntax is wrong but i don't know how to correct it Could someone help me ? TIA Isabelle
View Replies !
View Related
Bcp Single CPU Vs Multiple CPU
I'm doing a BCP of a large table 37 million rows. On a single CPU server, SQL 7, sp 3, with 512 meg of RAM, this job runs in about 3 hours. On a 8 way server with 4 Gig of RAM, SQL 7 Enterprise, this job runs 12 hours and is only a third done. The single CPU machine is running one RAID 5 set while the 8 way server is running 4 RAID 5 sets with the database spread out over two of them. Is there something obvious that a single CPU box would run this much faster?
View Replies !
View Related
Selecting A Single Row
I have a query that returns a certain amount of results. How do I select the last row??? I know I can do the top row by doing (top 1). Basically I need the complete opposite. Please help... I looked around and couldn't find anything on it. thanks in advance
View Replies !
View Related
Can This Be Done In A Single Query?
Hello. I am new to this forum and pretty new to SQL. My background is in firmware development but due to the smallness of our company I have inherited a VB6/SQL app in which I need to fix a problem. Given the following table: C1 | C2 | C3 ------------ 01 | 03 | 05 01 | 04 | 10 02 | 03 | 05 02 | 04 | 20 I need to select all rows for each C1 value where C2 values are equal and C3 values are not equal. In this case, rows 2 and 4. There are many C1 values in the actual table I need to query, but the query will be comparing only two C1 values each execution and these two values are passed into the app by the user, but usually more than two C2 values are in the table for each C1 value. Is this possible to do with a single query? If so, I have been unable to get the syntax correct. Any insight is appreciated. John F.
View Replies !
View Related
Single Query
Hello I have a sql query that shows the top N numbers of records and also wanted to show the total numbers of this records. Code: . . . rs_1.Open "SELECT top 5 msg_id, message, topic_id, last_post_by, id, sdate FROM forum_table",conn,1,3 rs_2.Open "SELECT msg_id, message, topic_id, last_post_by, id, sdate FROM forum_table",conn,1,3 “ Top number to show: <%=rs_1.recordcount%> Total number of records in database: <%=rs_2.recordcount%> . . . As you can see, it is only on the same database that I am querying. How do I make a single sql query out of this?
View Replies !
View Related
Single Statment
iam having two statement for updating one table. just i need to club into one statment.can any one tell how this can be achieved. first statement: =================== --Updating Customer code update stg set stg.customer_code=dtl.cgd_customer_code from Subsidiary_Ageing_tb stg(nolock) inner join fin_ods..cust_group_dtl dtl(nolock) on dtl.cgd_cust_group_code=stg.customer_grp second statment: =================== --updating customer name update stg set stg.customer_name=dtl.clo_cust_name from Subsidiary_Ageing_tb stg(nolock) inner join fin_ods..cust_lo_info dtl(nolock) on dtl.clo_cust_code=stg.customer_code
View Replies !
View Related
|
TRACKER
