One of the solutions is not a technical solution : it's a deeper analysis.
A great advantage of this solution is that the technical implementation will
be, we hope, simpler than an exhaustive research across all the columns of
the database.
The risk of an exhaustive implementation is an research that the users
will use only at 5% of its capacities (always quering on the 4 same
columns...).
I had implemented this kind of research program : it worked fine when it
returned 2 result lines, but too much often, it had returned thousand lines
!
For information, the main algorithm was (if my memory do not fail !) :
- ask to the user the value to search, and for string value only, if it is
an exact search or an "LIKE %s%" search ;
- detection of the type of value to search (char, interger, float, ...) ;
- research of the columns of this type (1 query in
INFORMATION_SCH EMA.COLUMNS) ;
- for each table with at least one matched column, 1 SELECT query :
- SELECT PK, fld1, fld2
- FROM the table
- WHERE fld1='value' OR fld2='value' OR ...
(or WHERE fld1 LIKE '%value%' OR ...)
* analyse the result recordset and display the results in a array, with
these columns : TableName, PKValueOfTheMat chedRecord, FieldName, FieldValue.
This program gives the right results, but it talks about "Table", "PK",
"Field", ... and it's not the user's language. So, the difficulty is to
transform these "data oriented" results in "users oriented" results !
I'm interested in an different approach.
Jacques.
"Versteijn" <ve*******@538m ail.nl> a écrit dans le message de news:
4d************* *************@p osting.google.c om...
Hello all,
I have a database in SQL Server that should save data from a CRM-like
application.
The database consists of tables like products, services, customers,
partners etc. Problem is that the users should be able to find these
items on different properties and with or without substring finding
(SQL: LIKE). Example: I want the users to be able to find a customer,
providing a customerID, but also providing a customername, zipcode or
just a part of those strings.
This will result in a lot of queries. I bet there are some nice
solutions to this, since I will not be the first with this situation.
If anyone can help, please.
Thank you in advance.
Regards,
Freek Versteijn