Skip to Main Content

Issue in using Sequence with INSERT FIRST

RaunaqJun 24 2014 — edited Jun 24 2014

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

Comments
Post Details
Added on Jun 24 2014
5 comments
115 views