SV: a scan/end scan question again.

SV: a scan/end scan question again.

Post by Ander » Fri, 26 Aug 2005 05:11:14


Hi Tom
Try some SQL

Create Cursor qdrotest (agency N (6), dpt N(2), employee N (6),;
lchkamt n(10,2), ldate d, fretchk n(10,2), fdte d)

INSERT INTO Qdrotest ;
(agency,dpt,employee,lchkamt,ldate,fretchk,fdte) ;
SELECT F1.agency, .F1.dpt, F1.employee, F1.checkkamt, ;
F1.checkdte, F1.retchkamt, F1.dtlstchk ;
FROM Fulopmas AS F1 JOIN Lopchk AS L1 ;
ON F1.agency=L1.agency ;
AND F1.dpt=L1.dpt ;
AND F1.employee=L1.employee ;
AND YEAR(F1.checkdte)= YEAR(L1.checkdte) ;
AND MONTH(F1.checkdte)=MONTH(L1.checkdte) ;
WHERE F1.qdro='Q' AND L1.chckamt>0

A suggestion to a newbie: use the fullest and most understandable table and
column names possible. Will you remember two years from now what 'fretchk'
and 'ldate' or 'lchkamt' or 'qdro' all meant.
And where check became chk or and a date column is 'date' or 'dte' or 'dt..
something?
Tables in a VFP database have no restrictions on the length of column
names.
-Anders


Den 05-08-24 18.46, i artikeln
XXXX@XXXXX.COM , skrev "Tom"
< XXXX@XXXXX.COM >:
 
 
 

SV: a scan/end scan question again.

Post by man-wai ch » Fri, 26 Aug 2005 20:51:13

> A suggestion to a newbie: use the fullest and most understandable table and

We are supposed to have something called data dictionary. :P
(is that an old idea in the advert of OOP and UML...)

--
.~. Might, Courage, Vision. http://www.yqcomputer.com/
/ v \
/( _ )\ Linux 2.4.31
^ ^ 7:50pm up 1 day 23:27 load average: 0.01 0.04 0.10

 
 
 

SV: a scan/end scan question again.

Post by Ander » Sat, 27 Aug 2005 19:37:50

Hi Tom,
Well I think this example and all the various suggestions that have been
posted go to show how complex and full of pitfalls a 'simple' piece of xbase
code with SCAN, REPLACE and SEEK can get to accomplish what basically a
standard filtered join query that SELECT .. FROM .. JOIN .. ON ... WHERE ..
will handle.
You're selecting some fields from a table while relating to another table on
some columns, and filtering on two columns, one in each table.
Now, was it the values in fkey or the values in lkey that should serve to
relate the tables?
-Anders


Den 05-08-25 23.19, i artikeln
XXXX@XXXXX.COM , skrev "Tom"
< XXXX@XXXXX.COM >:
 
 
 

SV: a scan/end scan question again.

Post by VG9t » Sat, 27 Aug 2005 22:59:02

Anders, the Value in fkey were from the Parent table in this case, those were
also filtered down to only the records i needed, in this case
fulopmas.qdro="Q", the lkey is from the child table. i needed to match the
records from the parent with the above filter, to (lkey) in the child, then
using the index (empind) in the child find the earilest date in the file for
that matching parent record. and append to a new table so i can output it to
a format (xls) or something.

when at the command line, browse for fulopmas.qdro="Q" returns 104 records.

when the code runs, it returns 41. that is a point of confusion for me as
well, i have used filters and things before without these kind of errors.