We have about 40 status codes which should be divided among 5 categories for a more readable report. I have a file listing each status code and its category. I'm trying to use DECODE, but it's not working.

FILEDEF STATUS_FILE DISK C:\IBI\...\StatusDecode.txt
DEFINE FILE MY_TABLE
STATUS_CATEGORY/A10 = DECODE (STATUS_FILE ELSE 'Unknown')
END
...

Is .txt an appropriate format here? If not, what is? Here's what my StatusDecode.txt file looks like:

'St_Code','St_Category'
'AD','Admitted'
'AT','Admitted'
'DY','Denied'
'FC','Review'
...

Anybody have any hints as to what I'm doing wrong? I keep getting this error message in my DEFINE:
"(FOC 272) FORMAT ERROR IN DECODE OR FILE ELEMENT: STATUS_LIST"

Assuming your decode file is formatted okay, I never put the comma between the values, you have one entry that is 11 characters all the others are less, the A10 is probably the problem. If changing it to A11 doesn't work then eliminate the commas between the columns and just put a space.

Thanks, Leah, but that doesn't work either. I initially had spaces in the file and tried commas when that didn't work. And I expanded "St_Category" for clarity in this example (oops, didn't count its chars), but in my real file, it's just "St_Cat", which is under 10 chars.

There's an error in your decode statment.

The syntax is:

DECODE fieldname (code1 result1 code2 result2...[ELSE default ]);
DECODE fieldname (file_name [ELSE default ]);

Your're missing the field you want to decode.

Cheers,

Pietro.

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

Erin,

Look at the file and see if there is a last line that is 'blank'. Seems to me I had that problem and by deleting that last entry so the text file didn't have it, it worked.

Say 'X' is the cursor position in the file on the last line.

...
'aa' 'all alike'
X

change to

...
'aa' 'all alike'X

So where the X is represents the last of the file.

I got all optimistic when you both posted simple-but-critical fixes, but no such luck.

Pietro, what you found was a transcription error. I omitted the fieldname in my post but it's present in my actual file. Here's what I'm working with:

FILEDEF STATUS_LIST DISK C:\IBI\...\AdmStatusDecode.TXT
DEFINE FILE ADMGAPTB_UNL_PRD
ADMSTAT/A10=DECODE ADM_STATUS (STATUS_LIST ELSE 'X');
...

Leah, I checked my file, and there's no line break after the last value.

Meanwhile I've removed the 'headings' from my text file, since headings wouldn't be appropriate if it was just reading the file into a DECODE line.

Erin, I just realized you are at UNL. It may be the program cannot find the decode file because it needs to be on the app server if you are doing a run remote.

Maybe we should take this offline. I put all my decodes on our UNO directory on our shared app server.

Give me a call or send an email.

402-554-3751, lcross@mail.unomaha.edu

Hace you tried removing the quotes on the data to be decoded?

AD 'Admitted'<br />AT 'Admitted'<br />DY 'Denied'<br />FC 'Review'

After trying various things inspired by Pietro and Leah's suggestions (many thanks to both of them), I finally hit the solution. In my FILEDEF, I shortened "STATUS_LIST" to "STATLIST". Voila--it ran with no errors.

It seems there's simply a character limit (probably 8) on the temporary name assigned to the decode file. (Note: I'm using 4.3.6; newer versions may be more forgiving.)

Case closed.