CODEWITHSUNDEEP
sqloracle-databaseselectinsertdistinct
SaintLike
SaintLike

Reputation: 9579

Select insert with sequence and distinct

I'm trying to perform a select insert where I need to use a sequence and a distinct clause.

I know we can't use sequences with distinct so, I've nested a subquery inside the FROM, like this:

INSERT INTO  SchemaName.XXSON_ST_B2B_CTR_PRICING
(SEQ_NO,SEEB_KEY,MESSAGE_ID,MESSAGE_TYPE,CREATE_DTIME,CHANGE_DTIME,STATUS)
   SELECT
          XXSON.XXSON_B2B_CTR_PRICING_SEQ.NEXTVAL   AS SEQ_NO,
         'SESS_NO'          AS SEEB_KEY,
          srp.ITEM          AS MESSAGE_ID,
         'MESSAGE_TYPE'     AS MESSAGE_TYPE,
          sysdate           AS CREATE_DTIME,
          sysdate           AS CHANGE_DTIME,
          'I'               AS STATUS
   FROM 
     (SELECT distinct srp.ITEM
      FROM  SchemaName.NB_PRICELIST_CTRL pctrl,
            SchemaName.NB_PRICELIST_SRP srp
      WHERE STATUS = 'W'
      AND SOURCE_TABLE = 'NB_PRICELIST_SRP');

However, with this, I can't insert the srp.ITEM as MESSAGE_ID because I have an invalid identifer (ORA-00904), no surprise here. Is there any workaround so I can do the insert properly?

Upvotes: 1

Views: 835

Answers (3)

JMariña
JMariña

Reputation: 324

The error message you provided deals with missing or invalid columns. I believe the specific problem with the error message is the srp alias in 

srp.item as Message_ID

since you haven't aliased the combination of the 2 tables in the parenthesis (you aliased the individual tables inside, but this does not get carried out). A simple fix is to remove 'srp.' from the front of 'item' in the top statement.

INSERT INTO  SchemaName.XXSON_ST_B2B_CTR_PRICING
(SEQ_NO,SEEB_KEY,MESSAGE_ID,MESSAGE_TYPE,CREATE_DTIME,CHANGE_DTIME,STATUS)
   SELECT
          XXSON.XXSON_B2B_CTR_PRICING_SEQ.NEXTVAL   AS SEQ_NO,
         'SESS_NO'          AS SEEB_KEY,
          ITEM          AS MESSAGE_ID,
         'MESSAGE_TYPE'     AS MESSAGE_TYPE,
          sysdate           AS CREATE_DTIME,
          sysdate           AS CHANGE_DTIME,
          'I'               AS STATUS
   FROM 
     (SELECT distinct srp.ITEM
      FROM SchemaName.NB_PRICELIST_CTRL pctrl,
        SchemaName.NB_PRICELIST_SRP srp
      WHERE STATUS = 'W'
      AND SOURCE_TABLE = 'NB_PRICELIST_SRP')

On a separate note, I didn't quite understand the inclusion pctrl.

Upvotes: 0

Jerry
Jerry

Reputation: 19

Please try the below query this might be help you. 

INSERT INTO  SchemaName.XXSON_ST_B2B_CTR_PRICING
(SEQ_NO,SEEB_KEY,MESSAGE_ID,MESSAGE_TYPE,CREATE_DTIME,CHANGE_DTIME,STATUS)
   SELECT
          XXSON.XXSON_B2B_CTR_PRICING_SEQ.NEXTVAL   AS SEQ_NO,
         'SESS_NO'          AS SEEB_KEY,
          (SELECT distinct srp.ITEM FROM  SchemaName.NB_PRICELIST_CTRL pctrl, SchemaName.NB_PRICELIST_SRP srp WHERE STATUS = 'W' AND SOURCE_TABLE = 'NB_PRICELIST_SRP') AS MESSAGE_ID,
         'MESSAGE_TYPE'     AS MESSAGE_TYPE,
          sysdate           AS CREATE_DTIME,
          sysdate           AS CHANGE_DTIME,
          'I'               AS STATUS;

Regards.

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17157

To get rid of ORA-00904: invalid identifier srp.ITEM you have to assign an alias to a derived table which serves as a normal table stored in memory. An alias in FROM clause assigned inside a block is not visible outside.

INSERT INTO  SchemaName.XXSON_ST_B2B_CTR_PRICING
    (SEQ_NO,SEEB_KEY,MESSAGE_ID,MESSAGE_TYPE,CREATE_DTIME,CHANGE_DTIME,STATUS)
        SELECT
                XXSON.XXSON_B2B_CTR_PRICING_SEQ.NEXTVAL   AS SEQ_NO,
                'SESS_NO'          AS SEEB_KEY,
                alias.ITEM          AS MESSAGE_ID,
                'MESSAGE_TYPE'     AS MESSAGE_TYPE,
                sysdate           AS CREATE_DTIME,
                sysdate           AS CHANGE_DTIME,
                'I'               AS STATUS
        FROM 
        (SELECT distinct srp.ITEM
            FROM  SchemaName.NB_PRICELIST_CTRL pctrl,
                    SchemaName.NB_PRICELIST_SRP srp
            WHERE STATUS = 'W'
            AND SOURCE_TABLE = 'NB_PRICELIST_SRP') alias;

Upvotes: 2

Related Questions