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] Help creating a variable from a field in a table

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Help creating a variable from a field in a table
 Login/Join
 
Cody
Silver Member
posted
I am having some issues getting a field out of a table and into a variable.

I have the below:
  
TABLE FILE COUNTRY
SUM
	DST.COUNTRY_NM
	COMPUTE ROWID/I4=LAST ROWID+1;
BY COUNTRY_NM NOPRINT
ON TABLE HOLD AS CNTRY1
END
TABLE FILE CNTRY1
PRINT
	COUNTRY_NM AS 'CNTRY'
WHERE ROWID EQ '2';
ON TABLE HOLD AS CNTRY FORMAT ALPHA
END
-RUN
-READ CNTRY = &CNTRY.A128
-TYPE &CNTRY


This code should return Germany, but for some reason it is returning 000007Germany.

In the master file, COUNTRY_NM is:
 FIELDNAME=COUNTRY_NM, ALIAS=COUNTRY_NM, USAGE=A128V, ACTUAL=A128V, $


Thanks for the help

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


WebFOCUS 7.6.8
Linux
HTML,AHTML,PDF
 
Posts: 37 | Registered: June 12, 2009Report This Post
Ram Prasad E
Master
posted Hide Post
In this 000007Germany, 7 is the number of characters in Germany.

Try this


TABLE FILE CNTRY1
BY COUNTRY_NM
WHERE ROWID EQ '2';
ON TABLE HOLD
END
-RUN
-*?FF HOLD
-READ HOLD &CNTRY.A128


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
Ram Prasad E
Master
posted Hide Post
Why one more hold file just to add a filter on calculated field. Make use of WHERE TOTAL phrase
TABLE FILE COUNTRY
SUM
COMPUTE ROWID/I4=LAST ROWID+1;
BY COUNTRY_NM
WHERE TOTAL ROWID EQ 2;
ON TABLE HOLD
END
-RUN
-READ HOLD &CNTRY.A128
-TYPE &CNTRY

Don't have access to reporting server to test this code.
But this should help you to get an idea. Hope this helps.

This message has been edited. Last edited by: Ram Prasad E,


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
Cody
Silver Member
posted Hide Post
Thanks. That seemed to work until I tried using that variable later on. I am eventually using that variable in a WHERE in another table file. For some reason, when it gets there, there is some extra characters in it, it is coming out as '? USA' instead of 'USA'. The ? is some random non printable character


WebFOCUS 7.6.8
Linux
HTML,AHTML,PDF
 
Posts: 37 | Registered: June 12, 2009Report This Post
njsden
Virtuoso
posted Hide Post
quote:

FIELDNAME=COUNTRY_NM, ALIAS=COUNTRY_NM, USAGE=A128V, ACTUAL=A128V, $

As Ram stated, the 000007 is the length of the value you obtained (7 for GERMANY).

That is the normal behaviour when saving variable-length alpha fields (AnV) to an ALPHA HOLD file.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
njsden
Virtuoso
posted Hide Post
Cody, "ON TABLE HOLD" is creating a file in BINARY format so the ? you are getting is due to a number in there (000003 for USA) that is rendered as non-printable due to its internal representation.

Can you try some different approach to see if it works? I'll leave the ROWID manipulation for you to play with for the sake of simplicity!

TABLE FILE COUNTRY
SUM
COMPUTE DST_COUNTRY_NM/A128 = DST.COUNTRY_NM;
BY COUNTRY_NM NOPRINT
BY TOTAL DST_COUNTRY_NM
ON TABLE HOLD AS CNTRY1 FORMAT ALPHA
END
-RUN
-READ CNTRY1 &CNTRY.A128.
-TYPE &CNTRY


- Neftali.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
njsden
Virtuoso
posted Hide Post
Cody, please make sure that HOLDLIST is set to PRINTONLY either before your request:

SET HOLDLIST = PRINTONLY

TABLE FILE ...


or as part of it:

TABLE FILE ...
.
.
ON TABLE HOLD ... FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY <-- Here!
END


This is to guarantee that only DST_COUNTRY_NM finds its way into the HOLD file.

- Neftali.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Cody
Silver Member
posted Hide Post
Well, I am getting closer. It all seems right now except there seems to be a space after the country name when I try to use it in the next table where statement.

It certainly is not the ideal solution, but in my where clause I now have WHERE COUNTRY_NM||' ' EQ '&CNTRY'; and it seems to be working

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


WebFOCUS 7.6.8
Linux
HTML,AHTML,PDF
 
Posts: 37 | Registered: June 12, 2009Report This Post
Ram Prasad E
Master
posted Hide Post
When using -READ, its always better to use TRUNCATE function following to remove the trailing blanks.

quote:
WHERE COUNTRY_NM||' ' EQ '&CNTRY';


When concatenating with trailing or leading space, avoid strong concatenation operator ||.

I will prefer | in case to append extra space.


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
Jim Morrow
Platinum Member
posted Hide Post
The garbage is the result of reading more charater then the table is writing. I always add lots of blanks to pad the end of a TABLE request so I don't have to size my read perfectly to the length of the TABLE output. I would code this TABLE something like this:



TABLE FILE CNTRY1
PRINT
COUNTRY_NM AS 'CNTRY'

COMPUTE PAD/A256 = ' ';

WHERE ROWID EQ '2';
ON TABLE HOLD AS CNTRY FORMAT ALPHA
END


Jim Morrow
Web Focus 7.6.10 under Windows 2003
MVS 7.3.3



 
Posts: 129 | Registered: June 01, 2005Report 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] Help creating a variable from a field in a table

Copyright © 1996-2020 Information Builders