MS SQL Server :: Duplicate Entries In Returned SQL Field

Removing Duplicate Entries In SQL Field
Hi All,

Below is a snippet of MS SQL inside some VB that retieves
Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get green, red, blue, and yellow which is correct. On another similar search with different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic, Glass, Sand. I want to remove the repeating elements returned in this field. IOW, I just need one set of Plastic, Glass and Sand. I hope this makes sense.

Below is the SQL and the results from the returned page.

PS I tried to use distinct but with no luck I want just one of each in the example below.

Thanks in Advance!

Scott

==============================

SQL = ""
SQL = "SELECT B.CIMS_MSDS_NUM," & _
"A.COMMODITY_NUMBER, " & _
"B.CIMS_TRADE_NME," & _
"B.CIMS_MFR_NME," & _
"B.CIMS_MSDS_PREP_DTE," & _
"B.APVL_CDE," & _
"COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _
"COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _
"A.MSDS_CMDTY_VERIF, " & _
"A.CATALOG_ID " & _
"FROM ( MATEQUIP.VMSDS_CMDTY A " & _
" RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _
" ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _
" LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _
" ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _
" LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _
" ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) "

SQL1 = ""
SQL1 = SQL

SQL = SQL & "WHERE " & Where & " "
==================================

Here is a piece of the problem field, note repeating colors etc.

CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65B1 GLOSS BLACK F65N11

View Replies !

Check For Duplicate Entries In One Field
Obviously, I'm a complete n00b at SQL.

I have a table in Access 2003 with about 6,000 records and there are about 20 records that have duplicate data in the first field (CompID).

I'm trying to make the first field my primary key, so I need to fix these duplicate entry.

I could export to Excel and fix the problem that way, but in the interest of learning SQL I want to figure out how to do it properly.

Thanks in advance for what is hopefully a simple answer.

View Replies !

Duplicate Entries In SQL W/ IE 5.5
I am getting duplicate entries in SQL database(7.0 or 2000)when users running IE 5.5 or higher access my ASP pages. We are running IIS 4.0, but the problem occurs with IIS 5.0 also. I'm finding no information on this problem...has anyone else had the same experience?

Thanks

View Replies !

Duplicate Entries

I have an issue where certain parts of data are repeated several times after i create my query. Without providing my SQL code for now could anyone suggest possibly the main reason(s) for data being duplicated?

Thanks

View Replies !

Duplicate Entries
I have an application that allows the user to enter data into a table. There are multiple users so I put in some code that, I thought, would keep 2 users from creating a new record at the same time. The IDs for the records are identical and this is causing a problem.

The IDs are in the format of ####-mmyy. at the start of each month the #### part goes back to 1.

We tried a test today where we had 2 users click on the New button at exactly the same time. The IDs that were created were identical. Is there anyway on the database that I can prevent this from happening?

Here is how I create the new record id:

I get the MAX(ID) from the table
I add 1 to the ID and then insert a new record with the new ID into the table.

Any help is appreciated.

Thanks,
enak

View Replies !

Duplicate Entries
I'm extracting data from a log (log_history) of patients where nurses perform various actions on a call, such as assessing and reassessing, despatching etc. This is the script:

Select
L.URN,
LH.THE_TIMESTAMP,
LH.ACTION_TYPE,
LH.ACTION_BY,
LH.ACTION_REQD,
LH.NOTE,
em.position_type_ref
From
LOG L
Join Log_history LH on (L.URN = LH.LOG_URN)
left outer join employee em on (em.code = LH.action_by)
Where
(L.Taken_at >= :DateFrom and L.Taken_at <= :DateTo) and (LH.ACTION_TYPE = 'D') and (em.position_type_ref ='NU')

Order By
L.URN ASC, LH.THE_TIMESTAMP DESC

The result I get shows duplicate 'timestamp' entries and I only want to return unique timestamp entries. Does anyone have any ideas. I'm self taught and have hit a wall

View Replies !

Remove Duplicate Entries

I am a newb at ms sql and was hoping someone could help me
eliminate duplicate PRODUCT.PRODUCT from this statement. I have tried using DISTINCT with the same results.The ProductImage table is causing this because
the duplicates are from the PRODUCT.PRODUCT that have more than 1 image.

