{Denodo}基本ビュー作成SQLの生成

複数のSQLを流すと結果が表示されない
VQLシェルからクリップボードコピー時にヘッダーが含まれるので除く

-- 1. MySQLの場合

-- step 1 基本ビュー作成SQL作成SQLの生成

select 'select creation_vql from GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW ()
where data_source_name = ''ds01''
and catalog_name =''test''
and table_name = '''|| table_name||'''
and base_view_name = ''b_'||table_name||'''
and folder =''/02bv''
and database_name = ''db01''
;'
from GET_JDBC_DATASOURCE_TABLES()
where input_datasource_name = 'ds01'
and input_catalog_name = 'test'
and type = 'TABLE'
order by table_name
limit 1
;

-- step2 キャッシュモード変更SQLの生成

select 'alter table "b_'||table_name||'"
CACHE FULL
TIMETOLIVEINCACHE NOEXPIRE
;'
from GET_JDBC_DATASOURCE_TABLES()
where input_datasource_name = 'ds01'
and input_catalog_name = 'test'
and type = 'TABLE'
order by table_name
limit 1
;

-- 2. Oracle の場合

-- step 1 基本ビュー作成SQL作成SQLの生成

select 'select creation_vql from GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW ()
where data_source_name = ''ds01''
and schema_name =''TEST''
and table_name = '''|| table_name||'''
and base_view_name = ''b_'||table_name||'''
and folder =''/02bv''
and database_name = ''db02''
;'
from GET_JDBC_DATASOURCE_TABLES()
where input_datasource_name = 'ds01'
and input_schema_name = 'TEST'
and type = 'TABLE'
order by table_name
limit 1
;

-- step2 キャッシュモード変更SQLの生成

select 'alter table "b_'||table_name||'"
CACHE FULL
TIMETOLIVEINCACHE NOEXPIRE
;'
from GET_JDBC_DATASOURCE_TABLES()
where input_datasource_name = 'ds01'
and input_schema_name = 'TEST'
and type = 'TABLE'
order by table_name
limit 1
;

-- 3. PostgreSQL の場合

-- step 1 基本ビュー作成SQL作成SQLの生成

select 'select creation_vql from GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW ()
where data_source_name = ''ds01''
and schema_name =''public''
and table_name = '''|| table_name||'''
and base_view_name = ''b_'||table_name||'''
and folder =''/02bv''
and database_name = ''db03''
;'
from GET_JDBC_DATASOURCE_TABLES()
where input_datasource_name = 'ds01'
and input_schema_name = 'public'
and type = 'TABLE'
order by table_name
limit 1
;

-- step2 キャッシュモード変更SQLの生成

select 'alter table "b_'||table_name||'"
CACHE FULL
TIMETOLIVEINCACHE NOEXPIRE
;'
from GET_JDBC_DATASOURCE_TABLES()
where input_datasource_name = 'ds01'
and input_schema_name = 'public'
and type = 'TABLE'
order by table_name
limit 1
;

-- 4. SQL Server の場合

-- step 1 基本ビュー作成SQL作成SQLの生成

select 'select creation_vql from GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW ()
where data_source_name = ''ds01''
and catalog_name =''test''
and schema_name =''dbo''
and table_name = '''|| table_name||'''
and base_view_name = ''b_'||table_name||'''
and folder =''/02bv''
and database_name = ''db04''
;'
from GET_JDBC_DATASOURCE_TABLES()
where input_datasource_name = 'ds01'
and input_catalog_name = 'test'
and input_schema_name = 'dbo'
and type = 'TABLE'
order by table_name
limit 1
;

-- step2 キャッシュモード変更SQLの生成