Forum Discussion

sshaik's avatar
sshaik
New Contributor III
3 years ago
Solved

Unique Constraint Issue in Oracle while inserting Data

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.

3 Replies

  • alchemiz's avatar
    alchemiz
    Contributor III
    2 years ago
    Copy Link

    Hi rriley99 ,

    Good day, you can use JSON generator to setup your "targets" then make sure that "process array" to iterate for each object

    In order to pass/receive the "values" in the child you need to have a snap with 1 input open

    Parent pipeline:

    Sample child pipeline:

     

    • rriley99's avatar
      rriley99
      New Contributor III
      2 years ago
      Copy Link

      Ok I understand, so I am passing this JSON as a parameter, using the JSON GENERATOR Snap but it's just a {"key": "json string"}.

      How does that work?

  • del's avatar
    del
    Contributor III
    3 years ago
    Copy Link

    @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)

    • sshaik's avatar
      sshaik
      New Contributor III
      3 years ago
      Copy Link

      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 .

      • sshaik's avatar
        sshaik
        New Contributor III
        3 years ago
        Copy Link

        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.