Good day,
I'm creating a multi source script that extracts the same query from multiple sources.
Due to the nature of the database structure, it requires dynamic queries.
This works ok, unless i want to add non-numerical values to filters.
In SQL these have to be surrounded by '
But as this is a control symol in QV, this breaks the script.
Does anybodt know a resolution for this problem?
Tnx, karel
Example:
$(vOutput):
LOAD SRC_Database, SRC_Company INLINE
[SRC_Database, SRC_Company
];
Let vDatabaseMainCount = NoOfRows('DatabaseMain');
For k = 0 To $(vDatabaseMainCount)-1
Let db = Peek('Database',$(k),'DatabaseMain');
Let sql = Peek('SQL',$(k),'DatabaseMain');
Let arc = Peek('SRC_Database',$(k),'DatabaseMain');
;
OLEDB CONNECT TO
[Provider=SQLOLEDB.1;Integrated Security=SSPI;Connect Timeout=2;'Persist Security Info=False;Initial catalog=
$(db)
;Data Source=
$(sql)
;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=S-BE-KI-XTS16;
Use Encryption for Data=False;Tag with column collation when possible=False]
;
Company:
SQL SELECT Name FROM $(db).dbo.Company;
if ScriptError = 0 then
Let vCompanyCount = NoOfRows('Company');
For i = 0 To $(vCompanyCount)-1
LET vMyCompany = Peek('Name',$(i),'Company');
LET vCompany = '$(vMyCompany)$';
// The Query
// example on company tables: select * from $(db).dbo.[$(Company)power customer]
// example on database tables: select * from $(db).dbo.[power logging]
LET vQuery =
'
select * from $(db).dbo.[$(vCompany)power customer]
where DT_Changed > '1/1/2015'
'
;
One:
LOAD '$(arc)' as SRC_Database,'$(vMyCompany)' as SRC_Company,*;
SQL $(vQuery);
;
Concatenate ($(vOutput)) LOAD *, '1' as ID resident One;
drop table One;
Next i;
drop table Company;
else
endif
next k;
