17,086
社区成员
发帖
与我相关
我的任务
分享
CREATE VIEW VIEW_TEST
AS SELECT
SHDIATEST ,
NEXTMAINFREQ ,
AR_MANUFACTURER ,
OUTLETPRESSUNIT FROM TABLE
DECODE(ISDATE(EXTRADATE),1,TO_CHAR((TODATE(EXTRADATE) + INTERVAL ' 2' day(3)), 'YYYY/MM/DD'),'*No Data*') AS CALCULATEDDATE
create or replace FUNCTION ufGetCVCalculatedDate_1 (
IN_USERKEY Varchar2,
IN_GRIDDATASOURCE Varchar2,
IN_FIELDNAME Varchar2)
RETURN CALCULATEDDATE_TABLE_TYPE_1
AS
v_TenantKey Varchar2(40);
v_DataTable Varchar2(10);
v_UniqueKey Varchar2(40);
v_CalculatedDateSql CLOB;
v_CalculatedDate Varchar2(50);
v_Sql CLOB;
v_Table CALCULATEDDATE_TABLE_TYPE_1 := CALCULATEDDATE_TABLE_TYPE_1();
TYPE ref_cursor IS REF CURSOR;
cur ref_cursor;
v_TemplateID VARCHAR2(40);
RowCount NUMBER(10);
BEGIN
v_DataTable :='CV';
v_TenantKey := ufGetTenantKeyByUser(IN_USERKEY);
v_TemplateID := UFGETTEMPLATEIDBYUSER(IN_USERKEY);
BEGIN
select a.SQLCALEXP ||' ' ||IN_FIELDNAME
into v_CalculatedDateSql
from GRIDCALCULATIONFIELD a
where a.USERKEY=IN_USERKEY
and a.GRIDDATASOURCE=IN_GRIDDATASOURCE
and a.FIELDNAME=IN_FIELDNAME AND a.TENANTKEY=v_TenantKey;
EXCEPTION
WHEN no_data_found
THEN
v_CalculatedDateSql := NULL;
END;
IF(LENGTH(v_CalculatedDateSql) < 1 ) THEN
v_CalculatedDateSql := 'NULL AS CALCULATEDDATE';
END IF;
v_Sql := 'select uniquekey,' ||v_CalculatedDateSql
||' from '||v_DataTable || ' where TENANTKEY='''||v_TenantKey||'''';
OPEN cur FOR v_Sql;
loop
fetch cur into v_UniqueKey,v_CalculatedDate;
exit when cur%notfound;
v_Table.extend;
v_Table(v_Table.last) := new CALCULATEDDATE_RECORD_TYPE_1(v_UniqueKey, v_CalculatedDate);
end loop;
close cur;
RETURN v_Table;
END ufGetCVCalculatedDate_1;
SELECT
SHDIATEST ,
NEXTMAINFREQ ,
AR_MANUFACTURER ,
OUTLETPRESSUNIT, DECODE(ISDATE(EXTRADATE),1,TO_CHAR((TODATE(EXTRADATE) + INTERVAL ' 2' day(3)), 'YYYY/MM/DD'),'*No Data*') AS CALCULATEDDATE FROM TABLE