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这个别名,应该怎么换呢?求大神们赐教,谢谢!
(select pk_marbasclass from bd_marbasclass where code=b2.BRAND_CODE) 这句错了 应该这么写 (select pk_marbasclass from bd_marbasclass where code in (select BRAND_CODE from TEMP_SERIES where STATUS = 0 and CREATE_DATE <= CUR_TIME))