Now I have created one temporary table call TEMP in which I need to INSERT data from ablove table(TABLE_A).
I need to insert all the fields from above table but the it should be latest invoice charged(CHARGE_DT) on Particular _Product.
Please tell me the syntax or inbuilt function that I can use to grab the latest record in my INSERT statement.
This is just a sample data, but I have many columns from different tables to be reported so GROUP BY cluase will be not a feasible approach.
Inshort I need below two rows to be inserted in my TEMP table.
declare global temporary table x (product_id int, charge_dt date) on commit preserve rows not logged with replace;
insert into session.x
group by product_id;
insert into temp
select * from table a
where (product_id,charge_dt) in (select product_id,charge_dt from session.x);
insert into TEMP
with t1 (product_id,charge_dt) as
(select product_id,max(charge_dt) from table_a group by product_id)
select * from table_a
where (product_id,charge_dt) in (select product_id,charge_dt from t1)
INSERT INTO temp
SELECT product_id , invoice_nbr , creation_dt , charge_dt
, invoice_amt , ifee , invoice_status
FROM (SELECT a.*
OVER(PARTITION BY product_id
ORDER BY charge_dt DESC) AS r_n
FROM table_a a
WHERE r_n = 1