This is very embarrasing, but I can't find this anywhere (An online search turned up a ridiculous number of results, and thus far [as of page 6], I haven't found anything that answers my question.
I would like to write a SELECT statement, WHERE a given field does NOT equal "". I know that in C# it would be !=, but this doesn't appear to work. I tried a couple of other things, but did not have any luck.
This is a very silly question, but could someone please tell me what I use in a SELECT statement to signify "Does NOT Equal"?
Thank you very much. I really appreciate everyones help.
in SQL query analyzer i have this skeleton test query:
DECLARE @nLen int DECLARE @strData varchar(200) DECLARE @strDelim varchar(50) DECLARE @nPos int
SELECT OwnersName1 = (IF len(OwnersName) > 0 OwnersName -- IF dbo.InString(OwnersName,'&',1) > 0 'yes' --if dbo.InString(OwnersName,' AND ',1) > 0 'yes' --if dbo.InString(OwnersName,', ',2) > 0 'yes' --ELSE OwnersName ) , OwnersName2 = OwnersName --@strDelim as Delim FROM dbo.TateWake WHERE OwnersName>'9'
i get this error: Server: Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'IF'. Server: Msg 170, Level 15, State 1, Line 8 Line 8: Incorrect syntax near 'OwnersName'.
if i replace IF len(OwnersName) > 0 OwnersName with: len(OwnersName) there is no error.
this is my 3rd attempt at posting this. other attempts did not post for some reason. now i'm home from work and so i can't cut and paste. this is from memory.
in query analyzer, i made a ridiculously simple query which returns a syntax error that i can't resolve:
SELECT name1 = ( IF len(name) > 0 len(name) ), name2 = name FROM ownertable
this gives an error something like: syntax error near IF on line 3
when i remove the IF len(name) > 0 the expression is evaluated as expected with no error. what is going on?
I must apologize for the simplification, but my posts kept going to the ether for some reason. I have had other dba's look at it and no one knows why this is happening in my office. nor can i understand why my posts don't stick. i even got a message from the forum app that i posted a duplicate message!
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
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
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
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.
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.
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
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
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
I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).
Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.
SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees, ( SELECT COUNT(gifts.donor_id) AS Count2 FROM gifts WHERE gifts.donor_id = ed.id_number ) subquery2
Some categories have sub-categories. Therefore, some entries will have null for parentID while others will point to another category.
I need to select all of the categories, but have them returned in the correct parent/child order (parents BEFORE children). I can't seem to get this to work correctly. Is this not possible or am I just being stupid?? :)
I have two tables: 1) Table that holds all available ports. 2) Table that holds users for each port.
There may be times where one user is getting more than one port at a time.
I've built up an ASP .NET page that will display each user its port/s in one table. On another table I want to display all the other available ports which the user doesn't posses and can buy to own.
My problem is where I try to build up the query. I just can't get all the other ports in normal display.
For example, this is what I need:
Ports table: 1/1 1/2 1/3 1/4
Users table: User A , 1/1 User A , 1/2
ASP page display:
------------ User A Holds: 1/1 1/2 ------------
Available: 1/3 1/4 ------------
Of course the Available option is derived from the User Holds query, and just getting the opposit not equal ports, but I just can't get it ! I've tried all kinds of Joins and nesting SELECT queries with no luck.
here is my problem and i am probably just stupid. i need the total amount Transfered minus the Received per barcode. so here i would need to see a total of 3.
I've getting the error message at the time ntbackup starts the backup of the server:
Event Type: Error Event Source: SQLWRITER Event Category: None Event ID: 24579 Date: 22-Aug-07 Time: 20:00:26 User: N/A Computer: DBS Description: Sqllib error: Database DYNAMICS of SQL server instance XXX is stored on multiple volumes, only some of which are being shadowed. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: 53 51 4c 53 4e 41 50 43 SQLSNAPC 0008: 33 36 31 00 00 00 00 00 361..... 0010: 53 51 4c 53 4e 41 50 43 SQLSNAPC 0018: 32 34 36 00 00 00 00 00 246.....
Where is the problem? How to fix this? Does this mean that I would not be able to restore server in the case of failure?
SQL - version SQL Server 9.0.3054 (2005 SP2) OS - 5.2.3790 (W2k3 R2 SP2)
I am trying to insert form data...three form sections into three different tables....i can insert into one table just fine....is this not possible ...or what? If so can someone please send me a small sample... thank you ...by the way i am using visual studio...vb asp.net 2.0 with ajax. Thanks in advance!
I haven't tested it, because at the moment I can't access my SQL database I know this is probably a stupid question, but...How can I test for DBNull when I'm using SqlDataReader.Item(string)? I use to check it using: if(!reader.IsDBNull(2)) ...But I have found that can not count on a particular ordinal reference because some of the stored procedures select the columns in different orders. can I do something like: if(reader["Column1"] != DBNull.Value)or with the reader["Column1"] throw an error because it is not found?? I too hate when people ask questions that they could test themselves...but I can't really test it at the moment. Any input would be greatly appreciated.
I'm trying to backup a database onto a different server within the same SQL Server Group, but the Enterprise Manager only offers the drives on the local server as destination options. Do I have something configured incorrectly or is there a way to do this manually? Thanks in advance.
