Hi All, it seems to be my week
from **** . One person leaves
and evrything
goes to TISH...My problem is,
our report using a decode to lookup
values in a file, which works fine.
Problem is that it then does a
recompute and the column for reason
display the last value it retrieved
rather than displaying a sum.
Is this the normal behaviour
for a Decode or I am doing
something wrong or is it a bug?
if you look below you will see
34,199 twice, the second one
is actually the total line.

Actual from 
Previous Day"$1,750,622
$7,063,314<br />$85
$2,977,643
$172,715<br />$120,214
$34,199
>$34,199

This message has been edited. Last edited by: <Mabel>,

Lloyd,

Are you decoding into an alphanumeric field? If so then the behaviour is as documented - i.e. summing an alphanumeric field will return the last value encountered.

Tony, thanks I didn't realize nor
can I find it in the help.....
But, when I try to do it as a
FOCUS file I still get errors.
Here sample code like what I did
and the error it gave me....

APP MAP FOCHOLD C:\Inetpub\wwwroot\Output_Hold\decoding
<br />APP HOLD FOCHOLD<br />
FILEDEF DTEST DISK  C:\Inetpub\wwwroot\Output_Hold\decoding\
DTEST.FOC<br />TABLE FILE CAR 
<br />SUM DEALER_COST<br />
BY CAR <br />ON TABLE HOLD AS 
DTEST FORMAT FOCUS INDEX CAR
<br />END<
ABLE FILE DTEST					<br />PRINT <br />CAR
COMPUTE PDAY/D12CN=DECODE CAR
(DTEST ELSE 0); AS 'Actual from,
Previous Day'<br />END
 0 NUMBER OF RECORDS IN TABLE=
18  LINES= 10
(FOC441) WARNING. THE FILE EXISTS 
ALREADY. CREATE WILL WRITE OVER IT
0 ERROR AT OR NEAR LINE 
20  IN PROCEDURE MEMFEX  FOCEXEC *
(FOC272) FORMAT ERROR IN DECODE 
OR FILE ELEMENT: ROMEO
(FOC009) INCOMPLETE REQUEST STATEMENT

Now the Feild is alpha
with a space, how do I ovwercome this problem?

This message has been edited. Last edited by: <Mabel>,

I believe your problems stems from the fact that the car model has the blank in the name without quotes around the value of the car model name. So you may have to play with the data replacing the blank in the model name with some character prior to creating the focus file as the decode, of course you could create a flat file for the decode and write out quote marks I suppose.

I believe you can use OVERLAY to replace the blank with a non blank character.

I pulled this from a fex I have, may be the hard way, but:

I_IDXA/I2 = IF AA003 CONTAINS '`' THEN
POSIT(AA003,32,'`',1,I_IDXA) ELSE 0;
AA003XYZ/A32 = IF I_IDXA EQ 0 THEN AA003 ELSE
OVRLAY(AA003,32,' ',1,I_IDXA,AA003XYZ);

The above is a routine for some name manipulation we do.

Then use the new field as your BY field, then create the same situation to do your decode to find the value.

Hope this makes sense.

Happy Fourth of July to all of you who celebrate same.

Leah

Yeah, that was my thoughts. I just hoped there would be a simpler way. Fixing the name is gonna take a some reworking of the actual report. Because the person that created the report, also created the file without a ID for Division (which is what it is in my actual report) and instead opted to the name which also gets printed on the report.

My pProblem is this report has been production for months and as soon he leaves, everyone dec1des the report is wrong. And to top it off, all of the top execs use this one. So now my weekend is gonna suck.....

Decode (filename ...) requires a flat file; you cannot use a Focus file there.

Instead, arrange to hold yesterday's data in Focus format (with distinct fieldnames) so that you can join today's to it. Then the join key values can have blanks, no problem; and yesterday's stats are just another numeric field.

Alternatively, if you need to stick with decode: Once upon a time there was a feature, that if the filename in he DECODE is "HOLD", the FIELD declarations in the HOLD MFD (rather than delimiters in the rows of the datafile) would determine the length of the key field. May still work; may even be documented.

OK. ...so to clarify all this because I'm thoroughly confused.

Decode will only work with flat files, not Focus files. Does that then mean that you should not retreive numeric values using decode? Because when I do, I go back to my original problem of the sum not recomputing, and instead retaining the last value. ( I even tried using NOPRINT and assigning the decoded value to another feild, which also didn't work)

Are these assumptions correct Or am I still missing something?

My problem is the manuals/Help, I find them less than accurate and definitely not indepth.

Confusion ranges. I guess the question we never asked was is the first file created in the same run of the report or a previous run, if the same run, then holding a file and joining to it might be best.
hypothetical example
TABLE FILE XYZ
SUM COST AS PREV_COST
BY MODEL AS MODELP
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS HOLD1
END
JOIN MODEL IN ABC TO MODELP IN XYZ AS J1
TABLE FILE ABC
PRINT MODEL MODELP PREV_COST ...
END

Yeah...that's what I'm doing now. I was originally just trying to repair someone else's problem, NOW I'm rewriting 90% of the report becuase with all intermediate and temp files he uses, it's kinda messy.

all you have to do is surround your decode value with quotes when you produce the decode file, so that the decode files looks like this
'alfa romeo' 12.2
'mg rover' 4.5
'triumph' 5.4
thats not such a big deal, and a good default practice in any text-to-value decode file to avoid something like this happening in the future.
plus you should always force a blank space in between the 2 values;
DEFINE FILE CAR
MODEL2/A26 = '''' | MODEL ||'''';
END
TABLE FILE CAR
SUM COMPUTE BLANK/A1=' '; COST
BY MODEL
ON TABLE SAVE AS whatever
END

There was a minor (or should I say perhaps major) error in my reply:

Confusion ranges. I guess the question we never asked was is the first file created in the same run of the report or a previous run, if the same run, then holding a file and joining to it might be best.
hypothetical example
TABLE FILE XYZ
SUM COST AS PREV_COST
BY MODEL AS MODELP
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS HOLD1
END

Following line should be
Join Model in abc to modelp in hold1 as j1
Of course in upper case.

JOIN MODEL IN ABC TO MODELP IN XYZ AS J1
TABLE FILE ABC
PRINT MODEL MODELP PREV_COST ...
END

my actual code from the report did do that....

NEWDIV/A27 = '''' | DIVISION || '''';

I just didnt't carry it into my test procedure. But it wasn't working for me either. I'll just recode the report to use a join which I feel more comfortable with anyway. It's just that I have 7 version of this report to fix.....