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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
IN FILE
 Login/Join
 
Platinum Member
posted
Can somebody tell me what the BY statements have to be like in order to use IN FILE? Its weird because I have been trying something like this:

TABLE FILE T2ENCNTR
SUM CNT.ENCOUNTER_NUMBER AS 'DOCPATS'
LOS AS 'DOCLOS'
BY DRG
BY MONTH
WHERE PCDOCNUM IN ('06077','21185','05960','11331','22144','05707','10506');
WHERE DRG NOT-FROM '370' TO '375';
WHERE DISCHARGE_DATE FROM '&BEG1' TO '&END1' ;
WHERE COMPANY_CODE EQ '&HOSP' ;
WHERE ACTUAL_TOTAL_CHARGE GT 0 ;
WHERE EXPIRED_CODE NE 'Y';
WHERE DRG NE ' ';
WHERE ENCTR_USER_ALPHA_3 NE 'Q-VSIT';
ON TABLE HOLD AS BW_HOLD1
END

TABLE FILE T2ENCNTR
SUM CNT.ENCOUNTER_NUMBER AS 'TOTPATS'
AVE.LOS AS 'PCMHLOS'
BY DRG
BY MONTH
WHERE DRG IN FILE BW_HOLD1 ;
WHERE DISCHARGE_DATE FROM '&BEG1' TO '&END1' ;
WHERE COMPANY_CODE EQ '&HOSP' ;
WHERE ACTUAL_TOTAL_CHARGE GT 0 ;
WHERE EXPIRED_CODE NE 'Y';
WHERE DRG NE ' ';
WHERE ENCTR_USER_ALPHA_3 NE 'Q-VSIT';
-*ON TABLE HOLD AS DRG_ALOS
ON TABLE SET ONLINE-FMT EXL2K
END

and I get this (line 71 is the WHERE DRG IN FILE BW_HOLD1 ):

0 ERROR AT OR NEAR LINE 71 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC272) FORMAT ERROR IN DECODE OR FILE ELEMENT: ?¬éQI¥@
0 ERROR AT OR NEAR LINE 71 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: WHERE
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT


Windows version 768
 
Posts: 215 | Registered: March 16, 2006Report This Post
Platinum Member
posted Hide Post
Mark1,

