Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am trying to extract data using the following code:
NoConcatenate
ListofTable:
SELECT table_name As TableName,
table_schema,
table_type
FROM information_schema.tables
where table_schema='edb'
and table_name in
(
'application_history'
);
for i=0 to NoOfRows ('ListofTable')-1
let vTableName=Peek('tablename',$(i),'ListofTable');
//let vTableName1="&vTableName&";
$(vTableName):
SQL SELECT * from
"edb".$(vTableName) ;
Store $(vTableName) Into [lib://Extraction/$(vTableName).qvd] (qvd);
Drop Table $(vTableName);
next
On selecting one table manually it comes in format below.
[applications_old]:
SELECT id,
`user_id`,
remarks,
`is_active`,
`created_on`,
`updated_on`,
`created_by`,
`updated_by`
FROM edb.`applications_old`;
While using above loop to extract all the tables I am getting an error of unknown statement at SQL Select part. Please let me know how to resolve this.
Why not adapting this wizard-syntax to your loop, like:
[$(vTableName)]:
SQL SELECT * from
edb.`$(vTableName)` ;
Store [$(vTableName)] Into [lib://Extraction/$(vTableName).qvd] (qvd);
Drop Table [$(vTableName)];
- Marcus
Hi, I tried but after using [] and `` this, it shows wrong table name.
Does a manually load with the wizard-syntax of wrapping (certain) table- and field-names? If yes, the wizard logic/communication between the Qlik and the driver and the database is generally working. If not you need to find the needed syntax. For this take a look within the documentation of the driver and database and/or you makes just a bit trial & error with various quotes for the wrapping but maybe just starting with a table and a single field which didn't contain any space or special char.
Beside this just apply a few trace-statements within the loop which return the loop-counter and the table-names and similar information to see their real values and where the load breaks - usually it will give valuable hints why it happens.
- Marcus