Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Dynamic SQL Generation to Load Data from Multiple Sources

The following code is what I have thus far:

SET vDataSource_ServerName='';

SET vDataSource_DatabaseName='';

SET vDataSource_SchemaName='';

SET vDataSource_DIR='';

SET vSQL=' ';

SET vIdx=1;

For vIdx = 1 to NoOfRows('REF_DATA_SOURCES')

  Let vDataSource_ServerName =Peek('ServerName',vIdx,'REF_DATA_SOURCES');

  Let vDataSource_DatabaseName=Peek('DatabaseName',vIdx,'REF_DATA_SOURCES');

  Let vDataSource_SchemaName =Peek('SchemaName',vIdx,'REF_DATA_SOURCES');

  Let vDataSource_DIR =Peek('DIR',vIdx,'REF_DATA_SOURCES');

  //LET vSQL = $(vSQL) & 'SELECT * from ' & $(vDataSource_ServerName) & '.' & $(vDataSource_DatabaseName) & '.' & $(vDataSource_SchemaName) & '.DiagCdMst with (nolock)';

  SET vSQL = '

  select distinct

   ''DP_' & $(vDataSource_DIR) & ''' [SourceSystem]

  , ''POSCd'' [CodeType]

  , LTRIM(RTRIM([POSCd])) [CodeName]

  , LTRIM(RTRIM([PlcOfSrvcDesc])) [CodeDesc]

  from ' & $(vDataSource_ServerName) & '.' & $(vDataSource_DatabaseName) & '.' & $(vDataSource_SchemaName) & '.PlcOfSrvcCdMst [poscm] with (nolock)';

  POSCdMst:

  LOAD

  AutoNumber( [SourceSystem] & '|' &

  [CodeType] & '|' &

  [CodeName] & '|' &

  [CodeDesc]

   ) as [ID]

  , [SourceSystem]

  , [CodeType]

  , [CodeName]

  , [CodeDesc]

  ;

  SQL $(vSQL);

Next vIdx

I am trying to pull data in from multiple databases, and I have the server name, database name, and schema name available.  Can this be done?

[Update 2016-06-10 1158 EST]

I changed the SET to a LET and it worked!

LET vSQL = '

select distinct

   ''DP_$(vDataSource_DIR)'' [SourceSystem]

  , ''POSCd'' [CodeType]

  , LTRIM(RTRIM([POSCd])) [CodeName]

  , LTRIM(RTRIM([PlcOfSrvcDesc])) [CodeDesc]

from $(vDataSource_ServerName).$(vDataSource_DatabaseName).$(vDataSource_SchemaName).PlcOfSrvcCdMst [poscm] with (nolock)

order by LTRIM(RTRIM([POSCd]))

';

Message was edited by: Wesley Kalata

Community Browser