If anyone could rewrite this statement so I can learn from this, it would
be most appreciated!

Thank you for your time

<asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"SelectCommand="SELECT Product.Product.productid,Product.Product.catid,Product.Product.name,Product.Product.smalltext,Product.Product.longtext,Product.Product.price,Product.ProductSpecial.saleprice, Product.ProductSpecial.feature,Product.ProductImage.imgId, Product.ProductImage.imgUrlFROM Product.ProductINNER JOIN Product.ProductSpecialON Product.ProductSpecial.productid = Product.Product.productidINNER JOIN Product.ProductImageON Product.Product.imgid = Product.ProductImage.imgId"></asp:SqlDataSource>

View Replies !

Dont Sum Duplicate Entries

Hi.
I have a table with Login and Logoff Time of users, but there could be duplicate Logtimes in the dataset, but for
different products. Because of this I cant do a distinct in the dataset. I need the Product and some other details in my Report.

I tried to make two datasets. One for the Select distinct and one for the other.

But the Problem is:
in my report, I need a table, where I make the Sum of the Logintime a day and in another column I calculate with data from the other dataset.(Logtime + data from dataset2). But this doesnt work, so I think, that is it not possible to join 2 dataset in one table.

datetime Login | datetime Logout | Product
11.12.2007 10:15 | 11.12.2007 12:15 | p1
11.12.2007 10:15 | 11.12.2007 12:15 | p2
11.12.2007 12:19 | 11.12.2007 15:15 | p2

Is there another option I can do this?

View Replies !

Duplicate Entries In The Resulting Table
Hi! I am joining 3 tables in SQL , I am getting the results I want exept it's duplicated. So the resultinmg table fom my stored procedure has 3 rows that have the same bulletin. How do I filter the storedprocedure to output only the rows that don't have duplicate entries for the column 'Bulletin' Thanks.
Here is my stored procedure:PROCEDURE [dbo].[spGetCompBulletins]
@Userid uniqueidentifier OUTPUT,@DisplayName varchar(200)

AS

SELECT *
FROM dbo.UserProfile INNER JOIN
dbo.bulletins ON dbo.UserProfile.UserId = dbo.bulletins.Userid INNER JOINdbo.Associations ON dbo.Associations.BusinessID = dbo.bulletins.Userid WHERE UserProfile.DisplayName=@DisplayName
and Userprofile.Userid = @Userid ORDER BY Bulletins.Bulletin_Date
Return

View Replies !

Preventing Duplicate Database Entries
Hi all.. I've been scouring the forums for about 6 hours to no
avail. This is a really simple question. I'm trying to have
a registration page that lets the user input name, email, desired
username, and password. I want to check the username and email
fields to make sure ppl cannot sign up twice. So from what I've
gathered I have a couple of options:

1) i can set up a unique constraint on the database columns,
2) i can run a select statement before inserting,
3) i can store the whole database column in a variable then search through it.

My question is how to do option 2? All of my transactions are through a sqldatasource object in c#.

View Replies !

Prevent Duplicate Entries In A Table
I have an ASP.Net Web appplication with a Back-End SQL DB. There are 3 Tables; Users, Groups, and GroupMember.

The GroupMember table is used to link Users to Groups and consists of just two fields; userID and GroupID.

Here is a sample of some data:

User1 Group1
User1 Group2
User2 Group2
User3 Group1
User3 Group3

Users can belong to multiple Groups. However, you shouldn't be able to have the same user and group comobination more than once. for example:

User1 Group1
User2 Group2
User1 Group1

I can stop this kind of duplicate data entry by doing a lookup first (using asp.net) to see if the entry already exists but this seems cumbersome.

Is there a simpler way to prevent duplicate entries in a table using sql?

Thanks a lot,

Chris

View Replies !

How Can I Remove Duplicate Entries In A Sql Query?
I have a database being populated by hits to a program on a server.The problem is each client connection may require a few hits in a 1-2second time frame. This is resulting in multiple database entries -all exactly the same, except the event_id field, which isauto-numbered.I need a way to query the record w/out duplicates. That is, anyrecords exactly the same except event_id should only return one record.Is this possible??Thank you,Barry

