Possibilty of Dynamic SQL execution in BTEQ

Database

Possibilty of Dynamic SQL execution in BTEQ

Hi All

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,

sagar

3 REPLIES
Enthusiast

Re: Possibilty of Dynamic SQL execution in BTEQ

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 :

BTEQ EXPORT

Select *title('') from B;

END BTEQ

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

Select

$ColumnList

from A;

Please try this , this should work. If my understanding is wrong do let me know.

Cheers,

Mani

Re: Possibilty of Dynamic SQL execution in BTEQ

Hi Mani

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.

Thanks

Sagar

Enthusiast

Re: Possibilty of Dynamic SQL execution in BTEQ

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 ",".

Cheers,

Mani