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)

Article Details

Article ID:
166
Date added:
2013-02-08 21:25:44
Views:
293
Rating (Votes):
(493)