Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Storing column value into amper var
Go
New
Search
Notify
Tools
Reply
  
Storing column value into amper var
 Login/Join
 
Member
posted
I am looking to read a value from a table and plunk it into an amper variable for use later in a series of calculations. I can do the hold into an external file with one summed value (the data I am working with is numeric) and I can READ this value into my amper var. It reads in as text, even when I give it a specific format, say D20. How can I ensure that it gets converted toa number?

here's some sample code:
TABLE FILE PARHOLD
SUM
IssuerPar AS 'TotalPar'
ON TABLE HOLD AS SUMPAR
END

-SET &PARPATH = &APPROOT || \CDOREPORTS\TOTPAR.FTM;
FILEDEF TOTPAR DISK &PARPATH
TABLE FILE SUMPAR
PRINT *
ON TABLE HOLD FORMAT ALPHA AS TOTPAR
END
-RUN

-SET &TotPortfolio = 0;
-READ TOTPAR, &TotPortfolio.D20.

-* This reveals what looks like a number
-TYPE &TotPortfolio

-* I try a simple calculation
-SET &X = &TotPortfolio + 2;
-TYPE &X
-* I get a string of *'s indicating a type mismatch.

I've tried EDITing the returned string and using the ATODBL function, but no luck.

As a recent convert form the MS SQL world, I find it awfully frustrating to do what used to be simple things (cache a column value into a local var)

Any tips?

Thanks!
 
Posts: 21 | Location: Springfield, MA | Registered: August 17, 2004Reply With QuoteReport This Post
Master
posted Hide Post
I was able to get something like this working. One thing that look wrong is the comma in the -READ line. Here is my code

-SET &ECHO = ALL;
TABLE FILE CAR
SUM DCOST
ON TABLE HOLD FORMAT ALPHA
END
-RUN
? HOLD
-RUN
-SET &DCOST = 0000000;
-READ HOLD &DCOST.D7.
-SET &DCOST = &DCOST + 2;
-TYPE &DCOST

Hope This helps
 
Posts: 863 | Registered: May 24, 2004Reply With QuoteReport This Post
Member
posted Hide Post
Thanks.

I'm dealing with some fairly large numbers (SQL bigint) and I think I'm running up against some kind of overflow situation.

I can display the type as A, but with all of the characters being numeric. I thought WF would interpret this 'string' as a number in any calculation:

-SET &A = '1234567890123';
-SET &B = 2; -* NOTE numeric here
-SET &C = 0; -* get WF thinking that we are going to be dealing w/numbers
-SET &C = &A * &B;
-TYPE C is now &C

My &C var will display as **********.

These numbers could arrive in at any magnitude so I don't want to hard code any length. Since my underlying data type is D20.2 and the 'numbers' come in with padded spaces in front (' 5210594079.43'), should I be concerned with stripping them away?

I'm a little stumped (doesn't take much these days)

Grateful for any clues.
 
Posts: 21 | Location: Springfield, MA | Registered: August 17, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
but &A is clearly Alpha, and you've set it in quotes. so you can't multiply Alphas, right?
-SET &A = '12345';
-TYPE &A.TYPE and you'll want N but get A
-SET &B = 12345 ;
-TYPE &B.TYPE
will give you the N you desire.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
<Pietro De Santis>
posted
You may want to do your numeric manipulation in FOCUS instead of Dialog Manager variables. Just HOLD the information, then run DEFINEs or COMPUTEs to your numeric fields. At the end, create an ALPHA HOLD file and -READ into alpha variables that I imagine you want to embed in an HTML page.
 
Reply With QuoteReport This Post
Member
posted Hide Post
I think we're getting close. StingRay's solution worked well for smaller numbers. I'm working with numbers in the 10's of billions range, which now (evidently) forces me to work with packed decimals.

Because I HOLD the value off as ALPHA, it gets read into an amper var as an ALPHA. I have to initialize that amper var with enough characters (0's in this case) to hold all of the digits in that number.

Is it safe to say that Dialog Manager can handle expressions involving amper vars when assigning a value to another amper var, but only up to a certain magnitude?

To recap, all I want to do is:

1) Sum a bunch of numbers from one result set
2) Store that sum in a local amper var
3) Be able to use that amper var as a denominator in a series of further ratio-style calculations

This is extremely easy to do in SQL, but I don't want to rely on stored procs. I need the flexibility to modify my data gathering as the business sees fit.

Pietro, could you (or someone else) provide an example of what you suggested? I'm still rather new to WF and in dire need of understanding its subtleties.

Thanks!
 
Posts: 21 | Location: Springfield, MA | Registered: August 17, 2004Reply With QuoteReport This Post
Guru
posted Hide Post
If you run the following code:
-SET &ECHO = ALL;
-SET &DCOST = 00000000000000000000;
TABLE FILE CAR
SUM
COMPUTE BIG_DCOST/P20 = DCOST * 100000000000000;
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE FORMAT ALPHA
END
-RUN
-READ SAVE &BIGDCOST.20.
-TYPE &|BIGDCOST IS &BIGDCOST
-TYPE TYPE OF &|BIGDCOST IS &BIGDCOST.TYPE

The result is
TYPE OF &BIGDCOST IS A

ie.
.TYPE tests for the type of a value. The letter N (numeric) is passed to the expression if
the value can be interpreted as a number up to 10e9�1 and stored in four bytes as a
floating point format(F). In Dialogue Manager, the result of an arithmetic operation with
numeric fields is truncated to an integer after the whole result of an expression is
calculated. If the value could not be interpreted as numeric, the letter A (alphanumeric)
is passed to the expression.

I think you will have to work with these numbers within a FOCEXEC(like I did with the COMPUTE) to utilize the large number formats.

If you can give us more info on how you get the
numerator, we can probably send you in the right
direction on how to do the calculations.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Reply With QuoteReport This Post
Master
posted Hide Post
Another way is to do the calculation in a define file like this:

DEFINE FILE PARHOLD
TOTALPAR/D20 = IssuerPar + 2 * 100;
END

TABLE FILE PARHOLD
SUM
TOTALPAR
ON TABLE HOLD AS SUMPAR
END

Then you can use dialoge manager to read the hold file. Hope this helps.
 
Posts: 863 | Registered: May 24, 2004Reply With QuoteReport This Post
Guru
posted Hide Post
Hi Bill,
you can also bring your amper variable into the code in a define like this;

TABLE FILE PMDASTN
SUM BOOK
IF READLIMIT EQ 500
ON TABLE SAVE
END
-RUN
-READ SAVE &BOOK.P14.
-TYPE BOOK IS &BOOK

DEFINE FILE PMDASTN
TOTBOOK/P14.2 = &BOOK;
END

TABLE FILE PMDASTN
PRINT PORTFOLIO_A CUSIP_9 TOTBOOK
IF READLIMIT EQ 100
END

Carol
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Storing column value into amper var

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.