I have recently discovered that on a few quotes the total of the sell price on the quote header does not equal the total of the sell prices from the lines. Is it possible to turn this select statement into an update statement where CSTQUTHD.TOT_SELL_PRC = SUM(CSTQUTLN.TOT_SELL_PRC)?
SELECT T1.QUOTE_NUMBER,T1.SEQUENCE_NUMBER,T1.REVALIDATE_NUMB,T1.TOTAL_SELL_PRC,T2.TOTAL_SELL_PRC
FROM (
SELECT QUOTE_NUMBER_OEQH AS QUOTE_NUMBER,SEQUENCE_NUMBER_OEQH AS SEQUENCE_NUMBER,REVALIDATE_NUMB_OEQH AS REVALIDATE_NUMB,TOTAL_SELL_PRC_OEQH AS TOTAL_SELL_PRC
FROM CSTQUTHD ) T1
INNER JOIN (
SELECT QUOTE_NUMBER_OEQL AS QUOTE_NUMBER,SEQUENCE_NUMBER_OEQL AS SEQUENCE_NUMBER,REVALIDATE_NUMB_OEQL AS REVALIDATE_NUMB,SUM(QUOTE_QUANTITY_OEQL*QUOTE_SELL_PRIC_OEQL) AS TOTAL_SELL_PRC
FROM CSTQUTLN
GROUP BY QUOTE_NUMBER_OEQL,SEQUENCE_NUMBER_OEQL,REVALIDATE_NUMB_OEQL ) T2 ON T1.QUOTE_NUMBER=T2.QUOTE_NUMBER AND T1.SEQUENCE_NUMBER=T2.SEQUENCE_NUMBER AND T1.REVALIDATE_NUMB=T2.REVALIDATE_NUMB
WHERE ROUND(T1.TOTAL_SELL_PRC,2)<>ROUND(T2.TOTAL_SELL_PRC,2)
Something like
Open in new window
You would then reference the additional field (no formula required): CSTQUTLN_TOT_SELL_PRC