Re: Comparing fields in 2 large files

Ferry, Craig wrote:
>> 
>> Please keep your responses to the perl.beginners group so that others
>> can both
>> provide input as well as learn from your experience. Thanks.
>> 
>> I suggest you stick with Perl but process the data directly from the
>> database.
>> Take a look at the DBI module, which isn't a standard one and so may
>> need to be
>> installed. Using that you can write SQL from within a Perl program, and
>> I'm sure
>> it will be a lot swifter that way.
>> 
>> Even so, it looks to me as if what you need can be written in a single
>> SELECT
>> statement, in which case a Perl wrapper is simply an unnecessary
>> overhead.
>> Without knowing the full details of the problem it's hard to tell.
> 
> Sorry about that I did mean to respond to the whole group.

No worries; my comment was for general consumption.

> I think I may be overlooking something based on your comment about
> 'single SELECT statement'.   Let me show you visual example.   My real
> data as I mentioned is millions of records of sales data totally
> unrelated to example :-).
> 
> Table_A (next line column headings)
> Animal	Color		PetStoreID
> dog		black		777
> cat		white		888
> dog		brown		999
> 
> Table_B (next line column headings)
> Comment				PetStoreID
> I sold the white cat		111
> cat black ran away		111
> br_own is the dog color		222
> do-gbla-ck is sick		222
> 
> I would need an SQL to do something similar to the following.  I know
> how to hard code a value for like, but don't know of a way to do a like
> comparing an actual field name.  I don't have the cleanup portion of the
> Comment column as I can do that relatively easily in a prior step.   My
> output would be a merge of data from table_a and table_b.
> 
> WHERE table_b.comment LIKE table_a.animal
> AND table_b.comment LIKE table_a.color
> AND table_b.PetStoreID <> table_a.PetStoreID

OK, that seems fine. And I wonder what your question is?

If you're worrying about the LIKE operator not working with field values
(instead of hard-coded strings) then that depends on your database engine. What
database are you working with, and have you tried this code? It looks like it
might just work.

Rob