Focal Point
[SOLVED] Variable in column name

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/780104944

May 24, 2010, 05:17 AM
RAVNEET
[SOLVED] Variable in column name
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,


WebFOCUS 7.6.1
Windows
All Outputs
May 24, 2010, 10:15 AM
GinnyJakes
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



Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
May 24, 2010, 05:43 PM
Waz
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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

May 28, 2010, 05:03 AM
RAVNEET
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 ?


WebFOCUS 7.6.1
Windows
All Outputs
May 28, 2010, 10:58 AM
GinnyJakes
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
May 28, 2010, 11:31 AM
Ram Prasad E
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



WebFOCUS 8.1.05
Windows
http://ibiwebfocus.wordpress.com
https://www.facebook.com/groups/ibi.webfocus/
May 30, 2010, 07:04 PM
Waz
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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

May 31, 2010, 01:13 AM
Ram Prasad E
quote:
I would also suggest not using HOLD, but use ON TABLE HOLD AS {name}.

Yes, I agree with you Waz.


WebFOCUS 8.1.05
Windows
http://ibiwebfocus.wordpress.com
https://www.facebook.com/groups/ibi.webfocus/
May 31, 2010, 07:28 AM
RAVNEET
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,


WebFOCUS 7.6.1
Windows
All Outputs
May 31, 2010, 05:35 PM
Waz
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.



Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

June 01, 2010, 07:00 AM
RAVNEET
Thanks Waz..
This is working fine now.

Regards,
Ravneet


WebFOCUS 7.6.1
Windows
All Outputs