Guys ,
i have the below INSERT FIRST statement
As with INSERT statement , it is a conditional insert
I have 2 conditions in my INSERT statement and in one of the conditions (2nd condition) i am using a sequence for one of the columns
Now the issue is when i am inserting data through that statement when 1st conditions matches the sequence present in the second condition is also executed (the statement in 2nd condition is not executed , only the sequence is )
Below is the sample statement
{noformat}
| | |
| | | INSERT FIRST |
| | | --<>-- |
| | | WHEN TEST_COL IS NOT NULL |
| | | | THEN |
| | | | | INTO TARG_TABLE |
| | | | | | ( |
| | | | | | PART_ID | | | |
| | | | | | ,COL_LIST |
| | | | | | ) | | |
| | | | | VALUES |
| | | | | | ( |
| | | | | | DUR_UK_ID, |
| | | | | | COL_LIST_VALUES |
| | ) |
| | | --<>-- |
| | | WHEN TEST_COL IS NULL |
| | | | then |
| | | | | INTO TARG_TABLE |
| | | | | | ( |
| | | | | | PART_ID | | | |
| | | | | | COL_LIST, |
| | | | | | ) | | |
| | | | | VALUES |
| | | | | | ( |
| | | | | | SEQUENCE_NAME.NEXTVAL, |
| | | | | | COL_LIST_VALUES |
| | | | | | ) |
| | into DATA_TABLE subpartition for(10, 'TARG_TABLE') |
| | | | | | ( COL_LIST, dur_uk_id) |
| | | | | values |
| | | | | | (COL_LIST_VALUES |
| | | | | | ,SEQUENCE_NAME.NEXTVAL |
| | | | | | ) |
| | | select |
| | | | S.COL_LIST |
| | | , KI.TEST_COL |
| | | FROM "I006GTARG_TABLE" S |
| | | left outer join |
| | | | ( SELECT * FROM DATA_TABLE |
| | | | | WHERE table_name = 'TABLE_NAME' |
| | | | ) KI |
| | | | ON ( KI.COL = S.COL) |
| | |
{noformat}
I am on 11g