Finding a value in multiple fields

[BryanLane]

Due to systems mergers from acquisitions, differences in how we handle customers with accounts versus customers without accounts and because we have a mailing address and ship to address to deal with a customer name or DBA (that's "doing business as" not database administrator!)could be be in one of up to 10 fields. And since we have a separate set of tables for the current months activity versus "permanant" files for all the activity prior to the current month, it could be in up to 20 fields. (Don't you just love consistency?)

My method works; but I want to know if there is a better, more efficinet way to do it.

I merge the current month sales order table with the permanent file via a Commmand and then in the resulting table I create a formula called "SearchFields" that has all the fields concatenated into one long string. Then I query for records that have my search parameter in "SearchFields" with a *LIKE* query.

Is there a better way?

Thanks,
Bryan

 

[PeterDimitrov]

I guess your final structure is like this
CustomerID Name1 Name2 Name3 DBA Field1 Field2 ....

This is not normalized structure and that is why you need to handle it in a formula. The other option is to write a separate, where statement
Where CustomerID=XXXX and (Name1 ='XXXX' OR Name2='XXX' OR ....)

You can try to normalize the data by unpivoting it. Then the structure will be like this:
CustomerID FieldName FieldValue
xxxxx Name1 xxxxx
xxxxx Name2
xxxxx Name3
....

And your WHERE clause will be much simpler
WHERE CustomerID=XXXX and FieldValue='xxxxx'

Unpivoting will depend on your database type. The advantage of this method is that you can create a view and deal with your data as normalized data

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[BryanLane]

Thank you Peter. Sometimes the Accountant who has to do double duty as a report writer isn't the best idea. As soon as I read your response I had the "duh" moment... I used a series of LIKE statements.

Much faster, like an order of magnitude. Where my method took many minutes yours took seconds.

Thank you and have a Happy New Year.

Bryan