ForceField compound SQL statement examples for use with repository
A select stmt that shows orders with no line items:
SELECT SALESREP, ORDNUM, ORDDATE, POST_DONE
FROM FORCEFIELD_ORDHEAD
WHERE NOT EXISTS
(SELECT LINENUM FROM FORCEFIELD_ORDITEM
WHERE FORCEFIELD_ORDITEM.SALESREP = FORCEFIELD_ORDHEAD.SALESREP
AND FORCEFIELD_ORDITEM.ORDNUM = FORCEFIELD_ORDHEAD.ORDNUM)
changing the above to an update statement to change the post flag:
UPDATE FORCEFIELD_ORDHEAD
SET POST_DONE = 'Y'
WHERE NOT EXISTS
(SELECT LINENUM FROM FORCEFIELD_ORDITEM
WHERE FORCEFIELD_ORDITEM.SALESREP = FORCEFIELD_ORDHEAD.SALESREP
AND FORCEFIELD_ORDITEM.ORDNUM = FORCEFIELD_ORDHEAD.ORDNUM)