โ05-22-2023 10:41 PM
In this Scenario, I have to insert data onto an Oracle DB. Before that one Iโm performing a Left Outer join and separating the data (for Update and Insert) by using router. In Insert snap I have to insert all the data ,before that one Need to check whether the data is matching with Target table / not . If yes ignore those records if not then perform insert . This was the whole logic .
I have to take data columns from mapper and check the columns with oracle target table if data is already exists/not. But, Unfortunately It was troughing errors.
INSERT INTO Schema.tablename (DEST,FROM_D,INSRT_BY,INSRT_DT,LVL,TO_D,UPDT_DT) VALUES (โโ+$DEST+โโ,โโ+$FROM_D+โโ,โโ+$INSRT_BY+โโ,โโ+$INSRT_DT+โโ,โโ+$LVL+โโ,โโ+$TO_D+โโ,โโ+$UPDT_DT+โโ) WHERE (DEST<>โโ+$DEST +โโ AND LVL<>โโ+$LVL +โโ)
Where condition columns are Primary Keys to the table.
DEST,FROM_D,INSRT_BY,INSRT_DT,LVL,TO_D,UPDT_DT ---- Table columns
โโ+$DEST+โโ,โโ+$FROM_D+โโ,โโ+$INSRT_BY+โโ,โโ+$INSRT_DT+โโ,โโ+$LVL+โโ,โโ+$TO_D+โโ,โโ+$UPDT_DT+โโ ---- Mapper columns/Data
I think Experts will find a best solution for this issue.
@bojanvelevski @del @AleksandarAngelevski @Spiro_Taleski
Please help on this issue.
Solved! Go to Solution.
โ05-26-2023 12:25 AM
Solved this scenario in a different way.
Insert handled by using inner join.
For Update
Here I have to update 2 columns($status, $ID) based on 6 key columns matching data.
So, I have read the target table as reference table($col_Ref) and regular/new data as left side data and performing an inner join based on 6 key columns.
There I found the matching data.
I put 1 filter to sort $status=โActiveโ
I just mapped 2 ($status hardcoded as โINACTIVEโ I have to update the $status column if $stat=โActiveโ, $ID taken from filter ) columns which I want to update in the Target Table.
Finally in Oracle Update snap mentioned Schema and table name.
In where clause $Status=โActiveโ.
This was one of the ways that I found for resolving this issue.
If we have any other ways to handle this, then it could be helpful.
โ05-23-2023 04:01 PM
@sshaik, I donโt have an Oracle DB with which I can test this suggestion, so consider it with that disclaimer; but I think what you may need to do is modify your WHERE query to be a NOT EXISTS subquery.
Something similar too:
WHERE NOT EXISTS (SELECT NULL FROM Schema.tablename WHERE DEST = $DEST AND LVL = $LVL)
โ05-23-2023 10:28 PM
Thanks for the reply.
The WHERE condition why I wrote like that means I canโt read the data from any table . Itโs a combination of 2 tables data then have to perform Change capture (update,delete,insert). So, is there any option to resolve this issue?
Not able to read/fetch those mapper columns in the DB snap thatโs the issue here.
What I want is how to write the query without selecting data from DB , by using WHERE clause we have to do .
โ05-26-2023 12:25 AM
Solved this scenario in a different way.
Insert handled by using inner join.
For Update
Here I have to update 2 columns($status, $ID) based on 6 key columns matching data.
So, I have read the target table as reference table($col_Ref) and regular/new data as left side data and performing an inner join based on 6 key columns.
There I found the matching data.
I put 1 filter to sort $status=โActiveโ
I just mapped 2 ($status hardcoded as โINACTIVEโ I have to update the $status column if $stat=โActiveโ, $ID taken from filter ) columns which I want to update in the Target Table.
Finally in Oracle Update snap mentioned Schema and table name.
In where clause $Status=โActiveโ.
This was one of the ways that I found for resolving this issue.
If we have any other ways to handle this, then it could be helpful.