insert into log_tbl(log_date, job)
select max(first_date), ...
from   MYTABLE

quote:

---

Originally posted by khtan

then you have to post the big query here

I posted half of the query.

The last modification date is the max(date) that i want to insert into the log table.

INSERT INTO mytable
(
SlsDcNo
,OrdTpCd
,LinItmNo
,SOrdEDt
,CarriCd
,CstPONo
,SlsOrgCd
,SlsOrgDs
,DisChnCd
,DisChnDs
,SlsOffCd
,SlsOffDs
,IncoTDel
,CSRUID
,CrdtStCd
,CrdtStDs
,OrdStCd
,OrdStDs
,DelBlkCd
,DelBlkDs
,BilBlkCd
,BilBlkDs
,PushSlIn
,TotAOrd
,HDelFlg
,RejStCd
,RejStDs
,HDelStCd
,HDelStDs
,OBlkStCd
,OBlkStDs
,HLstMdDt
,HInOdsDt
,HLstMUsr
,HLstOUsr
)
--**
SELECT
h.firstno
,h.typeno
,s.lineno
,h.[sales_order_entry_date]
,h.[carrier_code]
,h.[customers_po_number]
,h.[sales_org]
,CASE WHEN o.language = 'E' THEN o.description END
,h.[dist_channel]
,CASE WHEN d.language = 'E' THEN d.description END
,h.[sales_office]
,CASE WHEN so.language = 'E' THEN so.description END
,h.[inco_terms_delivery]
,h.[csr_user_id]
,h.[credit_status_code]
,CASE WHEN cs.language = 'E' THEN cs.description END
,h.[order_status_code]
,CASE WHEN os.language = 'E' THEN os.description END
,h.[delivery_block_code]
,CASE WHEN db.language = 'E' THEN db.description END
,h.[billing_block_code]
,CASE WHEN bb.language = 'E' THEN bb.description END
,h.[push_sale_indicator]
,h.[total_amt_of_order]
,h.[delete_flag]
,h.[rejection_status_code]
,CASE WHEN r.language = 'E' THEN r.description END
,h.[delivery_status_code]
,CASE WHEN dv.language = 'E' THEN dv.description END
,h.[overall_blocked_status_code]
,CASE WHEN ov.language = 'E' THEN ov.description END
,h.[last_modification_datetime]
,h.[inserted_into_ods_datetime]
,h.[last_modification_user_id]
,h.[inserted_into_ods_user_id]
FROM first_table h
INNER JOIN sales_order] s
ON h.firstno = s.firstno
AND h.typeno = s.typeno
LEFT JOIN [Sales_o] o
ON h.[SALES_ORG] = o.code
LEFT JOIN [dist_c] d
ON h.[dist_c] = d.code
LEFT JOIN [sales_office] so
ON h.[sales_office] = so.code
LEFT JOIN [sales_order_Credit_status] cs
ON h.[credit_status_code] = cs.code
INNER JOIN D_PLANT l
ON s.[plant_code] = l.PlantCd
INNER JOIN D_MATERIAL m
ON s.[material_code] = m.MatCd
INNER JOIN D_EMPLOYEEx e1
ON h.[sales_code] = e1.EmpCd


Thx

f.

---

KH

---