Quite often, when adding features I have had the need to see if my
feature works fine. If the feature adds new records to a table it is
easy to look at the table and see if it updated the record and the
fields correctly. But if it were to change existing records
(especially in inventory control table) it is hard to see if it
updated the correct record and the fields. Here is a tip on how to use
SQL – Select to do this:
1. Of course, before you start to do this you make a copy of the table
For VFP:
USE <table>
COPY to <temp table> WITH CDX
For SQL:
SELECT * INTO <temp table> FROM <table>
2. Test your feature
3. To compare give
For VFP
SELECT * FROM <table> a1
FULL JOIN <temp table> a2
ON <a1.keyfield1> = <a2.keyfield1> [AND <a1.keyfield2> = <a2.keyfield2>]
WHERE ISNULL(a1.keyfield1) OR ISNULL(a2.keyfield2) OR <a1.field1> <>
<a2.field1> [ OR <a1.field2> <> <a2.field2> …]
For SQL change the where clause to
WHERE a1.keyfield1 IS NULL OR a2.keyfield2 IS NULL OR <a1.field1> <>
<a2.field1> [ OR <a1.field2> <> <a2.field2> …]
The key here is the full join. It will show new entries (or entries
deleted) and the field comparison will list the field(s) you are
interested where they have changed.
Example using Sage Pro for icitem, iciloc, iciqty in VFP
USE prodata!icitem01
COPY TO temp1 WITH cdx
USE prodata!iciloc01
COPY TO temp2 WITH cdx
USE prodata!iciqty01
COPY TO temp3 WITH cdx
- Now I ran my test
After test here is the compare program (Use l_GetFieldString to easily add new fields)
SET DELETED ON
LOCAL lc_SelectList, ;
lc_FieldsWhere
lc_SelectList = ""
lc_FieldsWhere = ""
= l_GetFieldString("ionhand, avgcost", @lc_SelectList, @lc_FieldsWhere)
SELECT a1.item, &lc_SelectList. * FROM prodata!icitem01 a1 ;
full join temp1 a2 ;
ON a1.item = a2.item ;
WHERE ISNULL(a1.item) OR ISNULL(a2.item) &lc_FieldsWhere ;
INTO CURSOR ac_temp1
BROWSE LAST nowait
= l_GetFieldString("lonhand, lavgcst", @lc_SelectList, @lc_FieldsWhere)
SELECT a1.item, a1.loctid, &lc_SelectList. * FROM prodata!iciloc01 a1 ;
full join temp2 a2 ;
ON a1.item = a2.item AND a1.loctid = a2.loctid ;
WHERE ISNULL(a1.item) OR ISNULL(a2.item) &lc_FieldsWhere ;
INTO CURSOR ac_temp2
BROWSE LAST nowait
= l_GetFieldString("qonhand", @lc_SelectList, @lc_FieldsWhere)
SELECT a1.item, a1.loctid, a1.qstore, a1.qserial, &lc_SelectList. * ;
FROM prodata!iciqty01 a1 ;
full join temp3 a2 ;
ON a1.item = a2.item AND a1.qserial = a2.qserial ;
WHERE ISNULL(a1.item) OR ISNULL(a2.item) &lc_FieldsWhere ;
INTO CURSOR ac_temp3
BROWSE LAST nowait
FUNCTION l_GetFieldString
LPARAMETERS pc_FieldList, ;
rc_SelectList, ;
rc_WhereClause, ;
lc_Field
LOCAL ARRAY la_FieldList[1]
= ALINES(la_FieldList, pc_FieldList, 1+4+8, ",")
rc_SelectList = ""
rc_WhereClause = ""
FOR EACH lc_Field IN la_FieldList
rc_SelectList = rc_SelectList + "a1." + lc_Field + ", " + "a2." + lc_Field + ", "
rc_WhereClause = rc_WhereClause + " OR a1." + lc_Field + " <> " + "a2." + lc_Field
NEXT
RETURN
Regards,
Sankaran Raman
(work) 804-364-2995
(cell) 804-658-6321
(fax) 540-301-0794
sankaran@saharti.com
www.saharti.com
Saharti