How Do I Exclude Certain Unwanted Data From A Query Result
We've got a basic query that pulls a list of some parts out of our inventory database:
SELECT p21_view_inv_mast.item_id, p21_view_inv_mast.item_desc, p21_view_inv_loc.gl_account_no
FROM dbo.p21_view_inv_loc p21_view_inv_loc, dbo.p21_view_inv_mast p21_view_inv_mast
WHERE p21_view_inv_loc.inv_mast_uid = p21_view_inv_mast.inv_mast_uid
This returns a lot of info over three columns: "item_id" "item_desc" and "gl_account_no"
What I want to do is remove any entries where the "gl_account_no" shows as 011500000
I am by no means a TSQL person, I mostly run the canned queries that our vendor gives us so any help with this would be greatly appreciated.
This a microsoft SQL 2000 server.
I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID.
Here is my problem:
Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.
I know how to get the user table names from the DB as follows:
WHERE xtype = 'U' and name like 'Elect_Sub%Daily'
What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".
delete from tableName where Date_Stamp = '2008-3-18'
This should be trivial but I'm ignorant so I'm hoping someone can assist. I can find lots of code snippets for removing duplicates, but I can't find a variation that works for my case.
Overview: I'm doing a name look-up, combining first & last names and if it matches against an employee table, getting the employeeID for that person. I need to only return the unique matches, and exclude any names that happen more than once in the employee table.
I have 3 possible results from my select (snippet below):
1) Single match
2) No match
3) Multiple matches (2 or more people, same first and last name)
My simple code below does exactly what I need for case 1 & 2. If there is a single match on the name, it's returns the res_ID and emp_ID as expected, if no match, no record and that works for me.
The problem I can't solve is if there are two John Smith employees, both records are returned, which is what my query requests, but not what I need. I want ONLY return data which has ONE exclusive match, and exclude all others.
Code for case 1 & 2, doesn't handle 3rd case:
Select distinct ot.res_ID, e.emp_ID
From employee e, @OutputTable ot
Where (e.fName + ' ' + e.lName) = ot.empFullNameText
Employee table has first & list names, plus emp_ID
@OutputTable is a table variable from my proc which has "John Smith" type text names as one string.
This has to be simple, but I'm over my head on this one. All ideas, reference links or other assistance appreciated.
I have 16,000 rows in tblClient and 3000 rows in NewTable.SELECT tblClient.*FROM tblClient INNERJOIN [New Table] ON tblClient.NoDossier <> [New Table].NoDossierif I use = (equal) instead of <> (exclude), the query returns 3000 rowswhen I use <> it returns 160000 rows,if I try group by, the query bugswhat is my problem
Hi. hope someone can help me with this thing.
I have a list of numbers that I get from a query to my database. how could I make the query so that I can exclude a couple of numbers. For example the numbers 1234 and 8888. My list is in the range of 1 - 99999.
I have a table with five entries. For each row read in that table I want to pass the value
to a data flow task to be used to set the filename in a flat file connection.
I'm new to MS SQL server. Is there a select statement that will include fields that are not null?
I am selecting the following fields
I want to select both fields, like this
Select AVG(AVG_Back), AVG(AVG_Yield)
WHERE Date Between '3/1/2008' AND '3/31/2008'
I want to limit the AVG_Back field to exclude all values of 0. So only average AVG_Back if the value > 0. What is the best way to accomplish this? I can't just put it in the where clause or the AVG_Yield will be excluded too.
I have a table called MasterSkillList which is a list of skills and attributes, eg: [Appraise, INT], [Bluff, CHA] etc
I have a table called Classes, which is a list of all classes available (and some details which are irrelevant), eg: [Fighter], [Assassin] etc.
I also have a table called ClassSkills which holds a list of classes and their applicable skills, eg: [Assassin, Bluff], [Assassin, Open Lock], [Fighter, Appraise], [Fighter, Bluff] etc.
What I have is a gridview which shows all my classes from the class table. i want to be able to select a class on that gridview and create a checkbox list of all available skills that are NOT allready associated with that class. Eg: assassin has bluff and open lock, so those two skills shouldn't appear on my checkbox list. So i want to show all the skill from the master skills list, excluding all the skills the selected class allready has.
Alternatively, It would be better if there was a way to display all the skills in existance on my checkboxlist and the ones that class allready has to be checked. Any suggestions?
Here's the query I have:
SELECT MasterSkillsList.SkillFROM ClassSkills INNER JOIN MasterSkillsList ON ClassSkills.Skill = MasterSkillsList.SkillWHERE (MasterSkillsList.Skill <> ClassSkill.Skill)
I just added the following sql query, but when i run it i get no results even though it should show everything except 2 skills. Have I written it wrong?
SELECT SkillFROM MasterSkillsListWHERE (NOT EXISTS (SELECT Skill FROM ClassSkills WHERE (ClassName = @ClassName)))
I apply data mining query transformation to data i got from conditional split (Table Employees where salary=null).
I predicted the value of column(Salary) and i want to update column Salary(table Employees) with the predicted value, only for the rows that there is a prediction.
Any suggestions how can i update the table with the result of Data Mining Query Transformation?
I have a cube that is showing measures that don't exist. Let me give an example. This example will include 3 dimensions, product, location, and time. The fact table measure will be sales.
Here are the distinct values if you were to write a sql query against the dimensionl model that feeds the cube.
Product Location Time Sales
A X 1/04 200
B Y 1/04 100
A X 2/04 300
In the cube, if you were to look at product by location for just 2/04, you would see:
Product Location Sales
All Loc 300
A X 300
How do you get rid of the zero's or combinations that don't exist?
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
Hi All,I am trying to delete rows from a table using a SQL statement similarto this:DELETE FROM TableA where ID1 IN(Select ID1 From TableB where ID2>= @min and ID2<=@max)Basically I want to delete all rows from TableA that have an ID in arange in TableB. This is done in a stored proc.When I look at the execution plan, it is using the indexes as I wouldhope for. The problem is that it is doing a sort which accounts for73% of the cost. I do not need to sort the results. I don't care whatorder they are deleted in.How can I prevent the sort from occuring? I need this delete to occuras fast as possible.Thanks In Advance
I have a stored procedure that generates the following SQL WHERE clause
UserName LIKE 'Adi234%' AND Fname LIKE 'David%' AND LName LIKE 'Justin%' AND
It is good except that it i can not remove the last AND which is not neccessary at the end of the clause.
I want to remove the last AND that come up at the end, my code places AND after each data field(UserName, Fname, Lname) .
I've created an asp.net page that takes the content of text boxes and writes them to a sql table.
The problem is that when I examine the resulting data in the SQL database, I find that the fields written to have had padding added (up to the maximum size of the fields).
I was under the impression that fields of type NVARCHAR did not store padding (only the no of characters being stored).
I've checked it's not the text boxes on the aspx page by explicitly posting values instead of the boxes content and the same thing happens.
example of function i'm using to post data:
Function AddNews(ByVal Category As String, ByVal ApplicRole As String, ByVal NewsType As String, ByVal Description As String, ByVal News As String, ByVal Hyperlink As String, ByVal Email As String, ByVal BirthDate As Date, ByVal KillDate As Date, ByVal Parent As String) As Integer
Dim connectionString As String = ConfigurationSettings.AppSettings("AuthentConnection")
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [News]([Category],[ApplicRole],[NewsType],[Description],[News],[Hyperlink],[Email],[BirthDate],[KillDate],[Parent]) VALUES (@Category,@ApplicRole,@NewsType,@Description,@News,@Hyperlink,@Email,@BirthDate,@KillDate,@Parent)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_Category As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Category.ParameterName = "@Category"
dbParam_Category.Value = Category
dbParam_Category.DbType = System.Data.DbType.StringFixedLength
Dim dbParam_ApplicRole As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_ApplicRole.ParameterName = "@ApplicRole"
dbParam_ApplicRole.Value = ApplicRole
dbParam_ApplicRole.DbType = System.Data.DbType.StringFixedLength
Dim dbParam_NewsType As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_NewsType.ParameterName = "@NewsType"
dbParam_NewsType.Value = NewsType
dbParam_NewsType.DbType = System.Data.DbType.StringFixedLength
Dim dbParam_Description As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Description.ParameterName = "@Description"
dbParam_Description.Value = Description
dbParam_Description.DbType = System.Data.DbType.StringFixedLength
Dim dbParam_News As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_News.ParameterName = "@News"
dbParam_News.Value = News
dbParam_News.DbType = System.Data.DbType.StringFixedLength
Dim dbParam_Hyperlink As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Hyperlink.ParameterName = "@Hyperlink"
dbParam_Hyperlink.Value = Hyperlink
dbParam_Hyperlink.DbType = System.Data.DbType.StringFixedLength
Dim dbParam_Email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Email.ParameterName = "@Email"
dbParam_Email.Value = Email
dbParam_Email.DbType = System.Data.DbType.StringFixedLength
Dim dbParam_BirthDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_BirthDate.ParameterName = "@BirthDate"
dbParam_BirthDate.Value = BirthDate
dbParam_BirthDate.DbType = System.Data.DbType.Date
Dim dbParam_KillDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_KillDate.ParameterName = "@KillDate"
dbParam_KillDate.Value = KillDate
dbParam_KillDate.DbType = System.Data.DbType.Date
Dim dbParam_Parent As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Parent.ParameterName = "@Parent"
dbParam_Parent.Value = Parent
dbParam_Parent.DbType = System.Data.DbType.StringFixedLength
Dim rowsAffected As Integer = 0
rowsAffected = dbCommand.ExecuteNonQuery
I have lot of SQL logins which do not have access to any databases . They do not show any database access when i check their properties .
My server is SQL 7.0 with SP 3 .
I want to clean up those logins with the help of script instead of deleting every login manually which is time consuming as i have to check the database access before deleting the login.
Is there any where script available or any other way to delete these unnecessary logins ? Also will it be a problem on the SQL Server if i delete those logins at one command ?
Any info. will be helpful to me.
Here is a simplified version of my problem:
I am inserting data into a table using a stored procedure. The table has an identity column that increments with each insert. When I use erroneous data in the other fields the insert failsâ€¦.no surprises there! But when the next insert occurs with valid data I find that my identity field has increased even with those inserts that failed, so my sequence has jumped a few numbers. How do I get the identity inserts to roll back if the rest of the data in a row doesnâ€™t insert successfully?
Can unwanted characters (e.g. control codes) be replaced or removed in varchar fields during extraction inside DTS package?
SQL Server/SSIS 2005.
Hello, I have a report that is based on a dynamic column. The column is specified by the user, and the column tyep may be text, datetime, or bigint. The user specifies the column name, and there is also a parameter that specifies the type. I allow my users to format the date via passing in a date format string, so my code looks something like:
Parameters!primarycolumntype.Value = "text",
Parameters!primarycolumntype.Value = "date",
[DO DATE FUNCTIONS HERE, e.g. Year(Fields(Parameters!primaryitemcolumn.Value).Value)],
Parameters!primarycolumntype.Value = "numeric",
The trouble I am running into is that I am getting invalid date conversion errors whenever a text value is being supplied - in other words, the code in the "DO DATE FUNCTIONS HERE" is being executed regardless of whether my primary column type parameter is text OR date. It would appear that all conversion fuctions are calculated at runtime prior to running other logic. If so, this is a bad thing for me, as that means I cannot mix data types in the same report and figure it out all dynamically. Is this true? I had always thought that the date code (in the example above, the call to Year()) would not be executed unless that statement in the switch statement was true. I've had the same luck with If statements, and Choose statements, and I've even tried using custom code.
I think my whole problem relates to a misunderstanding of how the functions are processed. Can anyone shed some light?
I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc
tblGroups - with GroupID, GroupName
tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called
tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups.
What I want is a list of all contacts, with their IDs, names and what groups they are related to:
ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]
ContactID, ContactName, [SubGroupName1, SubGroupName3]
ContactID, ContactName, [SubGroupName3]
I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)
We can save query output save as CSV file directly from the Query Analyzer window. I have done it at last few year before. Now I need it.Can anyone please give the one example for the same.
Amit K Patel
How do I remove old stored procedures that are no longer being used?
In SQL How do you take a field like the phone field and replace the unwanted Character Like - ( ).
I have created a publication from SQL Server 7.0 and subscribed the publication it to one of the SQL 2000 server std. edition .
Now there are some entries in the subscription list at the subscriber which need to be deleted .
The list shows the name of the publication , the database name and the date .
I wanted to know hoe can we delete those unwanted entries. If i select the entry and right click on it , there is no delete option .
Are those entries to be deleted from one of the tables at the subscriber or the publisher ? If yes, then what is the name of the table ?
Any ideas ?
Thank you very much .
I am having a very wierd issue regarding a DB2 sql query. I need to get data from Db2 and insert into our sql server database. Using data flow task, to get data I am using the data reader source. COnnection is ado.netodbc connection.
THis sql query also has some comments in it.
The first wierd thing is...
1. On Development server, when I run this query manually, meaning using toad, winsql (connection to the db2 database), the query runs fine. Brings back approx 667 rows which is correct. ON the same server when I try to run this query, via a SSIS pkg, data flow task, using data reader source, gives me error on those comments that exist in that query. But if I run the same SSIS pkg on another server (Integration server). It runs fine. The same pkg also runs fine if I run it from my machine. SO What is different on my Dev server compared to the Integration server.
2. Say if I take those comments out from the sql query, then try to run the ssis pkg. The query is stuck at the first record and goes in an infinite loop mode. though my query is not a procedure, it is just a sql statement. But this ssis pkg with the query runs absolutly fine on the other server. I aslo tried using the other types of connection and ole db source but still the same problem on the Dev server.
What do I need to look for that is so different on the dev server compare to the INT server. I also checked the version on both these server for Visual Studio 2005(by going to About Microsoft Visual Studio), it is the same.
This is what I have on both the servers....
Microsoft SQL Server Integration Services Designer
Please HELP !!!!
I have some code that sorts a column logically even if it contains both numbers and text. But I've found that SQL converts certain characters automatically....
Here is the code...
SELECT * FROM TABLE
case when isnumeric(fieldname)=1 then
right(replicate(' ', 10 ) + convert(varchar,convert(numeric(10),fieldname)),10)
else fieldnameend --10 is the length of the field
The issue I've run across is that if my column contains the following strings(5D000,5E000,4E001) it thinks they are numeric then the THEN part of the condition causes a typecasting error....
I found out that any certain combinations of number + E or D + 0. Does anyone know a way to get around this? Try it out select isnumeric('5E0303') will return 1