Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

QVD Creation Script using Oracle Database

Hi Everyone,

Need Help..

I am having issue while creating qvd using oracle database.

I want to peek the column names from the inline table.

The code is as below:


List of tables which is cread in the previous tab:


Table_Names:

LOAD * inline [
TableNames

SCHEMA.TABLE1

SCHEMA.TABLE2

] ;

QVD CREATION SCRIPT:


LET rowTP = NoOfRows('Table_Names');

for i=1 to $(rowTP)


Let vTableName    = FieldValue('TableNames',i);
ColumnNames:
  LOAD
  * ;
  
  //select column_name from ALL_TAB_COLUMNS where TABLE_NAME ='$(vTableName)';
  select * from ALL_TAB_COLUMNS where TABLE_NAME ='$(vTableName)';
 
  LET vFields   =   peek('column_name');
  DROP Table ColumnNames;

$(vTableName):
LOAD
  *
  ;
  SELECT
  $(vFields)
  FROM
  $(vTableName)  $(vWhereCondition)
  ;
STORE $(vTableName) INTO $(QVDFileDir)$(vTableName).QVD;


DROP TABLE $(vTableName);

next


//Drop the inline Table
Drop table Table_Names;


Thanks,

Sonali

3 Replies
PradeepReddy
Specialist II
Specialist II

What is the error message you are getting exactly?

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

You need to concatenate the field names. I have added [ ] to handle possible spaces in the names:

...

  ColumnNames:

  SQL Select column_name from ALL_TAB_COLUMNS where TABLE_NAME ='$(vTableName)';

  ConcColumns:

  LOAD '[' & Concat(column_name, '],[') & ']' As ConcatColumns

  Resident ColumnNames;

  let vFields = Peek('ConcatColumns');

  Drop Table ConcColumns;

  $(vTableName):

  SQL SELECT

    $(vFields)

  FROM $(vTableName)  $(vWhereCondition);

  ....

  Drop Table ColumnNames;

Next

Note that you must drop the ColumnNames table so that you don't land up with TABLE1 and TABLE2 fields in the second pass of the loop.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hi Jon,

Thanks for reply,

Actuallyin my tables there is no field called Column_Name.

That's why I am getting error and instead of

       select column_name from ALL_TAB_COLUMNS where TABLE_NAME ='$(vTableName)';

I have used
     
       select * from ALL_TAB_COLUMNS where TABLE_NAME ='$(vTableName)';

But I need to have column names(or you can say Field names) of the tables that can be used further.