Although this SP intends to sorround a search text in double quotes, it seems that when called from Management Studio it throws a Syntax Error even before entering the SP.
createproc fts(@t nvarchar(1000)=null) as begin
select @t = '"' + @t + '"'
select @t
select * from dbo.products where CONTAINS(name, @t)
end
GO
exec fts @t = 'my product name'
GO
Msg 7630, Level 15, State 3, Procedure fts, Line 4
Syntax error near 'product' in the full-text search condition 'my product name'.
-------------------------
If I pass the string in double quotes I get a different error:
exec fts @t = '"my product name"'
Go
Msg 7630, Level 15, State 3, Procedure fts, Line 4
Syntax error near 'my' in the full-text search condition '""my product name""'.
Now, if I remove the quotes again and make the original call:
I have a customer who wants to SELECT records based on a partial match in atext field. For example, in a list of telephone numbers they want to searchfor all records that contain the digits '777' in any part of the string. Howdo I formulate such a query?Many thanks.
Hi All,I need to link two tables using partial word match.How can I write a SQL statement to do so? (I am using MS-Access.)Table One: [Table Name: tblStreet] [Field Name: Street]123 ABC Street124 ABC Street125 ABC Street1 XYZ AVE2 XYZ AVE3 XYZ AVE10 CBS Street11 CBS Street12 CBS Street100 Apple Road101 Apple Road102 Apple RoadTable Two: [Table Name: tblWord] [Field Name: Word]ABCCBSThe output should be:123 ABC Street124 ABC Street125 ABC Street10 CBS Street11 CBS Street12 CBS StreetCould this be done?Thank you in advanced.- Grasshopper -
Hi Alex (Kuznetsov) and All,This is to follow up with my last post, "Link two tables using partialword match".How can I UPDATE table using partial word match?How can I write a SQL statement to do so? (I am using MS-Access.)The two tables and the expected result are listed below:Thank you in advance!- Grasshopper -================================================== =====TABLE: tblStreet_Value (Table to be undated)StreetValue2---------------------123 ABC Street124 ABC Street125 ABC Street1 XYZ AVE2 XYZ AVE3 XYZ AVE10 CBS Street11 CBS Street12 CBS Street100 Apple Road101 Apple Road102 Apple RoadTABLE: TblWord_NumberWordNumberValue---------------ABC123NYABC125CACBS11MACBS12TXResultStreet Value2---------------------123 ABC Street NY124 ABC Street125 ABC Street CA1 XYZ AVE2 XYZ AVE3 XYZ AVE10 CBS Street11 CBS Street MA12 CBS Street TX100 Apple Road101 Apple Road102 Apple Road
SQL Server Data Mining comes with "Term extraction" and "Term Lookup" for phrase detection. Rather than using the GUI tool, how to utilize these two features in coding? Please assist! Thanks!
Hi, I am new to SSRS. Is there a way to show part of a report based on User. Say we have a report consisting data for all department. When a department director view it, the report only display data for his/her department. Can this be achieved in SSRS? Thank you in advance for your help.
I am designing a ssis package,This is intends to mine text data(Data extracted from websites). Term lookup/Term extraction has been used as tools for mining. I have lookup terms defined with me for reference table,but the main problem lie in extracting the nearby text/number/charcters to these lookup terms during mining. For example : I found noun "Email" 200 (frequency score) times in my text,Now I want to extract nearby email address(this is also true for PhoneNumber,Address attributes also).so how can I achieve this with SSIS. If u have some idea/suggestion to carry out this challenge with or without Term Extraction/Term Lookup,plz do write here.
Is there anyway to get the order in which data to be import on to tables when they have primary and Foreign Key relations?
For ex:We have around 170 tables and when tries to insert data it will throw error stating table25 data should be inserted first when we insert data in table 25 it say 70 like that.
I have small requirement in my project. I need to display the results of the WHERE clause based on percentage/ranking of exact match.
I mean the result set should be displayed based on percentage match.
For example i have the below table.
create table test ( id int identity(1,1) primary key, ename varchar(10) )
insert into test(ename) select 'REG' insert into test(ename) select 'xyz' insert into test(ename) select 'abc' insert into test(ename) select 'Reg' insert into test(ename) select 'Regsxysn' insert into test(ename) select 'psReg'
I need the output something similar as below
REG Reg Regsxysn psReg
I have tried out with full text indexing but i could'nt get the required output.
I have small requirement in my project. I need to display the results of the WHERE clause based on percentage/ranking of exact match.
I mean the result set should be displayed based on percentage match.
For example i have the below table.
create table test ( id int identity(1,1) primary key, ename varchar(10) )
insert into test(ename) select 'REG' insert into test(ename) select 'xyz' insert into test(ename) select 'abc' insert into test(ename) select 'Reg' insert into test(ename) select 'Regsxysn' insert into test(ename) select 'psReg'
I need the output something similar as below
REG Reg Regsxysn psReg
I have tried out with full text indexing but i could'nt get the required output.
I'm trying to do some analysis on duplicate records based off of several match keys. I have a data set of approximately 30,000 people and the goal is to determine how many duplicate matches are in the system.
How would I write an SQL statement that looks for the following pieces of information. (I'm not using one person as an example; I need to do an analysis on the entire data set)
First name (exact match) Last name (exact match) Address line 1 (exact match) Postal code/zip (exact match)
First Initial (exact match) Last name (exact match) DOB exact match Postal code/zip (exact match)
I'd like to get a extract table result, with a reference id primary key, showing the maximum dates for events and who was responsible for them. I can get the max(date) field in columns using PIVOT, but can't see a way to get the 'who' field without lots of LEFT JOINs.
Here's some test data and code which shows the principle:
CREATE TABLE #t ( ref INT , id INT , who VARCHAR(10) , checkin DATE
[Code] ....
The result set is:
ref 1 who1 2 who2 3 who3 4 who4 123 2014-01-18 carol 2014-01-18 andy 2014-01-16 bill 2014-01-17 carol 456 NULL NULL 2014-01-17 NULL NULL NULL NULL NULL
Is there some way to avoid all the LEFT JOINs, maybe by using another PIVOT, to produce the same result?
If an Account's value reaches 20 for StatusID = 1 and 40 for StatusID = 2, that is a called "Good". I want to find out which order made the Account become "Good".
By looking at the data, it is understandble that AccountID 1 crossed Status ID 1's limit of 20 with order 2, but the status ID 2's limit was only crossed after the 6th order was placed. So my output should show 6 for AccountID 1.
For AccountID 2, value of statusID 1 was 5 with orderid 3, but it reached the limit for status id 2 of 40 with order 4. But the first condition was not met. so it shouldn't be seen in the output.
Same with AccountID 3 as well, It reached the limit of status id 1 with order 5 but the limit for order 2 wasn't reached so it should be ignored as well.
I wrote the code as below, its working fine but I still know there are better ways to write since I will be working with atleast a million records.
;WITH CTE AS ( SELECT OrderID,AccountID, StatusID, SUM(Value) OVER(Partition By AccountID, StatusID ORDER BY OrderID) AS RunningTotal FROM @Input
I work for a school district and new requirement we were just given for scoring some student scores.
Everything will currently be in one table to keep track of students test scores for various things. This table will contain students information and a student will be shown more than once in the table. The Student ID is what we will key off of to find the multiple instances of the student. The table contains the following columns: studentName, StudentId, teacherName, focus1, controllingIdeas1, reading1, development1, organization1, conventions1, and contentUnderstanding1. All of the columns with a 1 at the end will be numeric values with possible decimal values.
What we need to be able to do is some how perform a search for these multiple entries of each student and when found, average the 2 scores for each 7 test categories. The result needs to be a single line for each student that gives the student name, student id, and the 7 test category averages exported to an csv file.
I have a simple select query and I need to eliminate records whose values from 2 different fields match. I thought I had this working, but if one of those fields in my data IS NULL it filters out those records. If I comment out my last line then my number record shows, if I include that statement that record drops. The only thing I see in my data is the Name and PName are both NULL in the data for that particular number. Just need to filter out any records where it finds those 3 Names that also have "Default" as the PName, then include everything else even if Name or Pname is NULL.
Below is my where clause.
WHERE [DETERMINATION] <> 'Denied' AND [Number] ='A150731000039'
---- Removes incorrect records where these names match---- AND ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')
I want to join 2 tables, table a and table b where b is a lookup table by left outer join. my question is how can i generate a flag that show whether match or not match the join condition ?
**The lookup table b for column id and country are always not null values, and both of them are the keys to join table a. This is because same id and country can have multiples rows in table a due to update date and posting date fields.
example table a id country area 1 China Asia 2 Thailand Asia 3 Jamaica SouthAmerica 4 Japan Asia
example table b id country area 1 China Asia 2 Thailand SouthEastAsia 3 Jamaica SouthAmerica 5 USA America
Expected output id country area Match 1 China Asia Y 2 Thailand SouthEastAsia Y 3 Jamaica SouthAmerica Y 4 Japan Asia N
I am currently working with 3 multi-valued parameters whose data sources are queries. The first 2 are required to have entries, 100% of the time, but the third one may or may not require selecting a value. Parm3's data source is filtered by the selections of Parm1 & Parm2. The data source for my report references Parm3 in a derived table that is then LEFT OUTER JOINed.
In the cases where the report does not require any selection from Parm3 I am still required to pick at least 1 entry. Can anyone shed some light on this, or provide a solution so I am not forced to pick any if I don't want?
I deployed my application to my newly installed xp pro laptop
The prerequisites are installed
Now that I want to testdrive my database application I get this error:
An attempt to attach an auto-named database file c:/program files........................MyDB.mdf failed. A database with the same name exists, or specified file can not be opened, or it is located on UNC share.
I have a chart showing quantity against time. I had been using X-Axis in category mode, but wanted to include all dates, so changed it to Scalar mode by checking "Numeric or time-scale values".
However instead of displaying dates it simply shows integers, and to make things worse, Y-axis values have all come out as zero. It appears that the integer values are simply sequential values for each datapoint (i.e. 1,2,3,4 etc).
The category field for the X-Axis is a field called "Date" which comes from a Date Type field in the cube based on a datetime column in the DSV. The properties of the category in the chart are : Group On =Fields!Date.Value and Label =Fields!Date.Value.
I would like to generate a working schedule for employees for x-days ahead based on a starting date that the user can enter.
I have got 3 relevant tables:
1. Table X with (1) resourcenumber, (2) starting date working schedule and (3) the daynumber representing the starting date (this is ISO so 1 for Monday, 2 for Tuesday etc.)
2. Table Y has the schedule itself and can hold a 7-days schedule or a 14-days schedule. In case of 7 days schedule there a 14 (!) records with (1) resourcenumber, (2) daynumber, (3) starting hour a.m. (4) ending hour a.m (5) starting hour p.m and (6) ending hour p.m. In case of a 14-days schedule there are 28 records (a.m. and p.m. records)
3. Table Z with resource data.
An example to clarify (for fake employee 100):
Table X: Resource: 100 Starting date: 2012-03-01 (from this date the schedule will be effective) Daynumber: 4 (2012-03-01 was a Thursday)
Table Y (Resource has a 14 days schedule because per 2 weeks Monday is an off-day):
Record 1 shows: Resource: 100, Daynumber: 1 (= Monday, working day), AM-Starting hour: 09:00, AM-Ending hour: 13:00, PM-starting hour: 13:30, PM-ending hour: 17:30 Record 2: same but daynumber is 2 Record 3: same but daynumber is 3 etc. ... Record 8 shows: Resource: 100, Daynumber: 8 (= Monday, off-day), AM-Starting hour: 00:00, AM-Ending hour: 00:00, PM-starting hour: 00:00, PM-ending hour: 00:00 Record 9: same as record 2 but daynumber is 9. etc. ... Record 14: same as record 7 but day is 14 (= last day)
The weekend days show as 00:00 for the hours (same as day 8 in example)
I generated the working schedule with a CROSS APPLY function based on the starting date and the x-number of days ahead.
I then evaluate the actual daynumber corresponding with that date with the daynumber in table Y. That works fine with a 7-days schedule but I can't get it fixed with a 14-days schedule. Day 8 in that schedule represents an actual day 1 but how do I know what actual date day 8 is ... I think I have to start with the starting date in table X ...
I think ideally I would like to have the generated days as follows (as an example in case of a 14-days schedule starting 2014-05-01 for 30 days ahead):
2014-05-01 = day 4 (= actual daynumber) 2014-05-02 = day 5 2014-05-03 = day 6 ... 2014-05-10 = day 13 2014-05-11 = day 14 2014-05-12 = day 1 2014-05-13 = day 2 2014-05-14 = day 3 ... 2014-05-24 = day 13 2014-05-25 = day 14 2014-05-26 = day 1 2014-05-27 = day 2 ... 2014-05-31 = day 6
With this done I can compare the actual daynumber with the daynumber in Table Y.
The rownumber that the CROSS APPLY function generates has to be reset to 1 after day 14. I tried PARTITION BY in THE ROW_NUMBER function but to no avail ... The only field I can partition by is the maximum value of the daynumber (14 is the example) but that is not allowed in the rownumber function.
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
I downloaded the SSRS Report RDL file from the report manager.That report is consolidated report. It has 4 dataset for four tablix one for each tablix. Actually in that report, I don't want to display one of the tablix data. So I deleted the tablix and related dataset.
Then I upload that rdl file in to the report manager.Here I haven't change any connection strings but when I click on the report from the report manger, it is not showing any thing
I am trying to move files to directories based on the file status. If the file contains the Flag='E' then I want to move it to an "Error" folder, otherwise it will go to a "Processed" folder.
Here is my current code:
DECLARE @cmd varchar(500) SELECT CASE WHEN Processed_Flag = 'E' THEN SET @cmd='MOVE /Y C: empmyfiles*.dat C: empmyfilesError' ELSE SET @cmd='MOVE /Y C: empmyfiles*.dat C: empmyfilesProcessed' END FROM#TEMP
what use reason of 'weighted-term' ?explain it. SELECT ID, firstname, lastnameFROM [contain-1]WHERE CONTAINS(firstname, 'ISABOUT(mohsen weight(.8),yaser weight(1.0))') table [contain-1] information: ID FIRSTNAME 1 mohsen 2 mohsen 3 yaser 4 mehdi
I've just started using the SSIS and i would like to know if it's possible to change or update the dictionary of the term extraction tool. That's important to me because i may have to look for words that don't exist in the defaut english dictionary of the tool.
I have a clustered index which shows as having a fragmentation level of 66% according to sys.dm_db_index_ physical_ stats.avg_ fragmentation_ in_ percent.
But no matter what I try the fragmentation level doesn't budge. And yes I'm updating the statistics after each attempt.Its not a huge issue the table only has 348 records. I'm testing a fixing fragmentation maintenance script. In Ironing out the syntax of my script I've fixed the fragmentation of indexes of over 65 % anyway..I've come across and index that I can't defragment. I've tried
ALTER INDEX ALL ON [GRIDINFO] REBUILD WITH (FILLFACTOR = 90)
The command complete successfully yet the avg_fragmentation_in_percent doesn't change. The table also has a nonclustered index. I've gleaned through all the statements of 'this will have no effect if' but so far I've not spotted a reason why this index won't defrag.The script has been modified to remove schema and database names for the forum.
dbcc showcontig('GRIDINFO','IDX_GRIDINFO1') with tableresults , all_levels go DBCC SHOWCONTIG ('GRIDINFO') go sp_helpindex 'GRIDINFO'
I have a table where I need to concatenate and sum based on conditions given below .
CREATE TABLE #DimOrder ( [ID] [INT] IDENTITY(1,1) NOT NULL, [OrderID] [INT] NOT NULL, [SalesRegion] [VARCHAR](5) NULL, [OrderType] [INT] NOT NULL,
[Code] ....
Example SELECT 1296 AS OrderID, 2547 AS ProjectID,364 AS ProductID,6 AS FormatID,66 AS MEdiaTypeID,'Fast Track,Fast Track' AS ProductComment,'sq mi' AS UOM,'Y' AS FormatName,'LTO4_800' AS MEdia ,41.67000 AS aPPLIEDRATE
IF OrderID,PrjectID,ProductID,FormatID,MediaTypeID AND AppliedRate AND ProductComment IS different , THEN I want TO concetenate ProductComment AND sum AppliedRate
Example SELECT 1207 AS OrderID, 2351 AS ProjectID,364 AS ProductID,6 AS FormatID,32 AS MEdiaTypeID,'no charge,final volume' AS ProductComment,'sq mi' AS UOM,'Y' AS FormatName,'3590E' AS MEdia ,55.56000 AS AppliedRate
Hi, Can you search a column of a database table to find all the rows that have a wor in it? example: I have a row that contains 'adventure st north', there are other columns in that table that are suppose to be the same but read 'adventure street N.' or 'adventure st. N.' or 'North Adventure st.' could I search for rows that contain 'adventure' in the column searched (lets call it columnA). I tried: select * from tbl_test where columnA LIKE 'adventure' and got no results. what is the way to do this?
