Relational 2 column field into 1 key using SPT 1

[Sansanto]

Hi All.
can we relation db on SPT in VFP command like this :

SQLEXEC(_skon, "select * from leave_dtl_mst", "leave_dtl_mst")
SELECT leave_dtl_mst
INDEX ON EMP_ID+YEAR TAG DTL_ID

SELECT LEAVE_DTL_MST
SET ORDER TO DTL_ID

SQLEXEC(_skon, "select * from leave_mst", "leave_mst")
SELECT leave_mst
INDEX ON EMP_ID+LVPERIOD TAG MST_ID

SELECT LEAVE_MST
SET ORDER TO MST_ID

SET RELATION TO MST_ID INTO LEAVE_DTL_MST

SELECT LEAVE_MST
THISFORM.REFRESH

I got an error saya "Variable MST_ID is not found" ? i am using db MYSQL. any suggestion ?

 

[Tore Bleken]

To repeat what I wrote in another forum:

Your code is a total overkill, you can do it all in two line of code!

SQLEXEC(_skon, "Select a.*, b.* from leave_dtl_mst a inner join leave_mst b on a.mst_id = b.mst_id order by a.mst_id", "leave_mst")
Thisform.refresh()

However, you shouldn't use Select * from any of the tables, instead you should list only the necessary fields. That way you will avoid "strange field names" when the fields in the two tables are identical.

 

[Mike Lewis]

you shouldn't use Select * from any of the tables, instead you should list only the necessary fields. That way you will avoid "strange field names" when the fields in the two tables are identical.

That is very good advice, But, going further, an even better reason not to do [tt]SELECT *[/tt] is that it defeats one of the main advantages of a client-server database. By retrieving only the rows and columns that you actually need, you can greatly reduce network traffic and storage overheads on the client system, and therefore promote greater efficiency.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I agree, but the problem you have has nothing to do with SELECT * or the involvement of SPT.

You have INDEX ON EMP_ID+LVPERIOD TAG MST_ID so MST_ID is a tag name, an index name

Then you do

SET RELATION TO MST_ID INTO LEAVE_DTL_MST

That's likely not what you wanted. You have backwards how SET RELATION works

The help says

SET RELATION TO [eExpression1 INTO nWorkArea1 | cTableAlias1

The target workare must be ordered by an index and the expression used in the SET RELATION is seeked in this index. You try to seek an index in an index, that's not how RELATIONS are meant to be used.

For this relation to work you only need an index in the cursor LEAVE_DTL_MST, so you don't need INDEX ON EMP_ID+LVPERIOD TAG MST_ID, it may just help to get the data sorted, but it's not necessary for the relation. The SET RELATION you need is

SET RELATION TO EMP_ID+LVPERIOD INTO LEAVE_DTL_MST

And hopefully, LVPERIOD is a year.

By the way, if these fields are integers, this will get wonky, you get a sum, not a concatenation, that's working technically, but say emp_ids are 1 and 2, years are 2022 and 2023, 1+2023=2024 is the same as 2+2022=2024, so relating employee 2 to last year gives you all records of employee 1 in 2023 and employee 2 in 2022 in the details, not only records of one employee and one year. Any combination of emp_id and year that sums to the same value (2024 in the sample case) will show up. That's not, what you'd want.

For sake of this working correctly I hope for you emp_id and year and are LVPERIOD are character fields, but then I would question why you would store a number in a character field. That's bad design one way or the other.

Chriss

 

[Chris Miller]

Let's look at the detail of

SET RELATION TO MST_ID INTO LEAVE_DTL_MST

Where the only thing that MST_ID could be in the context of all code we know from you is the index tag defined by

INDEX ON EMP_ID+LVPERIOD TAG MST_ID

It seems to me you expect MST_ID to evaluate to the index expression EMP_ID+LVPERIOD, because that's what MST_ID is defined. You're completely out of your mind to think this is how it works. An index will always just be able to give you record number for which the index expression is a value you seek in the index, it does not work the other way around telling you the value of some record, i.e. the current record. To get the values of field of the current record or of an expression with fields, you use that expression, an index, even one that is defined on that expression, won't give you that expression value.

It doesn't work that way, it never worked that way also in legacy FoxPro. The way you could get the value of an index MST_ID for the current record would be EVALUATE(KEY()), which would be overcomplicating things.

Chriss

 

[Sansanto]

Thanks for all for your solutions
Specialy Thank for Chris Miller the problem is solved now.