>I need to combine some tables in SQL query and I also need to get the first table's records numbers.
>
>I try to build SQL query like this:
>
>Select RecNo() as RecNo_, tbl1.fld1, tbl2.fld1 from tbl1, tbl2 where tbl1.keyfield1 = tbl2.keyfield1
>
>and it works fine, but when I try to run query on three tables like this:
>
>Select RecNo() as RecNo_, tbl1.
, tbl2., tbl3. from tbl1, tbl2, tbl3 where tbl1.keyfield1 = tbl2.keyfield1 and tbl2.keyfield2 = tbl3.keyfield1
>
>then RecNo() returns the wrong values for the first table record numbers.
The obvious question is which RECNO() is involved, and is it taken from the source record or an intermediary. RECNO() is not a field, or a fixed attribute, of a record. In order to make it such, you need to store the RECNO() value to a field of the table before issuing the SQL SELECT statement. A better idea would be to use some sort of synthetic surrogate key - an arbitrary unique value associated with the record that remains static for the life of the record. RECNO() doesn't fit the bill, because the RECNO() of a given record may change over the life of the table as a result of deletions, packing and reordering of the file, and a given RECNO() may be assigned to several distinct records over the life of the table.
You would be better off simply assigning a sequentially allocated value to an integer field of the record and using it to uniquely locate the record.
