How Do I Exclude Certain Unwanted Data From A Query Result

Hi All,

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.

View Replies


How To Delete Unwanted Data From Multiple Different Tables With One Single SQL Query?

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:

FROM dbo.sysobjects
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 fromtableNamewhere Date_Stamp = '2008-3-18'

View Replies View Related

Exclude Duplicates From Result Set

This should be trivial but I'm ignorant so I'm hopingsomeone 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 matchesagainst 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 theres_ID and emp_IDas expected, if no match, no recordand 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.

View Replies View Related

Exclude Query

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

View Replies View Related

Exclude From Query

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.

Thank you
Mr. Newbie

View Replies View Related

Using Result From One Query For Data Flow Task

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.



View Replies View Related

How To Exclude Null Data?

I'm new to MS SQL server. Is there a select statement that will include fields that are not null?

View Replies View Related

How To Exclude Data From An Average?

I am selecting the following fields


I want to select both fields, like this

Select AVG(AVG_Back), AVG(AVG_Yield)
FROM tblUser
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.

View Replies View Related

How Do I Exclude Data From One Table Based On Data From Another?

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)))

View Replies View Related

Update Table With Result From Data Mining Query

I apply data mining query transformation to data i got from conditional split (Table Employeeswhere 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 cani update the table with the result of Data Mining Query Transformation?

Thank you.

View Replies View Related

How Do You Exclude Data That Doesn't Exist?

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

All Loc

How do you get rid of the zero's or combinations that don't exist?



View Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

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


View Replies View Related

End Result Is Main Query Results Ordered By Nested Result

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
AND showAccess.remoteID=21

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?


View Replies View Related

SP To Perform Query Based On Multiple Rows From Another Query's Result Set

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!    

View Replies View Related

Re-display Result Set Without Re-running Query In Query Analyzer?

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.

View Replies View Related

Unwanted Sort

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

View Replies View Related

How To Remove Unwanted Characters In SQL?

Hi all,
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) .

View Replies View Related

Unwanted NVARCHAR Padding

I've created an 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
End Try

Return rowsAffected
End Function

View Replies View Related

Remove Unwanted Logins .

Hi ,

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.

Sandeep .

View Replies View Related

Unwanted Auto-incrementing

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?
Marcha x

View Replies View Related

How To Remove Unwanted Characters During ETL?

Can unwanted characters (e.g. control codes) be replaced or removed in varcharfields during extraction inside DTS package?

SQL Server/SSIS 2005.

Thanks, Andrei.

View Replies View Related

Unwanted Date Conversion - Please Help

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?



View Replies View Related

Return Result From One Query As A Column In Other Query

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. :)


View Replies View Related

Save Query Result At CSV Or TXT File From The Query

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.

AmitK Patel

View Replies View Related

Removing Unwanted Stored Procedures.

How do I remove old stored procedures that are no longer being used?



View Replies View Related

Remove Unwanted Character From String

In SQL How do you take a field like the phone field and replace the unwanted Character Like - ( ).

View Replies View Related

Deleting Unwanted Entries At The Subscriber .

Hello ,

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 .

View Replies View Related

Inconsistent Result Using Data Reader Source To Get Data From DB2

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 serverwhen I try torun 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 sameSSIS pkgon 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 nota 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 VisualStudio), it is the same.

This is what I have on both the servers....
Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00

Please HELP !!!!

Thank you.

View Replies View Related

Unwanted Conversion Fails Sorting Code

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...


order by
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

View Replies View Related

Copyrights 2005-15, All rights reserved