CREATE OR REPLACE PROCEDURE TESTDTA.GETITEMRANKING
IS
TCount integer;
StrSql varchar2(4000);
begin
select COUNT(*) INTO TCount from all_all_tables where table_Name='ITEMRANKING';
if TCount>0 then
execute immediate 'drop table testdta.ITEMRANKING';
end if;
declare
StrSql varchar2(4000);
begin
StrSql:='create table testdta.ITEMRANKING as SELECT a.IBITM,'' '' as ranking,B.ITEM_VALUES,B.TOTAL_VALUES,frequency FROM TESTDTA.F4102 A LEFT OUTER JOIN (
SELECT SDITM,round(SUM(SDITWT/10000),2) AS ITEM_VALUES,(SELECT round(SUM(SDITWT/10000),2) FROM TESTDTA.f42119
WHERE SDDCTO<>''SW'' AND SDprp4=''THD'' AND SDMCU='' HK101''
AND SDIVD BETWEEN to_jde_date(TRUNC(Trunc(SYSDATE,''MONTH'') - 1,''MONTH'')-366)
AND to_jde_date(Trunc(SYSDATE, ''MONTH'') - 1 / 86400)) AS TOTAL_VALUES FROM TESTDTA.f42119
WHERE SDDCTO<>''SW'' AND SDprp4=''THD'' AND SDMCU='' HK101''
AND SDIVD BETWEEN to_jde_date(TRUNC(Trunc(SYSDATE,''MONTH'') - 1,''MONTH'')-366)
AND to_jde_date(Trunc(SYSDATE, ''MONTH'') - 1 / 86400)
GROUP BY SDITM
ORDER BY ITEM_VALUES DESC
) B ON A.IBITM=B.SDITM
LEFT OUTER JOIN
(SELECT SDITM,COUNT(SDDOCO) as frequency FROM(
SELECT DISTINCT SDDOCO,SDITM FROM TESTDTA.F42119
WHERE SDDCTO<>''SW'' AND SDprp4=''THD'' AND SDMCU='' HK101''
AND SDIVD BETWEEN to_jde_date(TRUNC(Trunc(SYSDATE,''MONTH'') - 1,''MONTH'')-366)
AND to_jde_date(Trunc(SYSDATE, ''MONTH'') - 1 / 86400))
GROUP BY SDITM
)C on a.ibitm=c.sditm
WHERE IBMCU='' HK101'' and a.ibprp4=''THD''
ORDER BY B.ITEM_VALUES desc';
--dbms_output.put_line(StrSql);
execute immediate StrSql;
--commit;
--UPDATE TESTDTA.ITEMRANKING SET ranking='F' WHERE ranking IS NULL OR ranking=' ';
--COMMIT;
END;
下面是运行结果提示:
SQL> EXEC TESTDTA.GETITEMRANKING;PL/SQL procedure successfully completedSQL> EXEC TESTDTA.GETITEMRANKING;begin TESTDTA.GETITEMRANKING; end;ORA-00904: "TO_JDE_DATE": invalid identifier
ORA-06512: at "TESTDTA.GETITEMRANKING", line 32
ORA-06512: at line 1SQL>
IS
TCount integer;
StrSql varchar2(4000);
begin
select COUNT(*) INTO TCount from all_all_tables where table_Name='ITEMRANKING';
if TCount>0 then
execute immediate 'drop table testdta.ITEMRANKING';
end if;
declare
StrSql varchar2(4000);
begin
StrSql:='create table testdta.ITEMRANKING as SELECT a.IBITM,'' '' as ranking,B.ITEM_VALUES,B.TOTAL_VALUES,frequency FROM TESTDTA.F4102 A LEFT OUTER JOIN (
SELECT SDITM,round(SUM(SDITWT/10000),2) AS ITEM_VALUES,(SELECT round(SUM(SDITWT/10000),2) FROM TESTDTA.f42119
WHERE SDDCTO<>''SW'' AND SDprp4=''THD'' AND SDMCU='' HK101''
AND SDIVD BETWEEN to_jde_date(TRUNC(Trunc(SYSDATE,''MONTH'') - 1,''MONTH'')-366)
AND to_jde_date(Trunc(SYSDATE, ''MONTH'') - 1 / 86400)) AS TOTAL_VALUES FROM TESTDTA.f42119
WHERE SDDCTO<>''SW'' AND SDprp4=''THD'' AND SDMCU='' HK101''
AND SDIVD BETWEEN to_jde_date(TRUNC(Trunc(SYSDATE,''MONTH'') - 1,''MONTH'')-366)
AND to_jde_date(Trunc(SYSDATE, ''MONTH'') - 1 / 86400)
GROUP BY SDITM
ORDER BY ITEM_VALUES DESC
) B ON A.IBITM=B.SDITM
LEFT OUTER JOIN
(SELECT SDITM,COUNT(SDDOCO) as frequency FROM(
SELECT DISTINCT SDDOCO,SDITM FROM TESTDTA.F42119
WHERE SDDCTO<>''SW'' AND SDprp4=''THD'' AND SDMCU='' HK101''
AND SDIVD BETWEEN to_jde_date(TRUNC(Trunc(SYSDATE,''MONTH'') - 1,''MONTH'')-366)
AND to_jde_date(Trunc(SYSDATE, ''MONTH'') - 1 / 86400))
GROUP BY SDITM
)C on a.ibitm=c.sditm
WHERE IBMCU='' HK101'' and a.ibprp4=''THD''
ORDER BY B.ITEM_VALUES desc';
--dbms_output.put_line(StrSql);
execute immediate StrSql;
--commit;
--UPDATE TESTDTA.ITEMRANKING SET ranking='F' WHERE ranking IS NULL OR ranking=' ';
--COMMIT;
END;
下面是运行结果提示:
SQL> EXEC TESTDTA.GETITEMRANKING;PL/SQL procedure successfully completedSQL> EXEC TESTDTA.GETITEMRANKING;begin TESTDTA.GETITEMRANKING; end;ORA-00904: "TO_JDE_DATE": invalid identifier
ORA-06512: at "TESTDTA.GETITEMRANKING", line 32
ORA-06512: at line 1SQL>
你把它去掉试试看
ORA-06512: at "TESTDTA.GETITEMRANKING", line 32
ORA-06512: at line 1
AND to_jde_date(Trunc(SYSDATE, ''MONTH'') - 1 / 86400)) AS TOTAL_VALUES FROM TESTDTA.f42119 TO_JDE_DATE oracle好像没有这个函数,难道是我见识太少?郁闷
USE SYS TO DO:
SQL> grant select on yourUSER.TO_JDE_DATE to yourUSER;
1:确保有相应的权限访问.
2:在不同的SCHEMA中引用时最好前面加上SCHEMA NAME.
SDIVD BETWEEN to_jde_date(TRUNC(Trunc(SYSDATE,''MONTH'') - 1,''MONTH'')-366)
AND to_jde_date(Trunc(SYSDATE, ''MONTH'') - 1 / 86400))
大致是什么意思?