We have a requirement where columns of table A are stored in Lookup table B.
I need to prepare a query such that it picks up the column-names of table A present in table B and gets the values stored in columns of table A.
This should be prepared with the help of BTEQ.
Could someone suggest on this.
If its possible through BTEQ,is it possible through any other way?
Thanks & Regards,
If I understand your question correctly, you have the list of columns stored in Table B as rows i.e Select * from B would result me in the column list, whose values need to found from Table A . Your query should be something like
SELECT (Column_list in B) from A.
I have a suggestion here
1) export the column list using BTEQ export, into a file.
Pseudo code :
Select *title('') from B;
Note: Do not load the titles in the file.
2) Read the file and store the values into a variable.
ColumnList= `cat FileColumnListfromB.txt`
3) U can start a new session of BTEQ. Here you can call that variable
Please try this , this should work. If my understanding is wrong do let me know.
Thanks alot for the reply.Your understanding is totally correct.
I will give it a try and let you know on this.
However i have missed to mention one part,table B contains column-names of table A not only in a single row.
Table B contains multiple rows and each row returns different set of table A column-names.
In the BTEQ,Depending upon lookup key between table A and table B, we need to pickup the column-names of table A from table B row.
While doing the import, u will have to join on the key column and store the result in the file.
For every line in the file do Step 3(a while loop). Is the column list in table separated by comma? If not u might have to do something to add commas perhaps something like a sed command to replace the separator with ",".