Select and Indexs 2

[ictoo]

What i'd like to know is can you index on a select, ive been looking but cant find the command to do this i'd just like to know if it can be done, or im just being blind and stupid again.

Ive got a report button that prompts the user for two data and then produces a report between these dates, but i want to filter the db that its coming from and SQL ignores the order set on the table its looking for so i assume there has got to be a command im not finding.

 

[Stella740pl]

Yes, SQL uses indexes only to speed up the process, but not to order the outcome. For ordering the oucome of the SQL-SELECT statement, you should inlude ORDER BY clause into your SELECT, if this is what you are trying to do. To filter the data you include in your data set, you list your conditions in the WHERE clause.

It would be much easier if you posted a sample of your data, your SELECT statement, and told what exactly doesn't work the way you wrote it.

 

[GriffMG]

I'm not sure I have read your request correctly, but if you want a select in a particular order (to match an index or somesuch)...

Select * from myTable order by myField1, myField2 desc, myField3

Good luck

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[ictoo]

Ok ive looked at the ORDER BY before i posted this and i dont think i can use it, i want to select a index i already have on my table the index is this:

value1=VAL(SUBSTR(tagname,AT("_X_",tagname)+3)).AND.type=0.AND.taggroup="PH_Dlog".AND."_X_"$tagname

As my table is fill with alot of differnt records i filter them by the taggroups then then i want to sometimes only show records ending in _X_.

i thought there would be away to select the Index that i have made on the table already but i guess this is not the case?

 

[JRB-Bldr]

You do not INDEX on the SELECT command.

You create an INDEX when you have EXCLUSIVE use of a data table.

USE MyTable IN 0 EXCLUSIVE
SELECT MyTable
INDEX ON Field1 TAG Fld1  && Build one index
INDEX ON Field2 TAG Fld2  && Build another index

You can then utilize any pre-defined index whenever it might be needed.

SELECT MyTable
SET ORDER TO Fld1
<do whatever>

You could most likely benefit by looking in your VFP Help for INDEX ON to better understand the capabilities of the INDEX command.

But your 2nd paragraph describes something not necessarily associated with INDEX issues.

You could do it with a FILTER and an activated INDEX, but it would most likely be better using a SQL Query to accumulate only the data you need.

SELECT *;
  FROM MyTable;
  WHERE BETWEEN(DateFld,dFirstDate,dLastDate);
  ORDER BY <field order>;
  INTO CURSOR RptData

SELECT RptData
REPORT FORM MyReport NOCONSOLE TO PRINT

For this might benefit by looking in your VFP Help for SELECT - SQL to better understand the ORDER BY optional parameters.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Ictoo,

Let me get this right. You want to do a SELECT, and place the results in a particular order, with the ordering based on a complex expression rather than on a simple combination of fields. Is that right?

If so, you have two choices:

- Add a field to the SELECT, containing values based on the expression you want to order by. Use that field in your ORDER BY clause; or

- Don't use an ORDER BY clause. Instead, do the SELECT into a cursor, and index the cursor (you will have to make the cursor READWRITE in order to index it).

Does that help at all?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Stella740pl]

I am not sure I understand what you mean when you say you want to select an index.

Are you trying to create a table that has the same structure as your source table and has the same index, but includes data only between the two dates entered by the user?
Then you have an option of COPying instead of SELECTing:

COPY TO ... FOR BETWEEN(Field1,Value1,Value2) WITH CDX

If, on the other hand, you are trying to SELECT items on the same condition you have in your index, you can do just that:

SELECT ... WHERE value1=VAL(SUBSTR(tagname,AT("_X_",tagname)+3)).AND.type=0.AND.taggroup="PH_Dlog".AND."_X_"$tagname

or a similar expression.

You can also get the index expression using SYS(14) function:
SYS(14, nIndexNumber [, nWorkArea | cTableAlias])

 

[Olaf Doschke]

As you say you are prompted for two dates, it sems the report already is using a parameterized view. To not be promted for values, you need to define the view parameters as variables before calling the report.

Eg the view SQL is SELECT * FROM sometable WHERE somfield>=?mindate AND somefield<=?maxdate, then you need variables called mindate and maxdate set up to the min and max date you want before calling the report.

In regard to using the index in the SQL, Stella is pointing in the right direction, you can pseudo use the index by using the exact index expression in the where clause. Rushmore will then use the index to filter the records, but the order will simply be the physical order, your index is rather filtering than ordering, as it's an expression, which overall evaluates to .T. or .F.. A sorting index most probably would be one ending in a character expression and therefore sorting alphabetical. But in SQL you use the ORDER BY clause, you'll need to establish the same index in the SQL result set to be able to SET ORDER TO that index, an index is not "inherited" by SQL into it's SQL resultset.

A totally different way would not be using the SQL approach, USING the table and REPORT FOR indexexpression AND Between(datefield,mindate,maxdate).

Bye, Olaf.

 

[SnyAc]

To answer your question simply... no you can't index on a select. What you do is select your record set to a cursor READWRITE and then index the cursor using the same key expression as the index tag you wanted from the datasource (which is what Mike said above).

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

Hyperware Inc. a division of AmTech Software

http://www.hyperwareinc.com

 

[ictoo]

Ok ive got it working i put something together and it seems to be holding, and working the way i want it to thanks to everyone for your help i was looking at this for most of the day yesterday and just couldnt see what i was doing wrong.

 

[Stella740pl]

ictoo,

Thanks for the star.
But could you please show the solution that worked for you?
I think we all would like to know what was that you wanted and how it turned out.
Thanks.

 

[ictoo]

Well i was looking at all the suggestions and working out how i would use them when you said using my index as a simple where clause in my select would work and didn’t think I could do that so i tried it and it didn’t work, but then i found out it was just the way i had set up my select all together so i came out with something as simple as this.

*****

SELECT * ;
FROM epamonitor21 ;
WHERE taggroup = ALLTRIM("EPA_Dlog");
AND BETWEEN (TTOD(stamp),ldFrom,ldTo) ;
AND value1=VAL(SUBSTR(tagname,AT("_X_",tagname)+3)).AND.type=0.AND.taggroup="EPA_Dlog".AND."_X_"$tagname;
INTO CURSOR Tempepa

SELECT Tempepa
REPORT FORM C:\Playdata\report2.frx PREVIEW

USE IN tempepa

******

I weren’t sure i could do that, as i had tried something along that line before and it just failed because of the way i had setup my select. Again I’d like to thank everyone you have all given me different insights in to this problem and I’ll be sure to try them all in my next project.

 

[Stella740pl]

Thanks for coming back and closing the thread with your solution.