I've never tried using IN FILE against a file containing multiple columns. So I'm not sure whether that might cause you problems. I do know you need FORMAT ALPHA on your HOLD statement: ON TABLE HOLD AS BW_HOLD1 FORMAT ALPHA. (In theory, BINARY will also work, but I haven't tried that.) If that does not work, I'd suggest creating a hold file with a single field. And if THAT doesn't work, create a SAVE file, instead of a HOLD file.


dwf
 
Posts: 135 | Location: Portland, OR | Registered: March 23, 2005Report This Post
Platinum Member
posted Hide Post
Cool. thanks. I will give it a try on Monday and reply back.


Windows version 768
 
Posts: 215 | Registered: March 16, 2006Report This Post
Virtuoso
posted Hide Post
I used this several times, but there are some rules and restrictions.
1) The hold file only needs to have one field, so in this case only the field DRG.
2) add the -RUN command after the table that creates the hold file.
3) the already mentioned "SAVE AS XXX FORMAT ALPHA"
4) the number of records or the total file size has a limit of 2K (I'm not sure about the exact size).

Hope this helps....




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
Virtuoso
posted Hide Post
As a clarification, when using a TABLE request to create a screening file for use in 'WHERE field in FILE filename':

Use:

ON TABLE HOLD (default binary format, no AS name). Alpha or numeric comparison. File may contain multiple fields (though for clarity maybe it shouldn't), comparison field must be the first field. (WHERE field in FILE HOLD). This is my preferred option as it causes no issues. WF recognises this as a HOLD file and acts accordingly.

ON TABLE HOLD FORMAT ALPHA (no AS name). For alpha comparison only, not numeric fields. File may contain multiple fields, comparison field must be the first field. (WHERE field in FILE HOLD).

ON TABLE HOLD AS fileName (default binary format). For alpha comparison only, not numeric fields. File must contain only a single field. Note that using just BY field places 2 fields into the HOLD file, use SET HOLDLIST=PRINTONLY. (WHERE field in FILE fileName).

ON TABLE HOLD AS fileName FORMAT ALPHA. Alpha or numeric comparison. File must contain only a single field for Alpha comparison, though strangely for numeric fields the file may contain the 2 fields created when using BY numericField. (WHERE field in FILE fileName).

ON TABLE SAVE AS fileName (default alpha format). The same as ON TABLE HOLD AS fileName FORMAT ALPHA.

ON TABLE SAVE AS fileName FORMAT BINARY. The same as ON TABLE HOLD AS fileName (default binary format).

ON TABLE SAVE. The same as ON TABLE HOLD AS fileName FORMAT ALPHA.

ON TABLE SAVE FORMAT BINARY. The same as ON TABLE HOLD AS fileName (default binary format).


File size limited to 'approximately 16,000 bytes' [sic] when using WHERE.

If a file is empty, then no screening is applied, i.e. all records are returned based on other screening, if any.

I would recommend that, for ease, Mark changes:
ON TABLE HOLD AS BW_HOLD1 to ON TABLE HOLD
and
WHERE DRG IN FILE BW_HOLD1 ; to WHERE DRG IN FILE HOLD;

Causing a slight aside for a moment.

There does not need to be a -RUN in between the TABLE requests. There seems to be a self-perpetuating myth that a -RUN is required under many and various circumstances. Though a -RUN can be placed in between requests if you really want to (some before me have even considered -RUN to be almost the work of the devil), the only time a -RUN is essential is when a piece of Dialogue Manager code relies on a piece of none-DM code to work prior to that DM command, and the DM does not recognise this state.
e.g.
TABLE FILE CAR
PRINT CAR
IF COUNTRY EQ 'PORTUGAL'
END
-IF &LINES EQ 0 GOTO MYLABEL;
-TYPE Got &LINES lines
TABLE FILE...
.
.
-MYLABEL

The reference to &LINES causes the TABLE request to run (actually everything in FOCSTACK is processed), zero records returned, flow passes to MYLABEL, TYPE statement and subsequent TABLE bypassed. No -RUN required. You can see this action with a -SET &ECHO=ALL;, where the -IF &LINES EQ 0 GOTO MYLABEL; is encountered, no DM action is initially taken, FOCSTACK is processed and only then is the line actually processed.

One of the very few times that a -RUN is essential is when a FILEDEF/ALLOC is followed by a -READ or -WRITE,e.g.
FILEDEF file DISK path/file.dat
-RUN
-READ file &variable.A4

will fail if the -RUN is omitted.

Otherwise the none DM commands (anything that does not have a - at the start of the line) get placed into FOCSTACK and are run at the end of the procedure (or at an -EXIT), in the order they are placed in the FOCSTACK, each line completing before the next line is read. An END statement, amongst others, will cause the commands drawn from FOCSTACK to be executed and FOCSTACK processing will pause until the execution is complete.

I don't mean to lecture (or maybe I do!), but I hope this clarifies things for some people.

(08:29 - cleared up a couple of small typos)

This message has been edited. Last edited by: Alan B,


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
Hi Alan

masters always lecture....it's their habit.

but we all can still learn a lot.




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
Alan,

I have used "in file" for quite a while and most of what you explained was news to me. This was one much appreciated lecture. Keep them coming.

Glenda


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Virtuoso
posted Hide Post
Lecture anytime.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Master
posted Hide Post
I'd like to add one more little tip on this subject. I have for years used the same multi-field SAVE file in both IF statements and in DECODE statements. For the multi-field SAVE file to work, the fields have to be separated with a blank.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Virtuoso
posted Hide Post
jgelona

Thanks for that addition.

For the example I quoted, WHERE field IN FILE HOLD, for a multi-field HOLD a blank field is NOT required. For SAVE files a blank field could be used between fields, but I wanted to keep this relatively simple.

For me, using a HOLD file is the simplest approach, without having to be concerned about any other factors.

Creating a file for a DECODE is another matter entirely. You must not forget the quotes (if required) and the blank (or comma) in between.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders