oracle虚表能加筛选条件吗,万能报表开发说明 - 虚拟表支持条件查询

1、增加表x3_database_sql_where。这个表保存虚拟表支持哪些字段可以作为查询条件

2、虚拟表内部使用表的别名，要跟正常表对应的别名一致。这样在设置同一个查询条件的情况下，才能注入到内层虚拟表里面，提高查询速度。

例如：public void saveTableSQL(){

dictionaryService.deleteTable("sql_retail");

TableDictionarySqlRequest req=new TableDictionarySqlRequest();

req.setTableAlias("sql_retail");

req.setTableName("sql_retail");

req.setTableNameCn("零售单虚拟表");

req.setTableNameEn("sqlRetail");

List sqls=new ArrayList();

req.setSqls(sqls);

//查询

TableDictionarySqlCodeRequest selectSQl=new TableDictionarySqlCodeRequest();

selectSQl.setTableSqlId("sql_retail.selectSQl");

selectSQl.setTableAlias("sql_retail");

selectSQl.setSqlCodeType(SQLCodeType.SelectSQL);

selectSQl.setSqlCode("dretailnum.bills_id,dretailnum.style_id,s.style_name,dretailnum.acc_sums ");

sqls.add(selectSQl);

//from

TableDictionarySqlCodeRequest fromSQL=new TableDictionarySqlCodeRequest();

fromSQL.setTableSqlId("sql_retail.fromSQL");

fromSQL.setTableAlias("sql_retail");

fromSQL.setSqlCodeType(SQLCodeType.FromSQL);

fromSQL.setSqlCode("d_retail_num dretailnum left join a_goods_style s on dretailnum.style_id=s.style_id ");

sqls.add(fromSQL);

//where

TableDictionarySqlCodeRequest whereSQl=new TableDictionarySqlCodeRequest();

whereSQl.setTableSqlId("sql_retail.fromSQL");

whereSQl.setTableAlias("sql_retail");

whereSQl.setSqlCodeType(SQLCodeType.FieldWhereSQL);

whereSQl.setSqlCode("dretailnum.acc_nums>5");

sqls.add(whereSQl);

TableDictionarySqlWhereRequest whereCol1=new TableDictionarySqlWhereRequest();

whereCol1.setColumnId("style.brand_id");

whereCol1.setDataType(SQLDataType.Text);

whereCol1.setShowIndex(1);

whereCol1.setTableAlias("style");

whereCol1.setWhereOperation(SearchType.in);

whereCol1.setWhereRequired(true);

req.getWhereCols().add(whereCol1);

TableDictionarySqlWhereRequest whereCol=new TableDictionarySqlWhereRequest();

whereCol.setColumnId("dretailnum.create_date");

whereCol.setDataType(SQLDataType.Date);

whereCol.setShowIndex(0);

whereCol.setTableAlias("dretailnum");

whereCol.setWhereOperation(SearchType.between);

whereCol.setWhereRequired(true);

req.getWhereCols().add(whereCol);

ResponseMsg data1 = dictionaryService.createTableBySql(req);

System.err.println("getdepot:"+JSON.toJSONString(data1, true));

}这里使用到的表别名dretailnum就跟正常定义的d_retail_num表别名一致。

oracle 建立表脚本

CREATE TABLE "X6_BILLS_TEST"."X3_DATABASE_SQL_WHERE"

("TABLE_SQL_WHERE_ID" VARCHAR2(128 CHAR) NOT NULL ENABLE,

"CREATE_DATE" TIMESTAMP (6),

"CREATE_USER" VARCHAR2(32 CHAR),

"CREATE_USERNAME" VARCHAR2(32 CHAR),

"COLUMN_ID" VARCHAR2(64 CHAR),

"DATA_TYPE" VARCHAR2(32 CHAR),

"SHOW_INDEX" NUMBER(10,0),

"TABLE_ALIAS" VARCHAR2(64 CHAR),

"VERSION" NUMBER(19,0),

"WHERE_OPERATION" NUMBER(10,0),

"WHERE_REQUIRED" NUMBER(1,0),

PRIMARY KEY ("TABLE_SQL_WHERE_ID")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "X6_BILLS_TEST" ENABLE

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "X6_BILLS_TEST" ;