June 1, 2006 at 9:31 am
I've got a couple of tables:
FinCenPersonal Table
ID bigint
tracking_number varchar(6)
last_name varchar(MAX)
first_name varchar(MAX)
middle_name varchar(MAX)
suffix varchar(MAX)
alias_last_name varchar(MAX)
alias_first_name varchar(MAX)
alias_middle_name varchar(MAX)
alias_suffix varchar(MAX)
number varchar(MAX)
number_type varchar(MAX)
dob varchar(MAX)
street varchar(MAX)
city varchar(MAX)
state varchar(MAX)
zip varchar(MAX)
country varchar(MAX)
phone varchar(MAX)
Checks Table
CheckDate datetime
Payee varchar(MAX)
PurchasedBy varchar(MAX)
Amount decimal(18, 2)
Address varchar(MAX)
Reference varchar(MAX)
NonCustInfo varchar(MAX)
Initials varchar(5)
PersonLoggedIn varchar(MAX)
Computer varchar(MAX)
PrintLocation varchar(MAX)
NonCustomerTransaction varchar(3)
PrintTime datetime
I'm trying to find matches in the two tables for terrorist checks. I'm looking at the last_name field in the FinCen table and comparing it to the Checks.Payee, checks.Pruchasedby and Checks.Address to see if any of the three contain the text in the FinCen table.
I also need to check if the values in the finCen table are ='' and not select any of them.
So far this is what I've got, but of course (I'm here arent' I) its not working.
SELECT FinCenPersonal.ID, FinCenPersonal.tracking_number, FinCenPersonal.last_name, FinCenPersonal.first_name, FinCenPersonal.middle_name,
FinCenPersonal.suffix, FinCenPersonal.alias_last_name, FinCenPersonal.alias_first_name, FinCenPersonal.alias_middle_name,
FinCenPersonal.alias_suffix, FinCenPersonal.number, FinCenPersonal.number_type, FinCenPersonal.dob, FinCenPersonal.street, FinCenPersonal.city,
FinCenPersonal.state, FinCenPersonal.zip, FinCenPersonal.country, FinCenPersonal.phone, Checks.CheckDate, Checks.Payee, Checks.PurchasedBy,
Checks.Amount, Checks.Address, Checks.Reference, Checks.NonCustInfo, Checks.Initials, Checks.PersonLoggedIn, Checks.Computer,
Checks.PrintLocation, Checks.NonCustomerTransaction, Checks.PrintTime
FROM FinCenPersonal INNER JOIN
Checks ON FinCenPersonal.last_name IN
(SELECT Payee
FROM Checks) OR
FinCenPersonal.last_name IN
(SELECT PurchasedBy
FROM Checks) OR
FinCenPersonal.last_name IN
(SELECT Address
FROM Checks) OR
FinCenPersonal.first_name IN
(SELECT Payee
FROM Checks) OR
FinCenPersonal.first_name IN
(SELECT PurchasedBy
FROM Checks) OR
FinCenPersonal.first_name IN
(SELECT Address
FROM Checks) OR
FinCenPersonal.alias_last_name IN
(SELECT Payee
FROM Checks) OR
FinCenPersonal.alias_last_name IN
(SELECT PurchasedBy
FROM Checks) OR
FinCenPersonal.alias_last_name IN
(SELECT Address
FROM Checks) OR
FinCenPersonal.alias_first_name IN
(SELECT Payee
FROM Checks) OR
FinCenPersonal.alias_first_name IN
(SELECT PurchasedBy
FROM Checks) OR
FinCenPersonal.alias_first_name IN
(SELECT Address
FROM Checks) OR
FinCenPersonal.number IN
(SELECT Reference
FROM Checks) OR
FinCenPersonal.street IN
(SELECT Address
FROM Checks)
help
thanks
Chuck Sndyer
June 1, 2006 at 10:14 am
You don't need all the subselects and ins. That will perform horribly.
Try this.
SELECT FinCenPersonal.ID, FinCenPersonal.tracking_number, FinCenPersonal.last_name, FinCenPersonal.first_name, FinCenPersonal.middle_name,
FinCenPersonal.suffix, FinCenPersonal.alias_last_name, FinCenPersonal.alias_first_name, FinCenPersonal.alias_middle_name,
FinCenPersonal.alias_suffix, FinCenPersonal.number, FinCenPersonal.number_type, FinCenPersonal.dob, FinCenPersonal.street, FinCenPersonal.city,
FinCenPersonal.state, FinCenPersonal.zip, FinCenPersonal.country, FinCenPersonal.phone, Checks.CheckDate, Checks.Payee, Checks.PurchasedBy,
Checks.Amount, Checks.Address, Checks.Reference, Checks.NonCustInfo, Checks.Initials, Checks.PersonLoggedIn, Checks.Computer,
Checks.PrintLocation, Checks.NonCustomerTransaction, Checks.PrintTime
FROM FinCenPersonal
INNER JOIN Checks
ON FinCenPersonal.last_name = Checks.Payee
OR FinCenPersonal.last_name = Checks.PurchasedBy
OR FinCenPersonal.last_name = Checks.Address
OR FinCenPersonal.first_name = Checks.Payee
OR FinCenPersonal.first_name = Checks.PurchasedBy
OR FinCenPersonal.first_name = Checks.Address
OR FinCenPersonal.alias_last_name = Checks.Payee
OR FinCenPersonal.alias_last_name = Checks.PurchasedBy
OR FinCenPersonal.alias_last_name = Checks.Address
OR FinCenPersonal.alias_first_name = Checks.Payee
OR FinCenPersonal.alias_first_name = Checks.PurchasedBy
OR FinCenPersonal.alias_first_name = Checks.Address
OR FinCenPersonal.number = Checks.Reference
OR FinCenPersonal.street = Checks.Address
June 1, 2006 at 10:25 am
Woah there... you need all those varchar(max) fields?
June 1, 2006 at 10:27 am
That gets me part way there. I was using IN because it selected partial matches too. The performance hit is accectable, there are usually less than 1000 rows in the tables, and its only run every couple of weeks.
The problem is there are numerous rows in the FinCen table that are blank, and they are being selected. I need to filter out the blank rows while keeping the good data.
Just adding a WHERE clause like "last_name > '' and/or first_name > '' won't work, because one might be blank, and the other not and have a match.
chuck
June 1, 2006 at 10:33 am
IN doesn't do a partial match.
Try this:
declare @t1 table (code varchar(20))
insert @t1 values ('aaaa')
insert @t1 values ('bbbb')
insert @t1 values ('cccc')
declare @t2 table (newcode varchar(20))
insert @t2 values ('aaa')
insert @t2 values ('bbbbb')
insert @t2 values ('cccc')
select *
from @t2
where newcode in (select code from @t1)
Also, what do you mean by blank rows in the FinCen table? Could you post some sample data?
June 1, 2006 at 10:36 am
Didn't even notice that. You should size the columns to some realistic size based upon the data. Normally varchar(255) is more than sufficient for names, addresses and the like.
June 1, 2006 at 11:17 am
Thanks for looking at the problem.
I'll Adjust the varchar later, not important right now.
The problem is in the checks table I've got and entry in Payee like "Chuck Sndyer"
and in the FinCen Last_name I've got "Snyder". I need this to be a match, while at the same time not reporting those entries where either the payee or FinCen last_name are blank.
chuck
June 1, 2006 at 11:58 am
This looks like it works. It is striping out any blank rows, and passing thru the matches. Any suggestions would be appreciated.
chuck
SELECT FinCenPersonal.*, Checks.* FROM FinCenPersonal
INNER JOIN Checks
ON (Checks.Payee>'' and FinCenPersonal.last_name> '' and Checks.Payee LIKE '%' + RTRIM(FinCenPersonal.last_name) + '%')
OR (Checks.Payee>'' and FinCenPersonal.first_name> '' and Checks.Payee LIKE '%' + RTRIM(FinCenPersonal.first_name) + '%')
OR (Checks.Payee>'' and FinCenPersonal.alias_last_name> '' and Checks.Payee LIKE '%' + RTRIM(FinCenPersonal.alias_last_name) + '%' )
OR (Checks.Payee>'' and FinCenPersonal.alias_first_name> '' and Checks.Payee LIKE '%' + RTRIM(FinCenPersonal.alias_first_name) + '%')
OR (Checks.PurchasedBy>'' and FinCenPersonal.last_name> '' and Checks.PurchasedBy LIKE '%' + RTRIM(FinCenPersonal.last_name) + '%')
OR (Checks.PurchasedBy>'' and FinCenPersonal.first_name> '' and Checks.PurchasedBy LIKE '%' + RTRIM(FinCenPersonal.first_name) + '%')
OR (Checks.PurchasedBy>'' and FinCenPersonal.alias_last_name> '' and Checks.PurchasedBy LIKE '%' + RTRIM(FinCenPersonal.alias_last_name) + '%')
OR (Checks.PurchasedBy>'' and FinCenPersonal.alias_first_name> '' and Checks.PurchasedBy LIKE '%' + RTRIM(FinCenPersonal.alias_first_name) + '%')
OR (Checks.Address>'' and FinCenPersonal.last_name> '' and Checks.Address LIKE '%' + RTRIM(FinCenPersonal.last_name) + '%')
OR (Checks.Address>'' and FinCenPersonal.first_name> '' and Checks.Address LIKE '%' + RTRIM(FinCenPersonal.first_name) + '%')
OR (Checks.Address>'' and FinCenPersonal.alias_last_name> '' and Checks.Address LIKE '%' + RTRIM(FinCenPersonal.alias_last_name) + '%')
OR (Checks.Address>'' and FinCenPersonal.alias_first_name> '' and Checks.Address LIKE '%' + RTRIM(FinCenPersonal.alias_first_name) + '%')
OR (Checks.Reference>'' and FinCenPersonal.number> '' and Checks.Reference LIKE '%' + RTRIM(FinCenPersonal.number) + '%')
OR (Checks.Address>'' and FinCenPersonal.street> '' and Checks.Address LIKE '%' + RTRIM(FinCenPersonal.street) + '%')
June 1, 2006 at 12:08 pm
Try this.
SELECT [columns]
FROM FinCenPersonal
INNER JOIN Checks
ON Checks.Payee like '%' + FinCenPersonal.last_name + '%'
OR Checks.PurchasedBy like '%' + FinCenPersonal.last_name + '%'
[follow pattern]
Given a payee of 'Chuck Snyder', this will match 'Snyder', 'Snyders' and 'DeSnyder'. Not sure if this is what you want?
By blank, do you mean NULL, '' or '[space]'? They shouldn't be matching unless one of the other conditions in the OR is including them. Could you show some sample data?
You should store NULL in the column if the data is unknown, not ''. If you store NULL then you won't need to check the pieces.
declare @t1 table (code varchar(20))
insert @t1 values ('aaaa')
insert @t1 values ('bbbb')
insert @t1 values (NULL)
declare @t2 table (newcode varchar(20))
insert @t2 values ('aaaaa')
insert @t2 values ('bbbb')
insert @t2 values (NULL)
select *
from @t1 t1
inner join @t2 t2
on t2.newcode like '%' + t1.code + '%'
June 1, 2006 at 2:27 pm
Actually that's excactly what I want (well, its what the boss wants, so its what I want).
Currently the program importing the FinCen file is posting '' to the cells, but it would only take a minutes work to change to to post nulls instead.
I really apperciate the help, makes me feel like i'm not alone stumbling in the dark for solution.
Thanks
Chuck
June 1, 2006 at 2:53 pm
Last update. Thanks Jeff. Changed the import to be Null instead of '', and dropped off the checks for '' in the query.
Works beautiful. Going to save the operator quite a bit of time.
Chuck
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply