General information
Category:
Coding, syntax & commands
Title:
Problem writing 2 queries from the same table
Hi everyone,
I am trying to update a table which I have opened as an alias and then update that same table for a condition met in some cursors I created. The following is the code I have written:
SELECT prempl, taxid, ytax, ybase, ywage ;
FROM a_prehtx ;
WHERE txqtr = '3' AND (qtax <> 0 AND qbase <> 0 AND qwage <> 0) AND a_prehtx->prempl = '300041' AND a_prehtx->taxid NOT in ('FUTA','SUTA') group by prempl into cursor curTaxThree
SCAN
SELECT prempl, taxid, qtax, qbase, qwage, ytax, ybase, ywage;
FROM a_prehtx ;
WHERE txqtr = '4' AND (qtax <> 0 AND qbase <> 0 AND qwage <> 0) AND a_prehtx->prempl = curTaxThree->prempl AND a_prehtx->taxid = curTaxThree->taxid group by prempl,taxid into cursor curTaxFour
IF curTaxFour->Ytax <> (curTaxThree->Ytax + curTaxFour->Qtax)
Scan
Update a_prehtx ;
Set Ytax = (curTaxThree->ytax + curTaxFour->qtax), ;
YBase = (curTaxThree->ybase + curTaxFour->qbase), ;
Ywage = (curTaxThree->ywage + curTaxFour->qwage) ;
WHERE prempl = curTaxFour->prempl ;
AND txqtr = '4' ;
AND Taxid = curTaxFour->taxid
ENDSCAN
ENDIF
ENDSCAN
USE IN curTaxThree
USE IN curTaxFour
USE IN a_prehtx
The problem is that this code only updates the last record for prempl = '300041'. I thought the scan after the cursor curTaxThree would match the record to the second cursor,then the second scan would update for that one record, and then it would continue this process until all the records where found for the initial condition. Obviously I am doing this wrong but I do not know why the above code only updated the last record? Is there a better way to do this. Please help. Thanks!
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only