Archives of the TeradataForum

Message Posted: Thu, 09 Sep 2004 @ 20:13:57 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Craig

I noticed that you are not using the "a" alias in the "SET last_sale_date = b.last_sale_date"

You might wish to consider an insert/select with a UNION statement to populate an empty table. When you update you will use the transient journal whereas if you insert/select it will run much faster. If you have that flexibility, give it a try...

Try using the "a" alias...

SQL NOT TESTED.....

     UPDATE a
     FROM dssbatch.TMP_dpc_status_daily a, dssbatch.TMP_dpc_status_daily2
      b
     SET a.last_sale_date = b.last_sale_date
     WHERE a.branch_id = b.branch_id
     AND a.customer_no = b.customer_no
     AND b.last_sale_date > a.last_sale_date
     OR a.last_sale_date is null;

SQL NOT TESTED.....

     INSERT INTO dssbatch.TMP_dpc_status_daily_NEW
     Select a.branch_id, a.customer_no, b.last_sale_date
     FROM dssbatch.TMP_dpc_status_daily a, dssbatch.TMP_dpc_status_daily2
      b
     WHERE a.branch_id = b.branch_id
     AND a.customer_no = b.customer_no
     AND b.last_sale_date > a.last_sale_date
     OR a.last_sale_date is null
     UNION [ALL]
     Select a.branch_id, a.customer_no, a.last_sale_date
     FROM dssbatch.TMP_dpc_status_daily a, dssbatch.TMP_dpc_status_daily2
      b
     WHERE a.branch_id = b.branch_id
     AND a.customer_no = b.customer_no
     AND b.last_sale_date < a.last_sale_date
     OR a.last_sale_date is NOT null
     ;

SQL NOT TESTED.....

Best Regards

Chris Coffing
AFKS O&M Lead Teradata DBA
Certified Teradata Master

	<Prev	Next>		<<First	<Prev	Next>	Last>>