Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Variable in column name

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Variable in column name
 Login/Join
 
RAVNEET
Member
posted
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
 
Posts: 11 | Registered: November 25, 2009Report This Post
GinnyJakes
Expert
posted Hide Post
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
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Waz
Expert
posted Hide Post
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!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
RAVNEET
Member
posted Hide Post
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
 
Posts: 11 | Registered: November 25, 2009Report This Post
GinnyJakes
Expert
posted Hide Post
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
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Ram Prasad E
Master
posted Hide Post
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/
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Report This Post
Waz
Expert
posted Hide Post
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!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Ram Prasad E
Master
posted Hide Post
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/
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Report This Post
RAVNEET
Member
posted Hide Post
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
 
Posts: 11 | Registered: November 25, 2009Report This Post
Waz
Expert
posted Hide Post
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!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
RAVNEET
Member
posted Hide Post
Thanks Waz..
This is working fine now.

Regards,
Ravneet


WebFOCUS 7.6.1
Windows
All Outputs
 
Posts: 11 | Registered: November 25, 2009Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Variable in column name

Copyright © 1996-2020 Information Builders