Dynamic SQL Generation to Load Data from Multiple Sources

Fri, 10 Jun 2016 15:16:45 GMT

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

topic Dynamic SQL Generation to Load Data from Multiple Sources in QlikView

Dynamic SQL Generation to Load Data from Multiple Sources