Fuzzy Lookup and Case

Hi,

Could someone please help!

Im doing a fuzzy lookup based on 3 fields (Surname/DOB/Gender). The only difference between the two sets of data is the case of the first letter of the Surname.

Reference table has "Stuart" Lookup has "stuart", I have set Fuzzy Lookup Input for Surname to Ignore Case but still it won't match.

The DOB/Gender are Exsactly the same.

Why does this not work? I there a work around?

Many Thanks, Deano

This isn't a "fuzzy" workaround, but for the scenario you describe it may well work.

Try adding explicitly lower-cased versions of your comparison columns to your data flow source and to the lookup query. Use the standard Lookup transformation, with the lower-cased columns being used for the lookup matching. This will allow you to perform a case-insensitive match without the overhead of the Fuzzy Lookup transformation. If there is a match, send the rows down your data flow path. If there is not, send the rows down an error path, and then use the Fuzzy Lookup only on the error rows.|||No, still not working, I have looked at the data and converted them all to they same type (Unicode String/ String etc both now varchar(50) ) and they still don't match up .It works if I group all the datain one import but if import one of the records into the database then try and import the other it does not match (Same logic). I can't help but thing it is a data type issue but I can't see any difference.

Someone please help!

further to problem with obtaining first/last occurance of contiguous blocks of data

Hi again, I was hoping someone could help me create a sql query to
minimizing the content in my table based on a few rules.
Further to my previous post...
f2b9da63" target="_blank">http://groups.google.com.au/group/m...br />
f2b9da63
... I have decided to use triggers to process my table (see trigger solutio
n
in my previous post). However since triggers are slow at processing, I aim
to search for a solution to minimize my initial table structure so the
triggers have less data to work with (hence process it much quicker). What I
am looking for is a method to perform the following:
This is my initial table (as an example):
[system] [date] [isOn]
A 01 0
A 04 1
A 05 1
A 06 0
A 20 1
A 21 0
A 25 0
A 27 1
A 32 1
A 33 1
A 34 0
A 40 1
B 41 1
B 45 0
B 49 1
B 50 1
B 51 1
B 53 1
B 67 0
I want my final table to look like this:
[system] [date] [isOn]
A 01 0
A 04 1
-
A 06 0
A 20 1
A 21 0
-
A 27 1
-
-
A 34 0
A 40 1
B 41 1
B 45 0
B 49 1
-
-
-
B 67 0
... where i have placed a '-' sign to indicate the rows i need deleted
I am deleting rows under the following conditions:
1. for a contiguous block of 1's WITHIN the same client range, delete all
but the first one ( i.e.the one at the earliest date)
2. for a contiguous block of 0's WITHIN the same client range, delete all
but the first one (i.e. the one at the earliest date)
i.e. notice above that even though there is a contiguous block of 1's from
date = 40 to 41, I have not remove the 2nd '1' as that crosses into client
B.
After this table is thus processed, I can use my cursor on it.
Any help would be really appreciated!
many thanks
wileyI have scrapped my cursor implementation for a much faster set-based
approach put forward by Itzik Ben-Gan (as im my previous post). Thanks
everyone!
cheers
wiley
"wiley" <wiley@.nospam.com> wrote in message
news:uK5SI4SFGHA.216@.TK2MSFTNGP15.phx.gbl...
> Hi again, I was hoping someone could help me create a sql query to
> minimizing the content in my table based on a few rules.
> Further to my previous post...
> 29f2b9da63" target="_blank">http://groups.google.com.au/group/m... />
29f2b9da63
> ... I have decided to use triggers to process my table (see trigger
> solution in my previous post). However since triggers are slow at
> processing, I aim to search for a solution to minimize my initial table
> structure so the triggers have less data to work with (hence process it
> much quicker). What I am looking for is a method to perform the following:
> This is my initial table (as an example):
> [system] [date] [isOn]
> A 01 0
> A 04 1
> A 05 1
> A 06 0
> A 20 1
> A 21 0
> A 25 0
> A 27 1
> A 32 1
> A 33 1
> A 34 0
> A 40 1
> B 41 1
> B 45 0
> B 49 1
> B 50 1
> B 51 1
> B 53 1
> B 67 0
> I want my final table to look like this:
> [system] [date] [isOn]
> A 01 0
> A 04 1
> -
> A 06 0
> A 20 1
> A 21 0
> -
> A 27 1
> -
> -
> A 34 0
> A 40 1
> B 41 1
> B 45 0
> B 49 1
> -
> -
> -
> B 67 0
> ... where i have placed a '-' sign to indicate the rows i need deleted
> I am deleting rows under the following conditions:
> 1. for a contiguous block of 1's WITHIN the same client range, delete all
> but the first one ( i.e.the one at the earliest date)
> 2. for a contiguous block of 0's WITHIN the same client range, delete all
> but the first one (i.e. the one at the earliest date)
> i.e. notice above that even though there is a contiguous block of 1's from
> date = 40 to 41, I have not remove the 2nd '1' as that crosses into client
> B.
> After this table is thus processed, I can use my cursor on it.
> Any help would be really appreciated!
> many thanks
> wiley
>

functions and procedures

Dear Experts,
I'm working for aproduct based company, i need guidence from you in some respects

1) how to become expertise in functions and stored procedures?

is there any good links for me, i'm a learner.of cource google is there, but i dont know the starting point.please provide me some good links, and your esteemed guidenceHai friend

I hope this link helpful for u
http://www.informit.com/guides/cont...&seqNum=56&rl=1|||let mi start by saying im a stored procedure envangelist, lover n guru...so u can direct any problem or tots in ds direction to mi personally...

Stored Procedures are precompiled functions that encapsulate some basic fxns...pretty much like functions in regular procedural languages like vb e.t.c; but its compiled once...n runs straight(faster) when called...optimizes network traffic

Basically u pass parameters(or list of parameters) to a function; it does some work n returns a result(optionally)...it could return just about anything...a number, string or query...its hard!

lets create something very simple...a stored procedure to calculate area of a rectangle..

d basic structure or framework of a stored procedure is as follows:

create proc procedurename
{list of parameters n their data types passed to d stored procedure}
as
{body of procedure}

now...d 'area stored procedure'

create proc area
@.length int, @.breadth int
as
-- @.result is a local variable
declare @.result as int
set @.result = @.length * @.breadth
select @.result as [Area of a Rectangle]

its dat simple...copy d code above n paste unda ur database in query analyser n press f5...

on a fresh window...test d procedure using d following:
exec area 5,4

.................................
its dat simple.....let mi no if u av problems running ds or if u av questions...

i dont particularly work wit functions...but i no its ds straight to

be good...|||Hello seun
its great fro me to meet a guru.i'm very happy with your words.i've started learning stored procedures, perticularly, when i get a doubt, i'm expecting help fromyou.

function index

I have implemented function based indexes on tables in Oracle database. I want to implement same in SQL Server. Is there something similar to function index in SQL Server also? If yes, please explain as how I can do this.
Thank,
dcExplain more clearly what you want to do.|||You have to create a computed column with the function you want indexed, then index the computed column. Then you have to change all of your applications to query the new column. Simple, eh?

In short, Function Based Indexes don't really exist in SQL Server, but they are pretty nice things to have.

create index ind on table (upper(name))|||Function based indicies (where the indexed value is the result of one or more function calls) were the most dearly missed feature of Clipper when I converted to SQL Server. They are lovely things when you don't have to deal with most of the complexities that a real relational database engine is intended to solve!

Unfortunately, they don't scale, introduce all kinds of "functional dependancies", lead to really interesting collation questions, etc. A Function Based Index is certainly convenient in many ways, but it is a scaling/supportability nightmare in search of its next victim!

-PatP

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
Tom
It would help a lot if we could see this "function". Is it really a
function or a stored procedure?
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>
|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:

> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
TomIt would help a lot if we could see this "function". Is it really a
function or a stored procedure?
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:

> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement
.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
TomIt would help a lot if we could see this "function". Is it really a
function or a stored procedure?
--
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

function based index

Does SQL Server 2000 support function-based index?
Such as:
create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
Not directly.
But if your function is deterministic, you can create a computed column:
ALTER TABLE YourTable
ADD YourComputedColumn AS FLOOR(DECIMAL_READING);
... and then you can index it:
CREATE INDEX ComputedColumnIndex
ON YourTable(YourComputedColumn)
This won't quite be the same as a function-based index, as the index won't
be used for a query like:
SELECT *
FROM YourTable
WHERE FLOOR(DECIMAL_READING) = 10
... but you don't need to use the function at all, you can just do:
SELECT *
FROM YourTable
WHERE YourComputedColumn = 10
... And that _will_ use the index.
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
|||Bevo wrote:
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
You can create an index on a computed column, but I'm not sure you can
specify functions in the create index statement.
See the section on "Considerations when indexing computed columns and
views" in the "CREATE INDEX" page of BOL.
So you could do something like this:
create table #test (col1 char(10), col2 as left(col1, 4))
create index test2 on #test(col2)
David G.
|||Thanks for the post, but, unfortunately I cannot modify the table structure.
"Adam Machanic" wrote:

> Not directly.
> But if your function is deterministic, you can create a computed column:
> ALTER TABLE YourTable
> ADD YourComputedColumn AS FLOOR(DECIMAL_READING);
> ... and then you can index it:
> CREATE INDEX ComputedColumnIndex
> ON YourTable(YourComputedColumn)
> This won't quite be the same as a function-based index, as the index won't
> be used for a query like:
> SELECT *
> FROM YourTable
> WHERE FLOOR(DECIMAL_READING) = 10
> ... but you don't need to use the function at all, you can just do:
> SELECT *
> FROM YourTable
> WHERE YourComputedColumn = 10
> ... And that _will_ use the index.
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
>
>
|||You might also create an indexed view and do the same thing... Search for
indexed views in books online.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));

function based index

Does SQL Server 2000 support function-based index?
Such as:
create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));Not directly.
But if your function is deterministic, you can create a computed column:
ALTER TABLE YourTable
ADD YourComputedColumn AS FLOOR(DECIMAL_READING);
... and then you can index it:
CREATE INDEX ComputedColumnIndex
ON YourTable(YourComputedColumn)
This won't quite be the same as a function-based index, as the index won't
be used for a query like:
SELECT *
FROM YourTable
WHERE FLOOR(DECIMAL_READING) = 10
... but you don't need to use the function at all, you can just do:
SELECT *
FROM YourTable
WHERE YourComputedColumn = 10
... And that _will_ use the index.
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));|||Bevo wrote:
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
You can create an index on a computed column, but I'm not sure you can
specify functions in the create index statement.
See the section on "Considerations when indexing computed columns and
views" in the "CREATE INDEX" page of BOL.
So you could do something like this:
create table #test (col1 char(10), col2 as left(col1, 4))
create index test2 on #test(col2)
David G.|||Thanks for the post, but, unfortunately I cannot modify the table structure.
"Adam Machanic" wrote:

> Not directly.
> But if your function is deterministic, you can create a computed column:
> ALTER TABLE YourTable
> ADD YourComputedColumn AS FLOOR(DECIMAL_READING);
> ... and then you can index it:
> CREATE INDEX ComputedColumnIndex
> ON YourTable(YourComputedColumn)
> This won't quite be the same as a function-based index, as the index won't
> be used for a query like:
> SELECT *
> FROM YourTable
> WHERE FLOOR(DECIMAL_READING) = 10
> ... but you don't need to use the function at all, you can just do:
> SELECT *
> FROM YourTable
> WHERE YourComputedColumn = 10
> ... And that _will_ use the index.
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
>
>|||You might also create an indexed view and do the same thing... Search for
indexed views in books online.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));

function based index

Does SQL Server 2000 support function-based index?
Such as:
create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));Not directly.
But if your function is deterministic, you can create a computed column:
ALTER TABLE YourTable
ADD YourComputedColumn AS FLOOR(DECIMAL_READING);
... and then you can index it:
CREATE INDEX ComputedColumnIndex
ON YourTable(YourComputedColumn)
This won't quite be the same as a function-based index, as the index won't
be used for a query like:
SELECT *
FROM YourTable
WHERE FLOOR(DECIMAL_READING) = 10
... but you don't need to use the function at all, you can just do:
SELECT *
FROM YourTable
WHERE YourComputedColumn = 10
... And that _will_ use the index.
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));|||Bevo wrote:
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
You can create an index on a computed column, but I'm not sure you can
specify functions in the create index statement.
See the section on "Considerations when indexing computed columns and
views" in the "CREATE INDEX" page of BOL.
So you could do something like this:
create table #test (col1 char(10), col2 as left(col1, 4))
create index test2 on #test(col2)
David G.|||Thanks for the post, but, unfortunately I cannot modify the table structure.
"Adam Machanic" wrote:
> Not directly.
> But if your function is deterministic, you can create a computed column:
> ALTER TABLE YourTable
> ADD YourComputedColumn AS FLOOR(DECIMAL_READING);
> ... and then you can index it:
> CREATE INDEX ComputedColumnIndex
> ON YourTable(YourComputedColumn)
> This won't quite be the same as a function-based index, as the index won't
> be used for a query like:
> SELECT *
> FROM YourTable
> WHERE FLOOR(DECIMAL_READING) = 10
> ... but you don't need to use the function at all, you can just do:
> SELECT *
> FROM YourTable
> WHERE YourComputedColumn = 10
> ... And that _will_ use the index.
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> > Does SQL Server 2000 support function-based index?
> >
> > Such as:
> >
> > create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
>
>|||You might also create an indexed view and do the same thing... Search for
indexed views in books online.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));