Creating a temporary table, determining it's record order

[dmusicant]

Here's my problem:

I have two tables and they share a common key. Let's call them table A and table B. The key is alphabetical, 9 characters.

Table A has details. Table B tracks instances that table A items occur, i.e. date and order of item appearance on that date, by set and order within the set. Table B may have, say, 35 records of table A referenced for a particular date. But the only cross reference is the common alphabetical key.

Again, Table B also has info about the order that things in table A appeared on any particular date that happens to reference a particular Table A record. The references appear in sets, and each set has an order of item appearance. Those fields in table B are set_num and select_num (integer fields). If set_num is 4, it's the 4th set that occurred on the particular date. select_num for a record in table B refers to the position that record has in a particular set. Thus, a table A item for that date may appear in set_num 4 and select_num 6, which is right after set_num 4, select_num 5, when the table B's index is set to DATESORT, which is ordered by date/set_num/select_num in that order.

I am interested in seeing info of table A pertaining to a particular appearance in table B. Thus, if there's a reference to a certain item in table A for a specific date in table B, I want to see info for table A for all records that appear in a specific set in table B for that date. I did this with a select and a subquery. Here's the code:

sele proper(artist), proper(title), recordings, album, evaluation, play, airplay, theme from rec where rec.key ;
in (select key from airec where date=msetdate and set_num=mset_num)

Here I had already determined the msetdate and the mset_num variables. This works, however I'm not happy with the browse that pops up because I want to see the records in select_num order, that is the order they appear in table B. I'm sure this is doable, but the solution is eluding me.

 

[dmusicant]

Ah, I think I have it now, I needed to expand my select a bit.

 

[dmusicant]

Yeah, a subquery wasn't necessary, just this:

sele proper(artist), proper(title), recordings, album, evaluation, play, airplay, theme from rec, airec where ;
date=msetdate and set_num=mset_num and rec.key=airec.key order by select_num

 

[Olaf Doschke]

I see you solved it already. But it helps if you mention what is head and detail table, or are they both details to a third head table not mentioned?
If you query a family of tables, even if you don't need fields from the head table, including it in the query helps to straighten what belongs where. You can also do without, ie knowing a specific order number you can query order items and join product names (assuming order items don't contain product IDs, not names).

The other aspect was ordering the result. Well, yes, that is done with an ORDER BY clause, what index VFP uses to optimize the ordering is up to the Rushmore query optimization engine. If you have an index on a complex expression like you said date/set_num/select_num then you can use that for your order by to let the SQL engine use that index. So despite of looking more complex and computing an expression for every record to order by it, this becomes usage of an index. You design indexes and queries together to need the least indexes for all your queries.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[mjcmkrsr]

Hi,

sele proper(artist), proper(title), recordings, album, evaluation, play, airplay, theme from rec, airec where ;
date=msetdate and set_num=mset_num and rec.key=airec.key order by select_num

You may want to finetune this SQL a little bit, You may also want to specify the tables/alias the fields "date" and "set_num" belong to in order to avoid confusion. Furthermore beware of the PROPER function and consider what T. Granor, T. Roche & al. wrote about it in "Hacker's Guide to Visual Foxpro 7.0".

PROPER() is one of those great ideas that just didn't work out. Its purpose is to let folks enter names without worrying about capitalization. Then, you can come along later and apply PROPER() to fix it up. Unfortunately, names just aren't that simple. While PROPER() is great for run-of-the-mill names (like Granor or Roche), it falls apart when you hand it stuff like O'Hara or MacNeill.

Fundamentally, PROPER() is too simple-minded to do the job. It takes whatever you hand it and returns it with the first character of each word capitalized. If that's not appropriate, too bad.

.

Hence my suggestion

SELECT [b]UPPER[/b](artist), [b]UPPER[/b](title), recordings, album, evaluation, play, airplay, theme FROM rec ;
JOIN airec ON rec.key = airec.key ;
WHERE [i][b][highlight #FCE94F]alias[/highlight][/b][/i].date = msetdate AND [i][b][highlight #FCE94F]alias[/highlight][/b][/i].set_num = mset_num ;
ORDER BY select_num ;
INTO CURSOR csrTemp

hth

MK

 

[Olaf Doschke]

Ah, one further misunderstanding, perhaps:

You said:

when the table Bs index is set to DATESORT, which is ordered by date/set_num/select_num in that order.

You seem to have the idea that sorting by index influences the query. No, it does not. Neither does SET FILTER.

If your query uses alias names of already opened tables (in your case "rec" and "alrec"), the VFP SQL engine still takes the DBF("rec") and DBF("alrec") and reopens them again under temporary workarea names. An index can also be used by the SQL query engine, but you don't predefine the order of result records by setting an index order on the table queried.

This is not just a rule of thumb, this is really always true, a hard fact, it's never different: The SQL engine always makes use of its own separate workareas and starts fresh with the dbf files involved. It even is true when you query from a cursor, a previous query result. A query result is nothing else but a table. A cursor is a table. With one special case, that even is counter-intuitive: If your query results in a so-called filter cursor, the DBF("cursoralias") will be the DBF file of the table (so this also only applies to single table queries). VFP then did not create a separate cursor. Instead, VFP did set a filter on the DBF and gave it your wanted cursor alias. And then the usual working of the SQL engine to reopen the DBF would mean it would not start on the resultset but the whole table, which is why VFP disallows that.

And that closes the circle, SQL wants to prevent SET FILTER and INDEX order to affect query results, while some developers would really like that. But it would interfere with what the SQL engine does, has to do and can do instead. When it uses multiple workareas and switches indexes. The Rushmore engine analyzes your query and decides which indexes to use to optimize where clauses, joins, and order by clause, even other expressions it can reidentify in the tables CDX files in the field list. Also, this in the end has the positive effect it leaves your workareas as they were.

Besides all this, you think too linear and simple, if you think the result of a query will read records in order and copy them into a result, queries can be quite complex and index usage can make VFP just pic out certain recnos in recno order. In the ends, SQL has its own means to sort the output, the ORDER BY clause.

OK, and then some other aspect: MK already addressed you better not use proper, but even if you do, you'll get result fields called expr_1, expr_2 or similar. Every time you use an expression like Proper(artist) you have to realize VFP will not name the result of this artist, just because the expression only uses the artist field. I'll show you my variant of your query (formatting is a matter of taste, more important it may not work because I didn't guess which field comes from which dbf):

Select proper(alrec.artist) [highlight #FCE94F]as artistname[/highlight] ;
   ,   proper(alrec.title) [highlight #FCE94F]as songtitle[/highlight] ;
   ,   alrec.recordings ;
   ,   rec.album ;
   ,   rec.evaluation ;
   ,   rec.play ;
   ,   rec.airplay ;
   ,   rec.theme ;
from alrec ;
inner join [highlight #FCAF3E]rec[/highlight] on rec.key=airec.key ;
where [highlight #FCAF3E]date[/highlight] = msetdate and set_num = mset_num ;
order by rec.select_num

MK already turned your query into a join syntax, what you do works, too, but there is a slight difference in the way this is working when you put join conditions into the where clause you can only do inner joins, not left/right outer joins. The query optimization can likely save you from really first building the cartesian product of all combinations of records, of which you only keep those with same key, but in a join, you only match records with the same key in the first place.

MK also already gave you the INTO CURSOR clause, you might have INTO TABLE filename there, as your thread title says temp table - not cursor. If you have no INTO clause in VFP you will get a query result named "query" and you immediately get it in a browse window. You don't want that in an application.

I also marked some unfortunate names in orange, even though rec is no reserved word, record and recno are and if you have to do with music records, there's an overlap in terms with VFP language, that might bite you at some places.

Least important note: you prefix your variables with an m, well, that is quite similar to what you could do without making this prefix part of the name, Defining variables setdate and set_num you can differentiate between fields and m.setdate and m.set_num. But you can and should stay with your differentiation already in the name, even if you'd make use of this memory object for differentiation of fields vs variables, it's not just old school, it's really outdated to have same names for variables and fields even though SCATTER and GATHER work on that principle, we now can SCATTER to an object, one variable with multiple attributes, ie a record object and also GATHER that, even INSERT it into a table and also easily pass it. You also have control over variable declarations, no matter how the table structure changes, you have all the fields in one variable in its attributes.

But better make use of VFPs name convention for prefixes. We post in that style and while it may make everything more cryptic, to me that's worse with short field names which have their reason in name length limitations. Anyway, there we surely also enter the realm of opinion and taste.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name