I am working on a report in which a data file provides me with a DOCID (e.g., 04059).
We have other files that provide DOCID and DOCNAME, but I CANNOT use them to get the specific type of doctor I am looking for.

I do not think that I can join these files to obtain the Doctor's name via his/her DOCID
so, I thought that perhaps I could dump all physician names and ids into a hold file and code:
COMPUTE DOCNAM/A30 = DECODE(CPDHOLD ELSE 'XXX');

Here is the code I have so far:

[
-TEST
SET ASNAMES=ON
-*
FILEDEF SVC_CDE DISK \\CLEVELANDPOINT\IBI\APPS\CIMDATA\SVC_CDE.TXT
-RUN
-*
FILEDEF CPDHOLD DISK \\CLEVELANDPOINT\IBI\APPS\CIMDATA\CPDHOLD.TXT
-RUN
-*
-SET &BEG1 = '20080901';
-SET &END1 = '20080930';
-*
TABLE FILE CPDOCTOR
PRINT
PCDOCNAM
BY PCDOCNUM
ON TABLE HOLD AS CPDHOLD
END
-RUN
-*
DEFINE FILE T2CHGDTL
CHG_CD/A7 = EDIT(F_KEY,'$$$$$$9999999');
END
-*
TABLE FILE T2CHGDTL
PRINT
CHG_CD
EDP#
SERVICE_DATE
F_KEY
BY COMPANY_CODE
WHERE COMPANY_CODE EQ '01';
WHERE CHG_CD EQ '3670086' OR '3672000';
WHERE MASTER_FILE_YEAR EQ '2008';
WHERE DATE_OF_SERVICE FROM '&BEG1' TO '&END1';
ON TABLE HOLD AS AFILE FORMAT ALPHA
END
-RUN
-*
JOIN CLEAR *
JOIN EDP# IN AFILE TO ENCOUNTER_NUMBER IN T2ENCNTR AS J1
-RUN
-*
DEFINE FILE AFILE
DOCID/A6 = EDIT(ATTENDING_PHYSICIAN,'999999$$$$');
END
-*
TABLE FILE AFILE
HEADING CENTER
" REPORT FROM AFILE "
" "
PRINT
DOCID
AND COMPUTE DOCNAM/A30 = DECODE(CPDHOLD ELSE 'XXX');
CHG_CD
DATE_OF_SERVICE
FEEDER_KEY
BY COMPANY_CODE
BY MEDICAL_RECORD_NUMBER
BY ENCOUNTER_NUMBER
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN

-EXIT
]

Does anyone have any ideas?
Sometimes the ideas are great, but getting it to work is disconnect.

Thanks,

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

The syntax for DECODE reading values from a file is:

DECODE fieldname(ddname [ELSE default]);

Why do you think you can't "join these files to obtain the Doctor's name via his/her DOCID"?

The main idea of decoding from file should work.

I would save it as an alpha file but you need to but some quotes around the names.

TABLE FILE DOCADRES
PRINT DOCID
COMPUTE SP/A2=' ';
COMPUTE NEW_DNAAM/A40='''' || DOCNAAM || '''';
ON TABLE SAVE AS TODECODE FORMAT ALPHA
END
DEFINE FILE xxx
DOCNAM/A40=DECODE DOCID(TODECODE ELSE 'No docter name'); 
END
....
TABLE FILE WHATEVER
PRINT
DOCID DOCNAM other fields
BY COUNTRY
END

....

Frank,

I tried your example and I got a
(FOC273) SYNTAX ERROR IN DECODE.

My data in the TODECODE SAVE file looks like:

04059 Smith John MD
00369 Nakreet Abdullah N MD

I have not done this in 15 years, but if
memory serves, don't I need each of the
fields to be surronded by quotes?

RE:
'04059' 'Smith John MD'
'00369' 'Nakreet Abdullah N MD'

I'm gonna try that, and see what happens.

Thanks!

NOOO!!!!

Yes, the quotes around the name is needed, but not around the DOCID.
What is the original field size for the namefield?
You add some quotes and space, but I think I forgot the -RUN command before the DEFINE command.

Here are the field formats:

IN CPDOCTOR FILE:
PCDOCNUM/A6
PCDOCNAM/A25

