Hi,
Please have a look in the below code

SQL SQLORA PREPARE SQLOUT FOR
SELECT TOTAL_TRADE_VOLUME FROM IMA_REPORT_PAGE
END

TABLE FILE SQLOUT
PRINT TOTAL_TRADE_VOLUME/I10C AS 'Total,Transactions, ,,GBP'

Here I have hard coded the value GBP.
I want to retrieve the currency (GBP) from table and dynamically use over here. How can i do that?

Regards,
Ravneet

This message has been edited. Last edited by: Kerry,

Is the field in the same table? What is the length of GBP?

SQL SQLORA PREPARE SQLOUT FOR
SELECT GBP, TOTAL_TRADE_VOLUME FROM IMA_REPORT_PAGE;
TABLE 
ON TABLE HOLD FORMAT ALPHA
END
-RUN
-READ HOLD &GBP.An
TABLE FILE HOLD
PRINT TOTAL_TRADE_VOLUME/I10C AS 'Total,Transactions, ,,&GBP'
END

Perhaps the topic should be callled Variable in column Title

As Ginny says, Table the file with the currency, and hold it, then -READ it.

If it will be changing during the data, then there are other options.

A compound report for each currency.

or

Put the titles in a heading and use the column with the currency, and page break on it.

Hi Ginny/Waz,
Thanks for your response.
The field is from same table IMA_REPORT_PAGE and have length =3.

I tried by hold and read.
SQL SQLORA PREPARE SQLOUT FOR
SELECT TOTAL_TRADE_VOLUME, BASE_CURRENCY
FROM IMA_REPORT_PAGE;
TABLE
ON TABLE HOLD FORMAT ALPHA
END
-RUN
-READ HOLD &BASE_CURRENCY.A3
TABLE FILE HOLD
PRINT TOTAL_TRADE_VOLUME/I10C AS 'Total,Transactions, ,,&BASE_CURRENCY'
---
END

I got retreival killed error.

Please suggest ?

Well, the first thing that I see that is wrong is that on your -READ you didn't allow for the character positions occupied by TOTAL_TRADE_VOLUME which would be first. So either add a dummy variable with the appropriate length or select the BASE_CURRENCY first in the SQL. You can do a ? HOLD HOLD after the END statement to see the formats of the extracted columns.

After the -READ, do a -TYPE of the variable and don't go any farther in the code until you see what you are expecting in &BASE_CURRENCY.

Instead of "SQL SQLORA PREPARE SQLOUT FOR" use "SQL SQLORA".
Since both PREPARE and TABLE commands were used together, you got "RETRIEVAL KILLED" error.

As suggested by Ginny, use -TYPE to check if the amper variable got right value in it.

SQL SQLORA
SELECT BASE_CURRENCY,TOTAL_TRADE_VOLUME
FROM IMA_REPORT_PAGE;
TABLE
ON TABLE HOLD FORMAT ALPHA
END
-RUN
?FF HOLD
-READ HOLD &BASE_CURRENCY.A3.
-TYPE &BASE_CURRENCY

TABLE FILE HOLD
PRINT 
TOTAL_TRADE_VOLUME/I10C AS 'Total,Transactions, ,,&BASE_CURRENCY'
END

Ravneet, When you select Column1, Column2 and ON TABLE HOLD it, Column1 is first in the file and Column2 is second.

If Column 1 is 5 Characters and Column 2 is 3, use -READ HOLD &Col1.A5. &Col2.A3.

I would also suggest not using HOLD, but use ON TABLE HOLD AS {name}.

When you use HOLD, there is a chance that a previous process could have created a HOLD file, and if your SQL does not get any records, you -READ will get the previous HOLD's data.

quote:

I would also suggest not using HOLD, but use ON TABLE HOLD AS {name}.

Yes, I agree with you Waz.

Thaks Waz/Ram/Ginny,
I have coded as suggested by you with Hold as ALPHA format.

SQL SQLORA
SELECT BASE_CURRENCY,TOTAL_TRADE_VOLUME
FROM IMA_REPORT_PAGE;
TABLE
ON TABLE HOLD AS TEST_HLD FORMAT ALPHA
END
-RUN
-READ TEST_HLD &BASE_CURRENCY.A3.
TABLE FILE TEST_HLD
PRINT COUNTER_PARTY_FULL_NAME AS 'Counterparty,,,,'
TOTAL_TRADE_VOLUME/I10C AS 'Total,Transactions, ,,&BASE_CURRENCY'
---
END

I am getting the variable value in the alias name but the value is incorrect. I am getting '000' instead of 'GBP' over here. If i change -READ TEST_HLD &BASE_CURRENCY.A3. to -READ TEST_HLD &BASE_CURRENCY.A16. then i am getting 000003GBP000013L instead of GBP.

Could you please let me know if i am missing anything?
Is there any issue with the hold format ?

I have tried by removing FORMAT ALPHA as well
ON TABLE HOLD AS TEST_HLD FORMAT ALPHA to ON TABLE HOLD AS TEST_HLD with -READ TEST_HLD &BASE_CURRENCY.A6. then i am getting the required output, but in this case it gives pdf error in compund reporting.

Regards,
Ravneet

This message has been edited. Last edited by: RAVNEET,

Ravneet, looks like your fields from Oracle are VARCHAR. The first 6 chars are the length og the field.

To get the currency add in a dummy variable to take the length.

-READ TEST_HLD &DUMMY.A6. &BASE_CURRENCY.A3.

Thanks Waz..
This is working fine now.

Regards,
Ravneet