topic Re: QVD Creation Script using Oracle Database in QlikView

QVD Creation Script using Oracle Database

Thu, 03 Jul 2014 13:05:43 GMT

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

Re: QVD Creation Script using Oracle Database

PradeepReddy — Thu, 03 Jul 2014 13:15:45 GMT

What is the error message you are getting exactly?

Re: QVD Creation Script using Oracle Database

jonathandienst — Thu, 03 Jul 2014 13:43:00 GMT

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

Re: QVD Creation Script using Oracle Database

Thu, 03 Jul 2014 14:11:34 GMT

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.