INDEX ON coding problem 1

[Toman]

Nice day to all.
I need to set

INDEX ON expr FOR MyFieldName = ldMyDate

where ldMyDate is date type variable.

To avoid troubles I want to refer to literal values only so I rewrite the code to:

lcMyDate ="{" + DTOC(ldMyDate) + "}"
INDEX ON expr FOR MyFieldName = &lcMyDate

It seems to work properly, but my question is: can it be simplified?
Any recommendations?
Thanks Toman

 

[bborissov]

You don't have to do this.
There are many other way to accomplish this.
INDEX ON expr TAG SomeName
Then you wlways can get the portion of records you want in a second(or less):

ldMyDate = ????
SELECT * FROM YourTable WHERE Expr = m.ldMyDate INTO CURSOR SomeCursor

You could use LocalView or CursorAdapter if you need these records to be updatable.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Toman]

Hi Borislav,

Thank you for a brisk answer. You are probably right when suggesting SQL SELECT, I use it frequently.

But in my particular case it is too radical change.

I'd like to stay with INDEX ON …, namely because I use it with UNIQUE phrase, which effect is in this case hard to achieve with SELECT. I don't want to go into details here.

So I repeat my question: How to effectively replace data type variable with literal value in above INDEX command.

Tom.

 

[Mike Lewis]

Hi Tom,

You said: "I use it with UNIQUE phrase, which effect is in this case hard to achieve with SELECT."

In fact, you can get the same effect with the DISTINCT clause in a SELECT.

However, I realise you don't want to go down that route. Even so, it seems to me that a filtered index is redundant, as you can always achieve the same result with an additional condition in the FOR clause in your query.

In other words, create the index without the filter. Also create another index on the field that you would have used in the filter (MyFieldName in your example). Then, to retrieve a record, do something like:

LOCATE FOR expr = <whatever> AND MyFieldName = lcMyDate

Provided the indexes exist, the query will be fully optimised, and you won't have the dangers of using a local variable in an index.

Hope this makes sense.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Toman]

Hello Mike,

I must say the whole story.
In a project I take care about some objects and dates associated to them. In a "basic" table there are more records for every subject with different dates like that:

subject	date
---------------
sub1	date1
sub1	date2

sub2	date3
sub2	date4
sub2	date5

and so on..

The task is to retrieve new subset, where is one record for every object, with date which is first less then given date. The table is moderate (about 1000 records). New records are added seldom. Reading for different dates is frequent.

My method here was:

1)
clone a basic table so that in natural order are objects one by one and for every object are dates in descending order by means of

SELECT *  FROM myTable ORDER BY subject, date DESC INTO …

2) apply command

INDEX ON object FOR date < ldMyDate UNIQUE

to retrieve records for certain date

3)
repeat step 2) every time ldMyDate has changed

What strategy would you suggest?
Thank you very much, Tom.

 

[TamarGranor]

This is actually quite easy with a query:

SELECT subject, MAX(date) ;
FROM YourTable ;
WHERE date < dSpecifiedDate ;
GROUP BY subject ;
INTO CURSOR result

This will give you one record for each subject, containing the most recent data that's less than the specified date.

Tamar

 

[Toman]

Thank you Tamar,
will try it on Monday, when back in town.
Toman

 

[Bertiethedog]

By the way if you are going to use the index route use DTOS instead of dtoc

YYYYMMDD

Seeking an answer seek(dtos(date())

 

[Toman]

Hi Bertiethedog
During my 15 years living with Fox I absolutely overpass command DTOS(). And it is highly relevant here. Thank you. Tom.


Hi Tamar
Your query works nice, only I was not able to access other fields belonging to selected records in a result table.
So I modified query slightly:

SET CENTURY ON
SET DATE TO DMY
* table to play with
CREATE table tblTest (subject c(20),date D, nr n(2,0))
   INSERT INTO tblTest VALUES ("sub1",{01.01.2008},1)
   INSERT INTO tblTest VALUES ("sub1",{01.01.2000},2)
   INSERT INTO tblTest VALUES ("sub1",{01.01.2001},3)

   INSERT INTO tblTest VALUES ("sub2",{01.01.2000},4)
   INSERT INTO tblTest VALUES ("sub2",{01.01.2001},5)
   INSERT INTO tblTest VALUES ("sub2",{01.01.2003},6)

   INSERT INTO tblTest VALUES ("sub3",{01.01.2003},7)
   INSERT INTO tblTest VALUES ("sub3",{01.01.2007},8)
   INSERT INTO tblTest VALUES ("sub3",{01.01.2005},9)
   INSERT INTO tblTest VALUES ("sub3",{01.01.2000},10)

   INSERT INTO tblTest VALUES ("sub4",{01.01.2000},11)

dSpecifiedDate = {02.02.2008}
[COLOR=red]
SELECT tbl1.subject, tbl1.date, tbl1.nr ;
 FROM   tblTest tbl1, tblTest tbl2 ;
 WHERE  tbl1.subject = tbl2.subject and tbl2.date < dSpecifiedDate;
 GROUP BY tbl2.subject, tbl1.subject, tbl1.date ;
 HAVING tbl1.date = MAX(tbl2.date) ;
 ORDER BY tbl1.subject[/color]

and now it seems to be OK.
Thank you very much for giving me a direction.
The problem remains, how to simply transform variable dSpecifiedDate into literal value with which I've started this thread. Or doesn't it mater to use variable here?
Tom

 

[TamarGranor]

Tom - I don't think the query you're using will work in VFP 8 and 9, which have tightened up the rules related to grouped queries.

To get what you want, use a subquery like this:

SELECT Subject, Date, Nr ;
FROM tblTest T1;
WHERE Date = ( ;
SELECT MAX(Date) ;
FROM tblTest T2 ;
WHERE Date < dSpecifiedDate ;
AND T1.Subject = T2.Subject );
INTO CURSOR MaxDates

Tamar

 

[Toman]

Tamar,
not only it works properly, but also it is pleasant and easy to read your code. Thank you and Borislav and Mike and Bertiethedog. Star for you, Tamar, if you don't mind.
Tom.

 

[TamarGranor]

Thank you for the star and the kind words. I try to write readable code. It helps me, when I have to go back to my own code later.

Tamar