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?