IN T2ENCNTR
ATTENDING_PHYSICIAN/A10

[
FILEDEF SVC_CDE DISK \\CLEVELANDPOINT\IBI\APPS\CIMDATA\SVC_CDE.TXT
-RUN
-*
FILEDEF CPDHOLD DISK \\CLEVELANDPOINT\IBI\APPS\CIMDATA\CPDHOLD.TXT
-RUN
-*
-SET &BEG1 = '20080901';
-SET &END1 = '20080930';
-*
TABLE FILE CPDOCTOR
PRINT
PCDOCNUM NOPRINT
COMPUTE SP/A2 = ' ';
COMPUTE NEW_DNAAM/A40 = '''' ||PCDOCNAM|| '''';
ON TABLE SAVE FORMAT ALPHA AS CPDHOLD
END
-RUN
-*
DEFINE FILE T2CHGDTL
CHG_CD/A7 = EDIT(FEEDER_KEY,'$$$$$$9999999');
END
-*
TABLE FILE T2CHGDTL
PRINT
CHG_CD
ENCOUNTER_NUMBER
DATE_OF_SERVICE
FEEDER_KEY
BY COMPANY_CODE
WHERE COMPANY_CODE EQ '01';
WHERE CHG_CD EQ '3670086' OR '3672000';
WHERE MASTER_FILE_YEAR EQ '2008';
WHERE DATE_OF_SERVICE FROM '&BEG1' TO '&END1';
ON TABLE HOLD AS AFILE FORMAT ALPHA
END
-RUN
? HOLD AFILE
-RUN
-*
JOIN CLEAR *
JOIN ENCOUNTER_NUMBER IN AFILE TO ENCOUNTER_NUMBER IN T2ENCNTR AS J1
-RUN
-*
DEFINE FILE AFILE
DOCID/A6 = EDIT(ATTENDING_PHYSICIAN,'999999$$$$');
DOCNAM/A40 = DECODE DOCID(CPDHOLD ELSE 'Dr. Name Unknown');
-*DOCNAM/A40 = DECODE DOCID(TODECOD ELSE 'No docter name');
END
? HOLD AFILE
-RUN
-EXIT
-*
TABLE FILE AFILE
HEADING CENTER
" REPORT FROM AFILE "
" "
PRINT
DOCID DOCNAM
-*AND COMPUTE DOCNAM/A30 = DECODE(CPDHOLD ELSE 1);
CHG_CD
DATE_OF_SERVICE
FEEDER_KEY
BY COMPANY_CODE
BY MEDICAL_RECORD_NUMBER
BY ENCOUNTER_NUMBER
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN

-EXIT
]

gracias!

quote:

TABLE FILE CPDOCTOR
PRINT
PCDOCNUM NOPRINT

Why NOPRINT??

That was a mistake! Golly!

And Tom
is it SOLVED ??

DECODE has limits, how many doctors are going to be in this decode file, i.e. what is the size of the file ?

You are right Waz, but that does not give the error he got...

Frank, My concern is that with names, the limit may be reached very quickly, I would have thought that a join to get the name would be far better.

Sorry guys...out of the office friday to argue with my car engine at repair shop.

I am still getting error.

