问题描述

oracle的merge into中 where子句不能使用别名问题

CREATE OR REPLACE PROCEDURE YAXIA0617.YHYX_FI_SERIES
AS
CUR_TIME DATE;--当前时间
PK_GROUP_TAG VARCHAR(20);--集团pk
PK_SOURCE_TAG VARCHAR(20);--资源Pk
BEGIN
SELECT SYSDATE-5/1440 INTO CUR_TIME FROM DUAL;
SELECT PK_GROUP INTO PK_GROUP_TAG FROM ORG_GROUP;

MERGE INTO BD_MATERIAL a2
USING (select * from TEMP_SERIES where STATUS = 0 and CREATE_DATE <= CUR_TIME) b2
ON (a2.CODE = b2.CLASS_CODE)
    WHEN MATCHED THEN
        UPDATE SET
            a2.NAME = b2.CLASS_NAME,
            a2.TS = to_char(sysdate,'yyyy-mm-dd HH24:MI:SS')
    WHEN NOT MATCHED THEN
        Insert
           (code, creationtime, creator, dataoriginflag, discountflag, dr, electronicsale, enablestate, fee,
            intolerance, iselectrans, latest, modifier, name, outcloselowerlimit, outtolerance, pk_group,
            pk_marbasclass, pk_material,
            pk_mattaxes, pk_measdoc, pk_org, pk_source,
            productfamily, retail, setpartsflag, ts, unitvolume, unitweight,version)
        Values
           (b2.CLASS_CODE,to_char(sysdate,'yyyy-mm-dd HH24:MI:SS'), '9999999', 0, 'N', 0, 'N', 2, 'N',
            0, 'N', 'Y', '~',b2.CLASS_NAME, 0, 0,PK_GROUP_TAG,
            (select pk_marbasclass from bd_marbasclass where code=b2.BRAND_CODE), to_char(SEQ_INTERFACE_DATA.nextval),
            (select pk_mattaxes from bd_mattaxes where MATTAXESCODE='CN001'),(select pk_measdoc from bd_measdoc where code='EA'),PK_GROUP_TAG, '~',
            'N', 'N', 'N', to_char(sysdate,'yyyy-mm-dd HH24:MI:SS'), 0, 0,  1);
   --处理pk_source和PK_MATERIAL一样
   select PK_MATERIAL into PK_SOURCE_TAG from BD_MATERIAL where CODE=b2.CLASS_CODE;
   if PK_SOURCE_TAG is not null then
   update BD_MATERIAL set pk_source=PK_SOURCE_TAG where CODE=b2.CLASS_CODE;
   end if;

update TEMP_SERIES set STATUS = 1,UPDATE_DATE=sysdate where STATUS = 0 and CREATE_DATE <= CUR_TIME;
COMMIT;

END;

执行上面语句时候，报错

ora-00904 “b2”."BRAND_CODE "标识符无效
就是where子句在此不能使用B2这个别名,应该怎么换呢？求大神们赐教，谢谢！