Solved

Database filtering...

Posted on 1997-11-15
10
283 Views
Last Modified: 2010-08-05
I want to filter a database acording to some cretiria.
For example i have a database with the following fields:

   -Firstname
   -Secontname
   -Code

 I want to show every record that:
   a)the firstname begins with 'A'
   b)the secontname begins with 'B'
   c)the code is greater than '500'

 I tried to "setrangestart" and "setrangeend" but it doesn' work.
                      help!!!
0
Comment
Question by:harisis
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 4

Expert Comment

by:itamar
ID: 1350546
With Paradox or dBASE tables, these methods work only with indexed fields. With SQL databases, they can work with any columns specified in the IndexFieldNames property.

Provide some code and additional information if it is not the problem, such:

1) What indexes do you have in this table;

2) Did you try the Filter property;

3) Are you using TTable or TQuery;

4) What database (Paradox, Interbase, ...);

                                            ????
IHTH
0
 

Author Comment

by:harisis
ID: 1350547
I use a TTable component with a Paradox table and i have created a secondary index named "secind" with all the field of the table in the above order.
 I have also put key in every field .

 I use the code below:

  With table1 do
   begin
    indexname:='secind';
    setrangestart
    fieldbyname('firstname').asstring:='A';
    fieldbyname('secondname').asstring:='B';
    fieldbyname('code').asinteger:=500;
    setrangeend;
    applyrange;
   end;

 What the filter property has to do with my problem?
 
0
 
LVL 4

Expert Comment

by:itamar
ID: 1350548
The correct usage of SetRangeStart and SetRangeEnd must be:

With Table1 do begin
    IndexName := 'secind';
    SetRangeStart; //Starts setting the starting values
    FieldByName('FirstName').asstring := 'A';
    SetRangeEnd; //Starts setting the ending values
    FieldByName('FirstName').asstring := 'A' + #255; // All A's
    ApplyRange;
end;

The filter has everything to do with your problem! Try for example:

With Table1 do begin
    Filter := 'Code>500';
    Filtered := True;
end;

This will solve part of your problem. You'll still have some troubles cause when using composite indexes, it seems that you must provide exactly the first part of it to get the second range, for example:

With OtherTable do begin
    IndexName := 'DeptSal';
    SetRangeStart; //Starts setting the starting values
    FieldByName('Department').asstring := 'XPTO';
    FieldByName('Salary').asinteger := 1000;
    SetRangeEnd; //Starts setting the ending values
    FieldByName('Department').asstring := 'XPTO';
    FieldByName('Salary').asinteger := 5000;
    ApplyRange;
end;

So, AFAIK (As far as I Know), it's better using TQuery to get the better results in your case. It will take the advantages of the index in a simple way, like:

SELECT *
FROM MyTable
WHERE FirstName LIKE 'A%' AND SecondName LIKE 'B%' AND Code > 500

I know it's a workaround, that's why it's a comment not an answer (the first one was supposed to be the same - Sorry). I also have some hope that other experts can provide a solution to use Ranges with composite indexes. Otherwise, i'd like to submit this comment as an answer since we can only give answers, but they are  not always solutions.

----[S.M.I - Sori mi inglige] ---------

0
 
LVL 6

Expert Comment

by:tecbuilder
ID: 1350549
Just use the table's filter property.

codevalue := 500;
fnamevalue := 'A';
lnamevalue := 'B';

table1.filtered := false;
table1.filter := 'table1firstname.Value >= ' + '''' + fnamevalue + '''' +
' and table1lastname.Value < ' + '''' + lnamevalue + '''' +
' and table1code.Value = ' + '''' + IntToStr(codevalue) + '''';
table1.filtered := true;

Since the table is now filtered, you can only access those records that meet the criteria.  If you use the table's RecordCount function (table1.RecordCount) the returned value will be the number of records that meet this critiria as well.
0
 
LVL 6

Expert Comment

by:tecbuilder
ID: 1350550
Oops, I mis-read what you wanted to filter on.  The code should be as shown below with some additional explanation at the end.

codevalue := 500;
fnamebegin := 'A'; fnameend := 'B';
lnamebegin := 'B'; fnameend := 'C';

table1.filtered := false;
table1.filter :=
'firstname >= ' + '''' + fnamebegin + '''' +
'firstname < ' + '''' + fnameend + '''' +
' and lastname >= ' + '''' + lnamebegin + '''' +
' and lastname < ' + '''' + lnameend + '''' +
' and code > ' + '''' + IntToStr(codevalue) + '''';
table1.filtered := true;

The problem that you raise is how to select all the names that begin with A for firstname and B for lastname.  There is no way to access just the first character directly from the record when filtering or setting the range.  So you have to declare the begin and end for what you want to select.  I have hard coded these variables, but they can be easily modified so that all a user has to do is select a value to fill the fnamebegin variable and the fnameend variable can be generated by what ever function you decide.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Expert Comment

by:itamar
ID: 1350551
As i had put in my comment you can really use the filter property to get the results, but be very carefull !!!
Filter doesn't do magic ! If you have a large table, it will not be efficient ! The best solution is use TQuery or mixing the SetRange... methods with Filter property.
I can provide some example if you reject tecbuilder's answer ( indeed it's really an example over one of the options in my comment)
I know you are goint to be fair ;)
0
 
LVL 6

Expert Comment

by:tecbuilder
ID: 1350552
harisis, I suggest using the filter option first.  It's very simple, easy to use, and you don't have to worry about updating your tables since the filter works on the table itself.

The SetRange properties are no faster than a filter when used on a TTable.  It works exactly as a filter.  The only difference being that the SetRange requires you to use the table's key fields to limit what you can see and the filter doesn't.  Also, when you want to go find a record you have to use the FindKey or SetKey..GotoKey methods which require you to only use the keyed fields.  The filter can be set via any field whether it's keyed or not.  With a filter to find a record you use the Locate method and again what you look for doesn't require the fields to be keyed.

If the filter doesn't do the job that you need it to do, then go with a TQuery.  TQuery is powerful because it allows you to use SQL.  However, it's also a bit more difficult since it works on a subset of the original table.
0
 

Author Comment

by:harisis
ID: 1350553
  Sorry ! I use Delphi 1 (client/server) and it doesn't recognize the filter property for a TTable.( or am i doing something wrong here?)

  If i use TQuery can i have the same functionality as if i use a TTable?
0
 

Accepted Solution

by:
Wilfred earned 300 total points
ID: 1350554
A TQuery  can be accessed the same way as a TTable component. So this could be a solution.
You could also use the OnFilter event of a TTable. It's a event in which you can set a return value (accept) to include or exclude the record in your result set.

0
 

Author Comment

by:harisis
ID: 1350555
 I will use a TQuery finally... So the points go to Wilfred (he is the lucky one) Thank you all folks...
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now