If seg file having 10 records
ex:eid
1
2
"
"
10
if oracle database having 100 records
ex:eid
1
2
"
"
100
how to delete matched records permenently from oracle
database using datastage ?
- 5 Answers
- 8267 Views
- Infotech, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
we can delete in 2 ways:
1) in TGT Oracle Stage,
select 'Write Mode' as 'DELETE'
and write the delete Query as
"DELETE FROM EMP_TGT
WHERE EID IN (SELECT EID FROM EMP_SRC)"
2)Select 'Write Mode' as 'UPDATE' and write some dummy update query(This statement will not execute as WHERE condition 1=2) as
"UPDATE EMP SER EID='1111' WHERE 1=2"
then write delete query in 'Run before SQL statemets' as
"DELETE FROM EMP_TGT
WHERE EID IN (SELECT EID FROM EMP_SRC)"
| Is This Answer Correct ? | 4 Yes | 0 No |
Answer / vaibhav
We can perform inner join between seq file & oracle stage and then in target oracle stage have write method as Delete where we can specify delete query using orchestrate incoming rows..
| Is This Answer Correct ? | 3 Yes | 0 No |
Simple job:
SEQ>>>ORACLE_STAGE.
in ORCL_STAGE: SPECIFY WRITE METHOD: DELETE ROWS
specify the matching column as key column in column definition.
| Is This Answer Correct ? | 2 Yes | 0 No |
Answer / shilpasagarg
1)The approach is to load the seq file data into one
temporary table say EMP_TEMP. (SEQ -> ODBC Stage (Oracle
(EMP_TEMP table)
EMP_TEMP contains
eid
1
2
"
"
10
2)Then take the ODBC Stage to connect to Oracle DB (EMP
table).
EMP table contains
eid
1
2
"
"
100
Here use the below query to delete the matched EID's
permanantly from oracle DB
DELETE EMP
FROM EMP
INNER JOIN EMP_TEMP
ON EMP.EID=EMP_TEMP.EID
| Is This Answer Correct ? | 0 Yes | 3 No |
what is combinability and non combinability?
I am defining one varaible parameter date in job parameters.I want use this variable date in where clause in source query.
How complex jobs are implemented in datstage to improve performance?
A signal has a wavelength of 1 micro min in air.how far can the front of the wave travel during 1000periods?
What are the areas of application?
Explain briefly scd type2 in datastage7.5x2(parallel)
which unix commands mostly used in datastage
Hi All, I have a file. i need to fetch the records between first and last records by using transform stage. EX:- Source: EMPNO EMPNAME 4567 shree 6999 Ram 3265 Venkat 2655 Abhi 3665 Vamsi 5852 Amit 3256 Sagar 3265 Vishnu Target: EMPNO EMPNAME 6999 Ram 3265 Venkat 2655 Abhi 3665 Vamsi 5852 Amit 3256 Sagar I dont wan't to Shree and vishnu records.we can fetch another way also but How can I write the function in transform stage?
Hi , Today 1000 records updated, tomorrow 500 records updated how to find that?
what is advantages of snowflake when it is used?
Hi am sundar, i have datas like 00023-1010 00086-1010 00184F2-1010 . . . . SCH-AS-1010 200-0196-039 . . . Now i want the result as values before the delimiter should come under the column ITEM_CODE and values after the delimiter should come under the column LOC_CODE.. But some datas like "SCH-AS-1010", for this, "SCHAS" should come under the column ITEM_CODE and 1010 should come under the column LOC_CODE.. Pls help me..
count number of deptno in a emp table?
Teradata 
