Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Using DECODE(CPDHOLD ELSE 'XXX');

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Using DECODE(CPDHOLD ELSE 'XXX');
 Login/Join
 
Master
posted
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? Confused
Sometimes the ideas are great, but getting it to work is disconnect.

Thanks,

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


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Master
posted Hide Post
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!


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Master
posted Hide Post
NOOO!!!!


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Master
posted Hide Post
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
]
Roll Eyes
gracias!


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Virtuoso
posted Hide Post
quote:
TABLE FILE CPDOCTOR
PRINT
PCDOCNUM NOPRINT



Why NOPRINT??




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Master
posted Hide Post
That was a mistake! Golly!


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Virtuoso
posted Hide Post
And Tom
is it SOLVED ??




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
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.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
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.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
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
]


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Virtuoso
posted Hide Post
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.




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Guru
posted Hide Post
Have you tried using the 'DB_LOOKUP' function. This allows you to get the name without having to join to the table?


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Virtuoso
posted Hide Post
RS: version 5 does not have lookup....

Ginny: you are right...mistake.




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Guru
posted Hide Post
FD,

He states that he has also 7.4 & 7.6


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Master
posted Hide Post
I am working on this when I get down time from other tasks.

Thanks everyone for the entusiastic support!

Nice Thread


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Virtuoso
posted Hide Post
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.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Using DECODE(CPDHOLD ELSE 'XXX');

Copyright © 1996-2020 Information Builders