Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What is the error message you are getting exactly?
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
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.