I have two tables.If there are new records in CUST_new table based on NAM column.
I have to take the new key by selecting the maximum value+1 from the old table.
cust_old table:-
cust_key,NAM,state
1001 christina illinois
cust_New table
NAM,state
christina california
vamsi Andhrapradesh
vishnu Andhra Pradesh
required output:-
cust_key,NAM,state
1002 vamsi Andhrapradesh
1003 vishnu Andhra Pradesh
Tried below things:-
To identify those two records
SELECT
N.NAM AS ,
N.STATE
FROM CUST_new N
LEFT JOIN CUST_old O
ON O.nam = n.nam
WHERE O.Nam IS NULL
For maximum:-
SELECT MAX(cust_key) AS max_id
FROM CUST_old
For generating Numbers:-
SELECT LEVEL just_a_column FROM dual CONNECT BY LEVEL <= 2
I am not sure how to combine all the 3 things to get required output
SELECT b.level+c.max_id,
a.NAM,
a.stat
(SELECT N.NAM AS NAM,
N.STATE AS stat
FROM CUST_new N
LEFT JOIN CUST_old O
ON O.nam = n.nam
WHERE O.Nam IS NULL
) a
( SELECT LEVEL just_a_column FROM dual CONNECT BY LEVEL <= 2
) b
(SELECT MAX(cust_key) AS max_id FROM CUST_old
)c