View Replies !

Duplicate Backup Entries Using SQLMAINT
I would appreciate someone pointing me in the right direction to resolve a backup anomaly
were currently experiencing. We recently installed SQL Server 6.5 and noticed that although
our scheduled tasks were running as requested the DelBkUps parameter wasn`t working. In
addition we noticed that the backup files that were created didn`t have the time portion
appended to date (suffix of backup name). The steps we took to resolve this was to install
Service Pack 3. After the install the following was observed: 1) DelBkUps parameter started to
work 2) duplicate backup entries were created, the only difference being that for one of
the entries the time portion was still missing (ie. apps_db_dump.19980722 instead
of apps_db_dump.199807221840). Letting in cycle through for a week didn`t have any affect.
The final observation is that for scheduled tasks that occur more than once/day (i.e. transaction
dump every 8 hours) no duplicate backup entries are created and file suffix is correct
(i.e apps_db_dump.199807221840).

.......thanks,,,,brad

.............

View Replies !

Stop Inserting Duplicate Entries
Hi I am trying to insert entries in a table which has a composite primary key and i am inserting it on UID basis.

INSERT INTO TABLE_B (TABLE_B_UID,NUM_MIN, NUM_MAX,BIN, REGN_CD, PROD_CD, CARD)
(SELECT UID,LEFT(NUM_MIN,16),LEFT(NUM_MAX,16),BIN, REGN_CD, PROD_CD, CARD FROM TABLE_A WHERE UID NOT IN (SELECT TABLE_B_UID FROM TABLE B))

When i insert it tries to insert a duplicate entries and gives me an error. Since I am new to SQL SERVER 2000 i need some help. I tried IF NOT EXISTS, EXCEPT but i guess i am wrong at the syntax.

Can anybody help me out?

View Replies !

Truncating Duplicate Entries In A Table
Hi,

I have a table with no primary key and i just want to see all the duplicate entries on the basis of two columns. Can anyone suggest me how should i go about it.

Can anyone provide me the syntax for the same?
I have only 1 table say ISSR_TBL and two columns using which i want to delete the duplicate ones. i.e. MIN and MAX.

Please help me out...

View Replies !

Finding Duplicate Entries (with Different Keys)
Yet another simple query that is eluding me. I need to find records in a table that have the same first name and last name. Because the table has a primaty key, these people were entered twice or they share the same first and last name.

How could you query this:

ID fname lname
10001 Bill Jones
10002 Joe Smith
10003 Sue Jenkins
10004 John Sanders
10005 Joe Smith
10006 Harrold Simpson
10007 Sue Jenkins
10008 Sam Worden

and get a result set of this:

ID fname lname
10002 Joe Smith
10005 Joe Smith
10003 Sue Jenkins
10007 Sue Jenkins

View Replies !

Best Method Of Checking For Duplicate Entries In SQL Server
Here is my situation. I have a table in my application that pairs users with cars they like. We'll call this table Favorites. A user can browse the site and they can designate as many cars they want as favorites. For example, a user can go to the Honda Accord page and add that as a favorite car and then go to the Toyota Camry page and add that as a favorite car. However, if he/she goes to that Honda Accord page and tries to click the "Add to Favorites" button again, at the present state of my application, it will just add another entry into the Favorites table with a duplicate pairing. So, if I were to datalist the table to generate a listing of all favorites belonging to a certain user, he/she may potentially be returned with superfluous duplicate entries. Not to mention, taking up valuable database space and not looking very professional.
In my Favorites table, the 3 fields are.....favoriteId (set as primary key)userIdcarId
I've been thinking about this for awhile and I've come up with 2 solutions. I'm a newbie to ASP.NET/programming so I don't have enough insight to make a decision or to even think up of other alternatives.
1) Check proactively by doing a.....SELECT favoriteID FROM Favorites WHERE userId = x and carId = y (where x and y are variables)If I get a null return, it means I can go ahead and let the user add the car as a favorite in the database. If I get a valid value, then it means there already exists the same pairing, so I exit out without updating the table.
2) Check reactively by forcing an exception whenever a user tries to enter a duplicate pairing. I'm not sure how to do this, but perhaps, instead of making "favoriteId" a primary key, perhaps, I can make a primary key pairing of "userId" and "carId". And by trying to do an insert with a primary key that already exists, we know it won't work since primary keys by definition are unique.
Now, I expect some concurrent users on my site, so I must take into consideration pros and cons of each and determine which is more efficient. Checking proactively will force a check even if the table does not contain a duplicate pairing of user and car. However, having a duplicate primary key may be more expensive from a database point of view and may slow down lookups, etc. Or maybe neither has significant benefits, in which case, I rather go with proactive, since I've already coded it and it works fine. Or maybe there is a third alternative, which I did not think. Which method do programmers usually take and which is a better practice?
TIA for your help.

View Replies !

Writting Trigger Or Procedure To Delete Duplicate Entries In A Table?
I am using Sql Server 2000.
I have a customer table with fields - CustId, Name, Address, City, StdCode, Phone.
I used to insert entries in this table from an excel file.
One excel file will contain thousands of customer.
In this table combination of StdCode and Phone should not be repeated.
If I do it in my VB.Net coding.then application gets drastically slow.
So I want to write a procedure or trigger for this.
Here what I will do, I will send all records into database then this trigger or procedure will check for
any existing entry of combination of StdCode and phone. If entry exists then this will delete new entry
or will not allow this new entry.
Is this possible to do using Trigger or stored procedure?

View Replies !

Delete Duplicate Entries From Tables In My Database Using Query Analyzer
Hello,

How can I delete duplicate entries from tables in my database using Query Analyzer, as there are many duplicate entries in my tables, I want to delete them.

Thanks in advance,
Uday.

View Replies !

CREATE TABLE DUPLICATE OBJECT/DUPLICATE FIELD NAME ERROR Msg 2714
Hello Everyone:

I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.

When I try to create the tables, for the query, I am getting the following error:

Msg 2714, Level 16, State 4, Line 12

There is already an object named 'UserID' in the database.

Msg 1750, Level 16, State 0, Line 12

Could not create constraint. See previous errors.

I have duplicated this error with the following script:

USE [testing]

IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL

DROP TABLE [testing].[dbo].[users1]

CREATE TABLE [testing].[dbo].[users1] (

[UserID] bigint NOT NULL,

[Name] nvarchar(25) NULL,

CONSTRAINT [UserID] PRIMARY KEY (UserID)

)

IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL

DROP TABLE [testing].[dbo].[users2]

CREATE TABLE [testing].[dbo].[users2] (

[UserID] bigint NOT NULL,

[Name] nvarchar(25) NULL,

CONSTRAINT [UserID] PRIMARY KEY (UserID)

)

IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL

DROP TABLE [testing].[dbo].[users3]

CREATE TABLE [testing].[dbo].[users3] (

[UserID] bigint NOT NULL,

[Name] nvarchar(25) NULL,

CONSTRAINT [UserID] PRIMARY KEY (UserID)

)

I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.

I think that the schema is only allowing a single UserID primary key.

How do I fix this?

TIA

View Replies !

Finding Duplicate Entries In A &"smart&" Way - By Comparing First Two Words
What is the best way to compare two entries in a single table wherethe two fields are "almost" the same?For example, I would like to write a query that would compare thefirst two words in a "company" field. If they are the same, I wouldlike to output them.For example, "20th Century" and "20th Century Fox" in the companyfield would be the same.How do I do this? Do I need to use a cursor? Is it as simple as using"Like?"

View Replies !

No Results Returned When Using BIT Field
Hi,
I've got what is probably a simple question, but for the life of me I cannot figure it out and it is starting to do my head in . I have the following query:-
Select e.intEmployeeID AS ID, e.txtFirstName AS [First Name], e.txtLastName As [Last Name], e.txtMobile As [Mobile], i.txtWorkEmail as from EMPLOYEE AS e INNER JOIN EMPLOYEE_INFORMATION AS i ON i.intEmployeeID = e.intEmployeeID WHERE (((e.blnActive = -1) AND (e.txtFirstName Like '%b%')) OR ((e.blnActive = -1) AND (e.txtLastName Like '%b%')) ) ORDER By e.txtFirstName
In my database, the field blnActive is a BIT field. When I try to run this query, I get no results returned. If I remove the (e.blnActive = -1) WHERE statement segment, I get the correct results. The field blnActive contains -1 for every record but I cannot work out why it will not return these records .... aaarrrrggghhhh
Thanks to those who feel my pain !!

View Replies !

I Only Get The First Character Of The Field Returned .. What Am I Doing Wrong ?

here is my business/data object for some reason I only get the first character back, say value is Charlie, I only get C public string GetUserName(long UserId)
{
try
{
string userName;

DiscussionDB data = new DiscussionDB();
List paramlist = new List();
paramlist.Add(data.CreateParameter("@UserId", UserId));
paramlist.Add(data.CreateParameter("@UserName", "", ParameterDirection.Output, DbType.String, 20));
data.ExecuteNonQuery("dbo.Discussion_User_Name", ref paramlist);
userName = paramlist[1].Value.ToString();

return userName;
}
catch
{
throw;
}
}

ALTER PROCEDURE [dbo].[Discussion_User_Name]
@UserId bigint,
@UserName varchar(20) output
AS
SET NOCOUNT ON

SET @UserName = (Select [Name] from Discussion_Member WHERE UserID = @UserID)

if (@UserName is null or @UserName = '')
BEGIN
SET @UserName = (Select UserName from Membership_User WHERE UserID = @UserID)
END

View Replies !

How Can I Group Data Returned By A Datareader By A Particular Field
How can i group the data returned by statement below by "Dept" field. I have already tried to group it like shown below but that doesn't seem to work for me. Any help.
cmd_select = New SqlCommand("SELECT Incident_id,Dept From Report_Incident Group By Dept,Incident_id", myconnection_string)

View Replies !

No Results Returned By SELECT Against Datetime Field
I am trying to pull results from an SQL Server DB into an dataset where a particular field (SMALLDATETIME) is within a particular date range. The code I was using worked fine when the database was in Access. I have made several changes already but am still getting 0 results returned when there is data that should be returned.

I was using in Access:
Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>=#" & startDate & "# AND myDateField<=#" & stopDate & "# ORDER BY ID"
I have changed this for SQL Server to:
Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>='01/01/2003 00:00:01' AND myDateField<='01/01/2004 23:59:59' ORDER BY ID"
But I am always returned 0 results even if the date range should return plenty of data. I have also tried using the BETWEEN structure with the same result.

Is there a particular format for the date I am comparing with?
Am I missing something else in my query?

The connection / permissions and everything else are correct as I can read and write data to the database in numerous other pages. It is just this date comparison that is not working.

Many thanks for any help or comments you can provide.

View Replies !

The Data Extension Returned An Error During Reading The Field.
Can anyone tell me what the heck this is trying to tell me. I have 3 datasets I'm working with so there are errors for each here:

[rsMissingFieldInDataSet] The data set €˜Revenue_By_Client€™ contains a definition for the Field €˜Branch€™. This field is missing from the returned result set from the data source.

[rsErrorReadingDataSetField] The data set €˜Revenue_By_Client€™ contains a definition for the Field €˜Branch€™. The data extension returned an error during reading the field.

[rsMissingFieldInDataSet] The data set €˜Main_Dataset_AZ€™ contains a definition for the Field €˜PostedAmount_InHouse€™. This field is missing from the returned result set from the data source.

[rsErrorReadingDataSetField] The data set €˜Main_Dataset_AZ€™ contains a definition for the Field €˜PostedAmount_InHouse€™. The data extension returned an error during reading the field.

Preview complete -- 0 errors, 4 warnings

View Replies !

De-Duplicate Only One Field
How can I de-duplicate a field and return all other fields without affecting them?

View Replies !

Inflated Number Of Records Returned When Adding Bit Field Criteria
When querying a bit field, I am encountering a problem with MS SQLServer returning a larger number of records for a table than theactual number of records that exist within that table.For example, my customer table has 1 million unique records, so theresults of the following query are as such:select count(customer_nbr) from customer = 1,000,000There is bit field in the customer table that denotes whether acustomer has placed an order with us called. That flag is calledorder_flagIf I run the following query:select count(customer_nbr) from customer where order_flag = 1The result is 3,000,000 records.There is no logical way that this is possible, as my table onlycontains 1,000,000 unique records and the number of customers with anorder should be a subset of this.If a run the above query with a distinct before customer number, I getthe results I want:select count(distinct customer_nbr) from customer where order_flag = 1600,000 records.So while I can get to the answer I want, I have no idea why I amreturning incorrect values if I don't select distinct.Can anyone help? I checked microsoft support and message boards buthaven't seen anything.I should note that the bit field is indexed.I am not sure if that isthe problem or not.

View Replies !

[rsInvalidExpressionDataType] The Value Expression Used In Field Returned A Data Type That Is Not Valid.
I got this error for a calculated field that calls a public function of a custom assembly based on the report parameter values. The return type is an array of double.

=code.ovalues.dGetValues(Parameters!Report_Start.Value, Parameters!Report_End.Value, Parameters!Tag.Value, Parameters!Agg.Value)

Does SSRS 2005 supports the type double for a field? Can the data source of a field in a dataset be an array? Is there a way to pass the array to a field by reference and display the values at runtime?

I would appreciate any help since I could not find anything helpful on the internet and Microsoft document!

View Replies !

Trying To Append A Field If An Insert Happens To Contain A Duplicate
I deliberately intend to add some duplicates to one of my tables. For eg

Job User IsAdmin

JobID 235User ID 1
JobID 235User ID 5
JobID 235User ID 9
JobID 235User ID 5
JobID 235User ID 2
JobID 235User ID 9
JobID 235User ID 10
JobID 235User ID 1

I know its bad practice to do such a thing but there is a genuine reason. What I need to do is to be able to have a SQL statement that appends true to the IsAdmin field whenever it encounters the next UserID thats happens to be a duplicate. Hence the above would look like:

Job User IsAdmin

JobID 235User ID 1
JobID 235User ID 5
JobID 235User ID 9
JobID 235User ID 5 True
JobID 235User ID 2
JobID 235User ID 9 True
JobID 235User ID 10
JobID 235User ID 1 True

Thanks

View Replies !

Duplicate Entry In A Primary Key Field
Hi everybody couldn't get through with saving my data on the table with two primary keys...

my table structure is this

pubidintUnchecked (primary key)
pubchar(1)Unchecked
publchar(1)Unchecked
pubcodechar(2)Unchecked (primary key)

a sample data is here

pubid pub publ pubcode

1 a b ab
1 b b bb
2 a b ab
2 b b bb

when i save this table modifying the pubid and pubcode as primary keys the following error displays...

Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.

what i understand is that on the primary key duplicates are not allowed how could i allow it?

thanks

View Replies !

Select Records That Do Not Duplicate On A Certain Field.
I am looking for some help in pulling certain people out of a table

This is the basic setup of my table

PK ID NAME MEETING
1 11111 Joe CLASS98
2 22222 Jane CLASS98
3 33333 Bob CLASS98
4 11111 Joe CLASS04
5 22222 Jane CLASS04
6 44444 Sally CLASS04

What I am wanting to do is Select only the people who attended CLASS98 but didn't attend CLASS04

I could just remove the CLASS04 people by sorting if I could just return the rows that do not duplicate the ID and meeting is either CLASS04 or CLASS98

Either way would get me to my goal.

Any help is GREATLY appreciated.

Thanks
Jimmyjoe

View Replies !

Select Single Row With Duplicate Value In Particular Field
Hi,

I have several row records in which

name email
--------- ---------
name1 abc@abc.com
name2 abc@abc.com
name3 abc@abc.com
name4 123@123.com
name6 123@123.com

How should I write the query which can return records with unique email address with any name attached?

The expected record set will be

name email
--------- ---------
<any> abc@abc.com
<any> 123@123.com

I was thinking to select the distinct of email and just stuck at here. I encountered this problem few times and still cannot solve it. Any help will great appreciated. Thanks in advance.

View Replies !

How To Delete Duplicate Data From Field

my table structure is
  id                field1
  1               i am from india
  2               i am from usa
  3               i am from  delhi

So i want to remove common data from field1 , means   after run the query table should be like

  id                field1
  1               india
  2               usa
  3               delhi

thanks in advance
saumitra tamrakar

View Replies !

SELECT Highest Value Of Duplicate Field
I have a table like
TradeID ActionID
58096 3663
58096 3664
78901 2235
78901 2236

I want to select the only the TradeID with the highest ActionID

I tried using
select distinct tradeid,actionid
From
cct
Where ActionID = (SELECT MAX(ActionID)
FROM cct1
WHERE cct1.TradeID = cct.TradeID)
group by tradeid,actionid

but the result is not correct

please help

In god we trust,everything else we test.

View Replies !

Query Returning Duplicate Field Names
I have a .NET program that can connect to either an Access 97 database or anSQL Server 7 database. In the database I have two tables which have a fieldcalled ID. When I run a query like "SELECT A.*, B.* FROM A, B", the queryreturns those fields as "A.ID" and "B.ID" when connected to Access 97, butas "ID" and "ID" in SQL Server 7. Is there anyway to get SQL Server toprepend the table name to the field name in a case like this and not returnduplicate field names like that without having to specify aliases for thefields?- Don

View Replies !

Group By Query To Find Duplicate Field Value
Hello,

Not sure how to do this. I think I need to use a Group By query. I have a "Products" table with the following fields:

Program#
Number
UPC
Item
Item#
Size
Dept

Everything is specific to the Program#. In other words, for every instance of a Program# there can be more than one Product, which is specified by the "Number" field. So: SELECT * FROM Product WHERE [Program#] = '12345' should return this:

12345 | 1 | 000012345678 | Cookies | 98765 | 12ct | Retail |
12345 | 2 | 000012345678 | Cake | 98765 | 12ct | Retail |
12345 | 3 | 000012345678 | Ice Cream | 98765 | 12ct | Retail |

However, some recordsets are returning like this:

12345 | 1 | 000012345678 | Cookies | 98765 | 12ct | Retail
12345 | 1 | 000012345678 | Cake | 98765 | 12ct | Retail
12345 | 2 | 000012345678 | Ice Cream | 98765 | 12ct | Retail

In which case I have to fix them (the "Number" field) with an update query sp they are numbered sequentially. Luckily, this doesn't happen very often.

Can someone help me with a query that will return only those records with duplicate values in the "Number" field? I am not sure how to construct this query which I will use as a stored procedure.

TIA,
Bruce Wexler
Programmer/Analyst

View Replies !

.dbf File Import (duplicate Field Names)
I am importing a file creating by an application which exports the file into .dbf format. Very unfortunately, this .dbf file can have fields with IDENTICAL column_names. Utilizing ActiveX, I create an ado connection to the .dbf file using a visual foxpro drver. However, and not unexpectantly, I can not do the 'select *' from the file if there are duplicate names.

Can anyone make recommendations here that might help?

Oh, this is SQL200 in case that impacts what you might advise!!!!

View Replies !

Data Returned From .WriteXML Is Different Than What Is Returned In Query Analyzer.
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList);
13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)

View Replies !

Why Duplicate Field &"LoweredEmail&" ...?
Hi,
in table aspnet_membership of ASPNETDB.mdb file, i saw a kind of duplicate field like "LoweredEmail" containing the same value as field "Email" (also in table users field "LoweredUsername" ..).
I guess it's for containing email in lowercase, but why those fields?
Thanks
Tartuffe

View Replies !

Current Log - Too Much Entries?
Hello,does anyone know what could be wrong? There's a lot of similar entriesin the SQL Server current log:2004-01-21 17:54:00.01 spid57 Starting up database 'DB_NAME'2004-01-21 17:54:06.45 spid57 Starting up database 'DB_NAME'2004-01-21 17:54:59.13 spid56 Starting up database 'DB_NAME'There are hundreds of similar entries that repeat every few seconds. Thedatabase works quite slowly and the users get 'Connection timeout'message very often.Thanks for any advice....:: fabio

View Replies !

Finding Last Entries
Hi, I am searching for the most easy SQL solution:

Lets say i have 500000 rows of cars in one table with 30000 different car ID's.
This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?

Thx for reply.

dajm

View Replies !

Keeping Last 10 Entries By ID
Hello

my table :
Report :
R_id (PK)
RName
RDate

i am having a few 10.0000 lines and i want to keep the last 10 (or less if not in the table) rows maximum for each name

i can have 100 report by name (100 rows with the same name and of course R_id and RDate are different)

how can i do it ?

thanks a lot for helping

View Replies !

Want To Pull Last Three Entries From DB
I am trying to pull the last three entries from a table in my database but I am having trouble writing the correct query. The database has multiple entries for each item in my database but I want to pull just the last three for each item. I have tried the TOP function with the items ordered in descending order but that only works when I define each item seperatley. I need to know how to pull the last three entries for every item I have in the table.

Thanks,

Tim

View Replies !

Registry Entries
Below are the registry entries I want to update but "Trusted_Connection" = "Yes" does not seem to have any effect when it is run. Any ideas??

REGEDIT4

[HKEY_CURRENT_USERSoftwareODBCODBC.INIAAED]
"Driver"="C:WINNTSystem32sqlsrv32.dll"
"Description"="AAED"
"Server"="SIM0108"
"Trusted_Connection"="Yes"

[HKEY_CURRENT_USERSoftwareODBCODBC.INIODBC Data Sources]
"AAED"="SQL Server"

View Replies !

SQL 7 Registry Entries
Is there anything that can clean out the hundreds of SQL7 registry entries on my client following several failed installs using RC1? I only got it intsalled using the Gold version.

View Replies !

SQL 7 Registry Entries
Since installing SQL7 Gold on my client running W98, McAffee detects what it thinks is a virus in C:Program FilesOLAP ServicesSamplesMdxSampleQryFile.cls. If I then tell McAffee to scan that particular file, it finds nothing wrong. Anyone got any ideas? This ahppens about every 20 minutes.

View Replies !

How To Remove Dup Entries
Hi All
In my application i have to get the data from .csv file. My requirement is that file may consists of duplicate entries
I ant to remove the dup entries and i want to place in the table.
Waiting for valuable replies
Thank u
Baba

View Replies !

Help With Multiple Entries
Hi,

I need help desperately. The following is an example of the data that I have.

I have two tables one name Customers and the other named Orders. The tables have the following attributes

CUSTOMER
CustomerNum, CustomerName, OrderNum

ORDER
OrderNum, OrderDesc, CustomerNum

Bare in mind that the CustomerNum can have multiple customers attached to it, example a whole family (I know bad database design but it's too late to change) Let's use the following info for the tables respectively

CUSTOMER
0001 Sharon Bigbsy 1234
0001 Dale Bigbsy 1235
0001 Omar Bigbsy 1236

ORDERS
1234 Chips 0001
1235 Gatorade 0001
1236 Candy 0001

The query i'm using is select Customer.CustomerNum, Customer.CustomerName, Orders.OrderDesc from CUSTOMER, ORDERS where CUSTOMER.CustomerNum = ORDER.CustomerNum

The results are as follows

0001 Sharon Bigbsy Chips 1234
0001 Sharon Bigbsy Gatorade 1235
0001 Sharon Bigbsy Candy 1236
0001 Dale Bigbsy Chips 1234
0001 Dale Bigbsy Gatorade 1235
0001 Dale Bigbsy Candy 1236
0001 Omar Bigbsy Chips 1234
0001 Omar Bigbsy Gatorade 1235
0001 Omar Bigbsy Candy 1236

It's giving all the orders place to each customer name but I need only the following

0001 Sharon Bigbsy Chips 1234
0001 Dale Bigbsy Gatorade 1235
0001 Omar Bigbsy Candy 1236

I'm using SQL in MS Access 2003. Please disregard the abnormalities of the tables, it's someone else's work I inherited with over 6000 entried.

Any help will be greatly appreciated

View Replies !