[
FILEDEF SVC_CDE DISK \\CLEVELANDPOINT\IBI\APPS\CIMDATA\SVC_CDE.TXT
-RUN
-*
FILEDEF CPDHOLD DISK \\CLEVELANDPOINT\IBI\APPS\CIMDATA\CPDHOLD.TXT
-RUN
-*
-SET &BEG1 = '20080901';
-SET &END1 = '20080930';
-*
TABLE FILE CPDOCTOR
PRINT
PCDOCNUM
COMPUTE SP/A2 = ' ';
COMPUTE NEW_DNAAM/A40 = '''' || PCDOCNAM || '''';
ON TABLE SAVE FORMAT ALPHA AS CPDHOLD
END
-RUN
ALPHANUMERIC RECORD NAMED CPDHOLD
0 FIELDNAME ALIAS FORMAT LENGTH
PCDOCNUM PCDOCNUM A6 6
SP A2 2
PCDOCNAM PCDOCNAM A25 25
NEW_DNAAM A40 40
TOTAL 73
1
0 NUMBER OF RECORDS IN TABLE= 6517 LINES= 6517
0
-*
DEFINE FILE T2CHGDTL
CHG_CD/A7 = EDIT(FEEDER_KEY,'$$$$$$9999999');
END
-*
TABLE FILE T2CHGDTL
PRINT
CHG_CD
ENCOUNTER_NUMBER
DATE_OF_SERVICE
FEEDER_KEY
BY COMPANY_CODE
WHERE COMPANY_CODE EQ '01';
WHERE CHG_CD EQ '3670086' OR '3672000';
WHERE MASTER_FILE_YEAR EQ '2008';
WHERE DATE_OF_SERVICE FROM '20080901' TO '20080930';
ON TABLE HOLD AS AFILE FORMAT ALPHA
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 16 LINES= 16
? HOLD AFILE
-RUN
0DEFINITION OF HOLD FILE: AFILE
0FIELDNAME ALIAS FORMAT
COMPANY_CODE E01 A5
CHG_CD E02 A7
ENCOUNTER_NUMBER E03 A20
DATE_OF_SERVICE E04 P9
FEEDER_KEY E05 A20
-*
JOIN CLEAR *
JOIN ENCOUNTER_NUMBER IN AFILE TO ENCOUNTER_NUMBER IN T2ENCNTR AS J1
-RUN
-*
DEFINE FILE AFILE
DOCID/A6 = EDIT(ATTENDING_PHYSICIAN,'999999$$$$');
DOCNAM/A40 = DECODE DOCID(CPDHOLD ELSE 'Dr. Name Unknown');
-*DOCNAM/A40 = DECODE DOCID(TODECOD ELSE 'No docter name');
END
-GOTO XRPT
-XRPT
TABLE FILE AFILE
HEADING CENTER
" REPORT FROM AFILE "
" "
PRINT
DOCID
DOCNAM
-*AND COMPUTE DOCNAM/A30 = DECODE(CPDHOLD ELSE 1);
CHG_CD
DATE_OF_SERVICE
FEEDER_KEY
BY COMPANY_CODE
BY MEDICAL_RECORD_NUMBER
BY ENCOUNTER_NUMBER
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
(FOC273) SYNTAX ERROR IN DECODE
0 ERROR AT OR NEAR LINE 91 IN PROCEDURE wheredecode
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: DOCNAM
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
-EXIT
]

Tom

I see two problems

1) you get 3 fields in the CPDHOLD file, it should be two.

2) your file has 6517 lines, that is to mutch for the decode function.

error 1: put these lines before the TABLE FILE CPDOCTOR

-SET HOLDLIST=PRINTONLY

And/or do a define before the table where you create the NEW_DNAAM instead of the compute in the table.

error 2: if the decode is the only solution you should try to select less records.

In your second part of the fex you create the AFILE. It has 16 records, is that also the number of DOCID's you will expect?
If so, that might be the solution.

Re: Frank's reply. The HOLDLIST SET doesn't have a dash in front of it.

And as Francis said earlier, why can't you do a join? That would eliminate the size problem.

Have you tried using the 'DB_LOOKUP' function. This allows you to get the name without having to join to the table?

RS: version 5 does not have lookup....

Ginny: you are right...mistake.

FD,

He states that he has also 7.4 & 7.6

I am working on this when I get down time from other tasks.

Thanks everyone for the entusiastic support!

Tom,

Definitely do a JOIN. With your data you can only have some 950 records in your DECODE file (limited to 32K). Create a FOCUS file instead of your sequential file:

SET HOLDLIST = PRINTONLY
TABLE FILE CPDOCTOR
PRINT PCDOCNAM
BY PCDOCNUM
ON TABLE HOLD AS CPDHOLD FORMAT FOCUS INDEX PCDOCNUM
END
-RUN
and further down
...
JOIN CLEAR *
JOIN ENCOUNTER_NUMBER IN AFILE TO ENCOUNTER_NUMBER IN T2ENCNTR AS J1
JOIN DOCID IN AFILE TO PCDOCNUM IN CPDHOLD AS J2

This should do the trick.