Through out the table any number of the columns can be null.
I wish to remove rows from this table where the columns values are contained in another row i.e Row 2 above is contained within row 3. Similarly, row 3 is contained within row 4.
So, the only rows I want from the sample data above are rows 1, 4 and 5.
I hope I have explained my query adequately, and any help would be great appreciated. (Before I go mad...!)
Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name
Hi all..I have two tables such as cisco and ciscocom. and i wan to compare eachrow of ciscocom with cisco having same column values. i wan to get thecount of matching columns for each row in cisco...eg:Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco hascolumns:fw,ports,sec,des,tput etc. i wan the number of matching columfor each row in ciscocom. please provide me with the procedure....Waiting for your response....
I've been matching some incoming contacts to existing contacts in a database and need to update, insert, or rematch based on the ifs below.
If name, phone, and email all provided for both, then use the highest Source. So if the Contact has a Source value of 5 and SourceContact has a ContactSource of 1, update Contact with SourceContact
If name, phone and email all provided, and source the same then update to new values.
If name and phone on SourceContact and name and Email on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and email on SourceContact and name and phone on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and phone on SourceContact and name and/or Phone is blank in Contact then update
If name and email on SourceContact and name and/or email is blank in Contact then update
If phone numbers can be different, just update record to SourceContact if it has a same or higher ContactSource
If email address do not match then set SourceContacts to -1 Contact_fk it was computed incorrectly. Both have a non blank email
If Contact_fk is 0 then it is a new contact and just insert it.
IF OBJECT_ID('dbo.SourceContact', 'U') IS NOT NULL DROP TABLE [dbo].[SourceContact]; CREATE TABLE [dbo].[SourceContact] ( [SourceContact_pk]INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_SourceContact PRIMARY KEY CLUSTERED, [ContactLastName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactLastName DEFAULT (''), [ContactFirstName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactFirstName DEFAULT (''),
I have 4 rows below in file tblTEST, and I want to be able to transfer the CODE from the MAIN location to the INT location (replacing all existing "A" codes), preceeded by an "I".
ID LOC CODE -- ----- ------ 11 MAIN B 11 INT A 22 MAIN C 22 INT A
I want the result to be:
ID LOC CODE -- ----- ------ 11 MAIN B 11 INT IB 22 MAIN C 22 INT IC
I am stumped as to how to do this - any help or advice would be appreciated.
The only thing I've come up with is:
UPDATE S SET s.code = B.code FROM tbltest B LEFT OUTER JOIN tbltest S ON B.id = S.id WHERE (S.loc = 'INT')
I have some stored procedure and there is a cursor inside it. I added some new columns to the table and those columns I included in the cursor declaration and fetch statement. In the cursor declaration I forgot to add comma (,) in between the new columns. So SQL Server it considers as a alias name for that column so syntactically it is correct. But logically in the cursor declaration having less number of columns than the columns in the fetch statement. So it should throw an error. But the procedure is getting compiled without raising any error. But if I execute the procedure that time it is throwing the error.
For example, below I have given the sample procedure. In this procedure, in the cursor declaration I removed the comma (,) between DOB and DOJ. If I compile this procedure it is getting compiled. But when execute that time only it is throwing the error. So I am interested in if any option is available to know the error in the compilation time itself.
ALTER PROCEDURE Test AS BEGIN BEGIN TRY
DECLARE @empId INT, @fname VARCHAR(50), @dob DATE, @doj DATE
SELECT a.TestID, a.TestCode FROM TableA a WHERE UPPER(RTRIM(a.TestCode)) IN SELECT (SELECT UPPER(RTRIM(b.TestCode)) FROM TableB b)
Of course the above query is missing a few things but with ETL the where clause UPPER(RTRIM does not appear to be something that has an object or property that I can use in the Lookup.
Is it possible to do pattern matching against a string using FINDSTRING or similar in a derived column expression without recourse to including 3rd party regexp style plugins?
I want to seach for a reference in a string which will have the format ANNNNNAAA ie. an alpha with a certain value followed by 5 digits followed by three alphas with specific values.
SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours FROM dbo.tblEventStaffRequired;
and SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked FROM tblStaffBookings GROUP BY EventID, Role, StartTime, EndTime, Hours;
How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?
I have a strange request that might not be possible based on the laws of relational databases but I thought I'd give it a try.
I have three tables which for simplicity I will call A, B and C. Table A contains my master records, Table B contains user details and the final table contains some extra data
In my initial search when joining A and B, I return 100 records. I then need to search in table C for these 100 records based on a criteria. the expected result should return all 100 rows for the ones that match and also the ones that do not match. The problem is that in Table C, not all the 100 IDs exist, so there will not be a corresponding record. Unfortunately, our users still want to see all 100 records in the output. Is this possible
As always any help or direction would be appreciated.
Case: Exporting Report to PDF/Printing/TIFF Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
Okay I've posted something like this in another area but it doesn't seem to be focused towards the right people. I need ya'll SQL experts! I have a database table that has 5 columns: Time Status Message Source IP The Status column has only two choices "up" or "down" I'm looking for some kind of advanced query that will do the following: I need the query in my application to pull the table and then match up IP, Status, and Message columns. It should only find One match and then once it has found a match it needs to recognize that one is an "up" status and one is a "down" status and then remove them from the database. Does that make sense? Is it impossible?
Hi, This is a where clause I am using in a search. WHERE (ADDRESS_STREET LIKE '%' + @Search + '%' ) I am trying to do a search which returns the most matching record. For example if I have a record with Denver as text . If I search for Denvr the spell error is intended , I will not get the result. How can I create a stored procedure to counter probable spelling errors and return matching results in a ranked order. Thanks
say I have a list from an sql statement (results list) this list contains 10 items
In another table, in one particular column - there is a match for one of these items from the initial list.
SO... this may be the list _____________________ itemnumber 1 2 3 4 5 6 7 8 9 10 ------------------------------
in the other table there is a match... but just for one item on that list. ____________________ othertablefield 11 13 14 3 <------ match 99 78 ----------------------------
Dear all, I have two table, both table have a col which stored the data in CSV format, ie "ab , bc, de", etc I would like to select the item where "any one item" in first table is same as "any one item" on second table. I have alread have the private CSV function which can convert the string and return as table. However, I find i cannot do it like this select ..... where dbo.CSVfunction(tableA.colA) Thx//CSV functiondeclare @separator char(1) set @separator = ',' declare @separator_position int declare @array_value varchar(1000) set @array = @array + ',' while patindex('%,%' , @array) <> 0 begin select @separator_position = patindex('%,%' , @array) select @array_value = left(@array, @separator_position - 1) select @array_value = RTrim(@array_value) select @array_value = LTrim(@array_value) Insert @StringTable Values (Cast(@array_value as varchar(50))) select @array = stuff(@array, 1, @separator_position, '') endre @separator char(1) set @separator = ',' declare @separator_position int declare @array_value varchar(1000) set @array = @array + ',' while patindex('%,%' , @array) <> 0 begin select @separator_position = patindex('%,%' , @array) select @array_value = left(@array, @separator_position - 1) select @array_value = RTrim(@array_value) select @array_value = LTrim(@array_value) Insert @StringTable Values (Cast(@array_value as varchar(50))) select @array = stuff(@array, 1, @separator_position, '') end
My company is going to start a Cancel web site so customers can cancel their future orders by simply filling out a form. I was wondering what would be the best way to compare the cancel db to db that is used to store customer information and order information.
The cancel website will only hold the name, address, and Credit Card Number used. There will not be a customer/order number. The simple Credit Card to Credit Card search will be easy but I was more worried about comparing the rest of the information in case the Credit Card search fail. I was told the "standard" look up was to take the first 3 letters of the first name and the last 3 letters of the last name. If that comes up with more then one record, take that set and see if the zip code matches up and/or part of the street address matches up (counting we do not have very reliable people inputting the information into the system is not going to help this search).
Anyway, any suggestions on comparing the data would be great.
Hi everyone. I'm developing a web search engine using asp and SQL Server 2000. I need to return records that matches with a string entered by users. In example, suppose my database to have this structure and it's filled like this:
ID NAME KEYWORDS --- ------- ----------- AA025 NAME1 attached, atic, common, business, hotels AA026 NAME2 headache, medicin, aspirins, heat, health AA027 NAME3 at, services, music, electronics
suppose that user enters 'at'. By now, i'm using this pattern '%<input_text>%'. So in this example, pattern would be '%at%'. As i remarked in the fields above, the three fields matches, and that's not what i'm looking for. I want that the result of that query be just the last field, with 'AA027' ID. Thanks in advance guys. Cheers.
I'm getting the following error when running dbcc checkdb and no clue as to what to do.
Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=1328111872,referenced_minor_id=5) of row (class=0,object_id=1795589535,column_id=0,referenced_major_id=1328111872,referenced_minor_id=5) in sys.sql_dependencies does not have a matching row (object_id=1328111872,column_id=5) in sys.columns.
What can be done with Sql Server for this problem?
In this query:
select A, B, C from (select A, B, C from table1) as G1, (select A, B, C from table2) as G2
where G1.A = G2.A and G1.B = G2.B and G1.C = G2.C
a record in table1 can be matched with more than one record in table2, and I don't have any other field in the tables that helps me link one G1 record to a specific G2 record. What can I do to match each G1 record to only one (if any) G2 record (if there are two records in table2 which are identical, I want the query to choose one of them and leave the other one unmatched)? Another question: is there a way to count records and attribute them a unique number? What is an index?
hi allI want to write a sotred procedure which has a parameter,called@name.The SQL statement must return all rows where this parameter is asubatring of a special column called FirstName.This procdeure do thesame ,but return all rows where @name is the exact matching.whatshould I do?/*This procedure return the exact matching*/CREATE PROCEDURE substring@name varchar(50)ASSELECT *FROM table1WHERE (table1.firstName LIKE @name);
When I write the above query, I want to exclude the AddIDs that have a Name_Full of DAVIELT. In the Name_Full column the only difference between the two strings is the "T" in DAVIELT.
Please note that it has to use a LIKE statement. The code is already written that way and I do not have control over it.
We are in initial phase of designing an application whose primary purpose is to match a list of loan defaulters with bank's customer database.
Let me make the picture more clearer to you,
There is a list of defaulters which contains about 1500000 names (Primary names and Aliases e.t.c), related to a person.
On the other hand there is database which contains bank's customer's (about 20, 00000 records) data.
We will have to run batch a scan for name matching against defaulters list to the current customer base of bank to check if any of existing customer is in defaulter's list.
This whole exercise of name matching should be finished in 4 to 5 hours (as required by user/client).
How can I optimize my data base design or processing logic to achieve this goal,
Should I use full text query or I should load data related to names in memory than carry out some logic for name match. These are two my primary thoughts.
I have 2 tables called Customer & CustomerArchive. Both Tables have the same Column names and Datatypes etc.
CustomerID Address Postcode
1 10 Alcove Street 4000
50 21 Spring Street 4032
33 5 Terrace Place 4010
What I need to do is compare each column except for the CustomerID to see if it is the same!
In other words, I need to compare Address in the Customer Table with Address in the CustomerArchive Table.
I have to do this with all columns (12) of them between the 2 Tables.
Is there a efficient & smart way to do this aprt from the obvious way of joing the 2 Tables via the CustomerID and performing a Customer.Address <> CustomerArchive.Address Customer.PostCode <> CustomerArchive.PostCode etc etc etc!
I'm working with Access 2,007, and I have an instruccion in SQL but when I execute it, it shows an error "" the instruccion and var in red
Dim Mes As String Dim CostoKw As Double Dim SQLUdate As String Mes = InputBox("Mes a Actualizar Costo: ") CostoKw = InputBox("Costo que se Aplicara: ") Select Case Mes Case 1 DoCmd.RunSQL "UPDATE Clientes SET Clientes.Costo01;" = CostoKw
The table's field Costo01 is the type Number and Double.
Init SC --- 89 Post NCOA --- 89 Post Supp --- 89 Revised Final State Counts --- 89 Revised Final State Counts --- 94 ***********************************************************************************************
Since "Revised Final State Counts" appears in both cycles 89 & 94. How can I query the table so that I only get that 1 record?
Hi folks I have a section in my networking site where people can write things about themselves, such as likes dislikes, hobbies etc for their profile. I am looking to provide a very basic profile matching service, and have done a bit of searching to find out what the best way to approach this feature is. I have looked at using the sql LIKE clause, but since the profile information can be any length, there seem to be too many variables to account for. I have also looked at identifying keywords in profile blurbs to do the matching too, but I am struggling to see how this could be done with relative ease. My reason for posting this is simply to get some experienced knowledge about where to start the problem, of even some resources to look at that I may not have found yet. If anyone could give me some advice on where to start i would be very grateful I am develping in vb, with an sql server 2005 back-end
A column in my database contains phrases such as "Extreme Golf: The Showdown" or "Welcome to Happy Land". I need to write a search engine so that users could type in phrases such as "Golf Extreme Showdown" or "Happy Land" and the correct, or closest matched results will be returned. I don't need variations of words, just phrase keyword match based search. I know I could do this by using multiple LIKE %% statements OR'd together, but this would be too performance intensive. So, I have heard I should use charindex somehow to achieve this in a stored procedeure. Does anyone have any clue how to solve this problem? Thanks!
Hi, i am facing a scenario is that, iam matching table one table form oracle and another table form sql what is want is that using this two tables i want to display unmatched record from the orcale table very urgent help me please send the code for this scenario example in sql table i have fldinvoiceno 00000001 00000002 in oracle table i have fldinvoiceno 00000001 00000002 00000003 so matching the two table i need to get 00000003
Hey all, Got a little problem. have 2 matching tables on different servers with the EXACT same column layout and data (the tables are being replicated with MSSQL7) and one table is 200MB while the other is 2000MB. I'm running MSSQL7 SP2. Any ideas???
i have one task in which i have to match some attributes(required for creating a new databse) with the exiting database, are these attributes present in exisisting database, if yes how many , and how many are not,pls do reply
