I want to create a view is more special, because there is a column is a dynamic column according to the application of the configuration of the patchwork,
Common to create a view is similar to the following,

 CREATE VIEW VIEW_TEST 
AS the SELECT 
SHDIATEST, 
NEXTMAINFREQ, 
AR_MANUFACTURER, 
OUTLETPRESSUNIT FROM TABLE 

For example, I now have a list of special column called CALCULATEDDATE, this column is calculated through configuration in the program (according to the different columns of other configuration, anyway, if use different column configurations, calculated value will be different), if I spell out the current configuration of the string is as follows,

 DECODE (ISDATE (EXTRADATE), 1, TO_CHAR ((TODATE (EXTRADATE) + INTERVAL '2' day (3)), 'YYYY/MM/DD), No Data *' * ') AS CALCULATEDDATE 

The EXTRADATE is true when I configure this special column use column,

I now think of a way is to create the following function, used to return to the table of UNIQUEKEY and CALCULATEDDATE columns, then it is ok to link when creating the view to CALCULATEDDATE columns, but because of my data is more, I have already handed the necessary parameters in this function, but the function is very time consuming, so the plan is not workable

 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); 


The BEGIN 
V_DataTable:='CV'; 
V_TenantKey:=ufGetTenantKeyByUser (IN_USERKEY); 
V_TemplateID:=UFGETTEMPLATEIDBYUSER (IN_USERKEY); 


The BEGIN 
The select a.S QLCALEXP | | "| | IN_FIELDNAME 
Into v_CalculatedDateSql 
The from GRIDCALCULATIONFIELD a 
Where a.U SERKEY=IN_USERKEY 
And al-qeada RIDDATASOURCE=IN_GRIDDATASOURCE 
And a.F IELDNAME=IN_FIELDNAME and a.T ENANTKEY=v_TenantKey; 
The EXCEPTION 
The WHEN no_data_found 
THEN 
V_CalculatedDateSql:=NULL; 
END; 


IF (LENGTH (v_CalculatedDateSql) & lt; 1) THEN 
V_CalculatedDateSql:='NULL AS CALCULATEDDATE'; 
END IF; 

V_Sql:='select uniquekey,' | | v_CalculatedDateSql 
| | 'from' | | v_DataTable | | 'where TENANTKEY=' '| | v_TenantKey | |' ' ' '; 

The OPEN cur FOR v_Sql; 

Loop 
The fetch cur into v_UniqueKey v_CalculatedDate; 
Exit the when cur % notfound; 
V_Table. The extend; 
V_Table (v_Table. Last) :=new CALCULATEDDATE_RECORD_TYPE_1 (v_UniqueKey v_CalculatedDate); 
end loop; 
The close cur; 

RETURN v_Table; 


END ufGetCVCalculatedDate_1; 

And I tried if direct execution of SQL statements alone, execution is no problems, only need more than 1 second

 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 

So I want to ask is, like me, is there any way to create the view can be used directly when the dynamic splicing to SQL column?

CodePudding user response:

Build dynamic view is in the process of the execute immediate directly, didn't understand what are you doing the following function is to,

CodePudding user response:

Upstairs, because I now the program USES a component devexpress, asp.net web form devexpress version, if want to support paging cannot use stored procedures, can do through view + entityframework (personally think that this component in such a way support paging really do not how, but can't, the program has been done for several years, can not suddenly all unused), so I'll need to create such a view, but like I mentioned calculateddate column is based on the configuration of dynamic, I wanted to know is there any good way to create such a view,

I this function actually returns 2 column, a column is the original table uniquekey, a column is calculateddate column, then I do correlation of the original table with this function, in this way can create views calculateddate columns, but not such performance,

CodePudding user response:

No one know

CodePudding user response:

Minimum also wants the configuration rules, table structure clear, you have made such a tuo mess can't execute code coming again, no one is willing to guess what on earth are you doing,