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"?
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 30, 2008, 03:04 PM
FrankDutch
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
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
October 30, 2008, 05:22 PM
Tomsweb
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'
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.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
October 30, 2008, 05:44 PM
Tomsweb
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
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
November 02, 2008, 04:13 PM
Waz
DECODE has limits, how many doctors are going to be in this decode file, i.e. what is the size of the file ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
November 02, 2008, 04:58 PM
FrankDutch
You are right Waz, but that does not give the error he got...
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
November 02, 2008, 08:41 PM
Waz
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.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
November 03, 2008, 09:44 AM
Tomsweb
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 ]
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.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
November 04, 2008, 09:06 AM
GinnyJakes
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.
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.
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF