Hi All, I am having an issue with extracting a field to be a variable and wanted to see if you guys could help as I am sure others have had this issue.

 TABLE FILE US_CONV_RATES
SUM
	 FST.CLS_PRICE AS 'CUR_CAD'
WHERE FROM_COUNTRY EQ 'CAD';
ON TABLE HOLD AS CUR_CAD FORMAT ALPHA
END

TABLE FILE CUR_CAD
SUM
	 CUR_CAD
ON TABLE SAVE AS CUR_CAD
END
-RUN
-READ CUR_CAD &CUR_CAD.P18.6

-TYPE &CUR_CAD 

I am trying to extract the USD to CAD conversion rate from this table which stores that information in a P18.6 format. Unfortunately I am getting an error that it is an invalid format for a read function. Because this is a currency conversion I need to make sure I pick up the decimal points. I did also try changing the code -READ CUR_CAD &CUR_CAD.P18.6 to -READ CUR_CAD &CUR_CAD.A24 but that was unsuccessful as well and I got an error that a value was missing for &CUR_CAD.

I also tried converting CLS_PRICE to be a A24 instead of a P18.6 by multiplying by 1,000,000 which allowed me to successfully extract the value to a variable. Unfortunately once I had the value as a variable I wasn't able to convert it back to having the decimal place as any calculations would result in the rounded number without the 6 decimal positions.

Thanks in advance for all of your help, I want to make sure I handle this in an efficient and practical matter.

Thanks,

-Luke

This message has been edited. Last edited by: Luke Forster,

Try this

TABLE FILE US_CONV_RATES
SUM FST.CLS_PRICE AS 'CUR_CAD'
WHERE FROM_COUNTRY EQ 'CAD';
ON TABLE HOLD AS CURCAD FORMAT BINARY
END
-RUN
-READFILE CURCAD
-RUN
-TYPE CUR_CAD VALUE IS : &CUR_CAD

Martin,

It worked perfectly, thank you for the quick response.

I do have a question about -READFILE though, are there any downsides to extracting the value to a variable the way you showed in your code? This seems like a much easier way to accomplish bringing a value from a table to be a variable but I haven't ever seen it done that way before. Not sure if this is possibly a newer feature or something like that but before I adjust to always doing it this way by default I wanted some additional info.

Thanks,

-Luke

There are two issues, I've found with -READFILE.

1. The variable may get prompted for, if there isn't a -DEFAULTH or -SET of each column in the HOLD file.
2. If the -READ is in a look, it will produce a WARNING, that may cause report caster to fail (If its a scheduled job).

quote:

1. The variable may get prompted for, if there isn't a -DEFAULTH or -SET of each column in the HOLD file.
2. If the -READ is in a look, it will produce a WARNING, that may cause report caster to fail (If its a scheduled job).

Right Waz, must DEFAULTH all variables to avoid auto prompting. Ideally, -SET them to a default value such as '', this way no prompting and no warning.

Luke, the -READFILE appears somewhere in WF77x and you may be able to find documentation about it in Tech Support.

What you will find is that a -READFILE will warn you that you are overwriting a variable, it is already exists.

A way around that is to issue a -SET &var =; before it, to clear the variable.

Luke,

Whilst Waz and Martin have, IMHO, given you the better method, I think that you need to know why your -READ didn't work.

There are two reasons really. The first is that when using -READ, only integer and alpha formats are acceptable. So your attempted use of a packed decimal was doomed to fail.

The second reason is likely to be the fact that, as a default, dialogue manager numeric variables will have no decimal places. You can change this behaviour by using SET DMPRECISION=n.

The location in the tech doc is here, just click on the "Using an Amper Variable in an Expression" link.

This may be of use to you even when using -READFILE

Good luck

T

If I may add my two cents concerning DM variables and -READ:
What Tony says about decimals is true when you use DM variables in calculations. However, when reading from an ASCII file you will get all the characters, decimal point and decimals included.
Try the following:

  
DEFINE FILE CAR
RC/P12.2=RETAIL_COST/100;
END
TABLE FILE CAR
PRINT MAX.RC
ON TABLE SAVE
END
-RUN
-READ SAVE,&RC
-RUN
-TYPE &RC

You should get 88.78

Danny,

Oh so true, but if you add the following code to the end of your example, without SET DMPRECISION = 2, you get 88!

-SET &RC = &RC * 1
-TYPE &RC

T