I got a procedure (the short version) which creates a holdfile:
DEFINE FILE METINGENHD NEW_OPNDAT_DATE/YYMD=HDATE(J3.OPNAME_OPNAME.OPNDAT,'YYMD'); NEW_DAT_GEBDAT/YYMD=HDATE(J4.PATIENT_PATIENT.GEBDAT,'YYMD'); END
TABLE FILE METINGENHD PRINT COMPUTE VOLWASSENPAT/I8 = ( ( DATEDIF(J4.PATIENT_PATIENT.NEW_DAT_GEBDAT, NEW_OPNDAT_DATE, 'Y') ) GE 18 ); METINGENHD.SEG01.FOCLIST AS 'METINGFOCLIST' ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE HOLD AS MUSTHLD2012 FORMAT FOCUS END
This works Fine as long as I run the procedure in DS.
When I schedule the procedure in Reportcaster 2 run at night. I get the following error in the log file:
"INVALID TYPE OF ARGUMENT #1 FOR USER FUNCTION HDATE FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: J4.PATIENT_PATIENT.NEW_DAT_GEBDAT INCOMPLETE REQUEST STATEMENT THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: MUSTHLD2012 "
What is wrong with mine procedure?
Thanx in advance,
NordinThis message has been edited. Last edited by: Nordin Ben Ahmed,
7.7.03 OS is Windows 7 We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
January 02, 2013, 09:00 AM
Wep5622
As I understand it, DEFINE'd fields aren't part of the join and therefore end up in a different segment, most likely SEG01 in your case.
You can verify what the file looks like with ?FF or CHECK FILE .. PICTURE to see in which segment which fields go.
(Admittedly, this whole segments business is a bit fuzzy. It seems like it could be quite powerful, but ibi also made sure that multi-segment files are rather difficult to create :/)
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
January 02, 2013, 07:50 PM
Doug
What's the format of and sample values for NEW_OPNDAT_DATE and NEW_DAT_GEBDAT?
quote:
but ibi also made sure that multi-segment files are rather difficult to create
Actually, it's more accurate to say that there's a lot more to multi-segmented files then you may expect. But, you're right in saying
quote:
It seems like it could be quite powerful
They're worth their weight in data and worth learning about their power...
In FOCUS Since 1983 ~ from FOCUS to WebFOCUS. Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
January 04, 2013, 09:53 AM
Nordin Ben Ahmed
quote:
Originally posted by Doug: What's the format of and sample values for NEW_OPNDAT_DATE and NEW_DAT_GEBDAT?[.
Format of define field: YYMD The original format was: HYYMDs
7.7.03 OS is Windows 7 We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
January 04, 2013, 05:27 PM
Mighty Max
Do your join. Then print the columns you need from the join to a hold file. Then manipulate the hold file.
JOIN XYZ IN METINGENHD TO XYZ IN WHATEVER1 AS J3
JOIN XYZ IN METINGENHD TO XYZ IN WHATEVER2 AS J4
TABLE FILE METINGENHD
PRINT
J3.OPNAME_OPNAME.OPNDAT AS 'OPNDAT'
J4.PATIENT_PATIENT.GEBDAT AS 'GEBDAT'
METINGENHD.SEG01.FOCLIST AS 'METINGFOCLIST'
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS H1
END
-RUN
DEFINE FILE H1
NEW_OPNDAT_DATE/YYMD=HDATE(OPNDAT,'YYMD');
NEW_DAT_GEBDAT/YYMD=HDATE(GEBDAT,'YYMD');
END
TABLE FILE H1
PRINT
COMPUTE VOLWASSENPAT/I8 = ( ( DATEDIF(NEW_DAT_GEBDAT, NEW_OPNDAT_DATE, 'Y') ) GE 18 );
METINGFOCLIST
ON TABLE HOLD AS MUSTHLD2012 FORMAT FOCUS
END
-RUN
If this last suggestion does not work I would think of adding two new computed fields to the master descriptions that already convert the date time fields to smartdate fields If the original data is SQL you can do that by adding an extra line in the master that points to the same real fields but have an other format picture.
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
January 07, 2013, 09:32 AM
Nordin Ben Ahmed
Hi,
Thanx 4 the input, I tried all your suggestions. But unfortunately the same case. Is it possible that RC is acting this way, because the GEBDAT field is a Primary Key Field? That to me is the only logical consclusion I can think of, because I got another field with exactly the same format, same expression, same use, which works just fine.
Any ideas, how 2 tackle this?
Thanx,
Nordin
7.7.03 OS is Windows 7 We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
January 07, 2013, 10:59 AM
susannah
caster seems to be able to find whatever is J3, but it can't find J4. might be a pathing issue remember caster doesn't run stuff that a live user might have in a user profile. make sure the pathing is the same as it is when you run it live. specify the path at the top of your fex, to be sure
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
January 07, 2013, 02:03 PM
FrankDutch
Did you try to put the computed field in the define segment instead in the table ? First a hold file might also be an option
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
January 09, 2013, 04:03 AM
Nordin Ben Ahmed
Hi,
@frank, yes I did.. See my whole fex.
@Susanne I did specify the join by giving it a unique Tag.
The whole FEX:
JOIN METINGENHD.SEG01.MSESSIEID IN metingenhd TO MULTIPLE METINGEN_MEETSESS.METINGEN_MEETSESS.MSESSIEID IN metingen_meetsess TAG J2 AS J2 END JOIN INNER J2.METINGEN_MEETSESS.OBJECTID IN metingenhd TO MULTIPLE OPNAME_OPNAME.OPNAME_OPNAME.PLANNR IN opname_opname TAG J3 AS J3 END JOIN METINGENHD.SEG01.PATIENTNR IN metingenhd TO MULTIPLE PATIENT_PATIENT.PATIENT_PATIENT.PATIENTNR IN patient_patient TAG J4 AS J4 END DEFINE FILE METINGENHD NEW_DAT_GEBDAT/YYMD=HDATE(J4.PATIENT_PATIENT.GEBDAT,'YYMD'); FSAMEDAY/I1=IF J3.OPNAME_OPNAME.ONTSLDAT EQ J3.OPNAME_OPNAME.OPNDAT THEN 1 ELSE 0; OPNDAT_JAAR/I4=HPART( J3.OPNAME_OPNAME.OPNDAT, 'YEAR', 'I4'); OPNDAT_MAAND/I2=HPART( J3.OPNAME_OPNAME.OPNDAT, 'MONTH', 'I2'); ONTSLDAT_MAAND/I2=HPART(J3.OPNAME_OPNAME.ONTSLDAT, 'MONTH', 'I2'); ONTSLDAT_JAAR/I4=HPART(J3.OPNAME_OPNAME.ONTSLDAT, 'YEAR', 'I4'); ONTSLUUR/I2=EDIT(EDIT(J3.OPNAME_OPNAME.ONTSLTIJD,'99$$$')); ONTSLMINUUT/I2=EDIT(EDIT(J3.OPNAME_OPNAME.ONTSLTIJD,'$$$99')); ONTSLDAT2/HYYMDs=HADD(J3.OPNAME_OPNAME.ONTSLDAT, 'MINUTE', ((ONTSLUUR*60) + ONTSLMINUUT), 8, 'HYYMDI'); OPNUUR/I2=EDIT(EDIT(J3.OPNAME_OPNAME.OPNTIJD,'99$$$')); OPNMINUUT/I2=EDIT(EDIT(J3.OPNAME_OPNAME.OPNTIJD,'$$$99')); OPNDAT2/HYYMDs=HADD( J3.OPNAME_OPNAME.OPNDAT, 'MINUTE', ((OPNUUR*60) + OPNMINUUT), 8, 'HYYMDI'); F24HOUR/I1=IF HDIFF(ONTSLDAT2, OPNDAT2, 'HOUR', 'D12.2') LT 36 THEN 1 ELSE 0; DDR_MAAND/I2=HPART(METINGENHD.SEG01.MEET_DATUM, 'MONTH', 'I2'); DDR_JAAR/I4=HPART(METINGENHD.SEG01.MEET_DATUM, 'YEAR', 'I4'); DDRUUR/I2=EDIT(EDIT(METINGENHD.SEG01.MEET_TIJD,'99$$$')); DDRMINUUT/I2=EDIT(EDIT(METINGENHD.SEG01.MEET_TIJD,'$$$99')); DDRDAT2/HYYMDs=HADD(METINGENHD.SEG01.MEET_DATUM, 'MINUTE', ((DDRUUR*60) + DDRMINUUT), 8, 'HYYMDI'); F24HOURDDR/I1=IF HDIFF(DDRDAT2, OPNDAT2, 'MINUTE', 'D12.2') LT 4320 THEN 1 ELSE 0; NEW_OPNDAT_DATE/YYMD=HDATE(J3.OPNAME_OPNAME.OPNDAT,'YYMD'); AFDMEEC4/I1=IF J3.OPNAME_OPNAME.AFDELING EQ 'C4' AND OPNDAT_JAAR LT 2012 THEN 1 ELSE 0; AFDMEED4/I1=IF J3.OPNAME_OPNAME.AFDELING EQ 'D4' AND OPNDAT_JAAR LT 2012 THEN 1 ELSE 0; MEETUUR/I2=EDIT(EDIT(MEET_TIJD,'99')); MEETMINUUT/I2=EDIT(EDIT(MEET_TIJD,'$$$99')); MEET_DATUMDT/HYYMDs=HADD(METINGENHD.SEG01.MEET_DATUM, 'MINUTE', ((MEETUUR*60) + MEETMINUUT), 8, 'HYYMDI'); END TABLE FILE METINGENHD PRINT NEW_DAT_GEBDAT AS 'NEW_DAT_GEBDAT' NEW_OPNDAT_DATE AS 'NEW_OPNDAT_DATE' METINGENHD.SEG01.FOCLIST AS 'METINGFOCLIST' METINGENHD.SEG01.MEET_DATUM METINGENHD.SEG01.MEET_TIJD METINGENHD.SEG01.MEET_DATUMDT METINGENHD.SEG01.antwoord J2.METINGEN_MEETSESS.KOPP_ID J2.METINGEN_MEETSESS.OBJECTID J2.METINGEN_MEETSESS.PATIENTNR J2.METINGEN_MEETSESS.EERSTEDATU J2.METINGEN_MEETSESS.EERSTETIJD J2.METINGEN_MEETSESS.STATUS AS 'METINGSTATUS' J3.OPNAME_OPNAME.AFDELING J3.OPNAME_OPNAME.LOCATIE J3.OPNAME_OPNAME.OPNDAT AS 'OPNDATHLD' J3.OPNAME_OPNAME.OPNDAT2 AS 'OPNDATDT' J3.OPNAME_OPNAME.STATUS AS 'OPNAMESTATUS' J3.OPNAME_OPNAME.PATIENTNR AS 'OPNPATNR' J3.OPNAME_OPNAME.PLANNR BY METINGENHD.SEG01.MSESSIEID AS 'METINGSESSIEID' WHERE ( J3.OPNAME_OPNAME.STATUS EQ '4' ) OR ( J3.OPNAME_OPNAME.STATUS EQ '5' ); WHERE antwoord NE ')' OR 'U' OR ' '; WHERE J3.OPNAME_OPNAME.LOCATIE NE ' '; WHERE J3.OPNAME_OPNAME.F24HOURDDR EQ 1; WHERE J3.OPNAME_OPNAME.F24HOUR NE 1; WHERE J3.OPNAME_OPNAME.OPNTYPE NE 'K' OR 'M'; WHERE J3.OPNAME_OPNAME.AFDELING NE 'A4' OR 'A5' OR 'B5' OR 'C5' OR 'UDK' OR 'ZDK' OR 'ZKD3' OR 'E2' OR 'D2' OR 'ZDO' OR 'DBOH' OR 'ZB1'; WHERE J3.OPNAME_OPNAME.AFDMEEC4 NE 1; WHERE J3.OPNAME_OPNAME.AFDMEED4 NE 1; ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE HOLD AS MUSTHLD2012 FORMAT FOCUS END
DEFINE FILE MUSTHLD2012 OPLOSGEBDAT/I5=(DATEDIF(NEW_DAT_GEBDAT, NEW_OPNDAT_DATE, 'Y') ); END
APP HOLD ds13_kliniek
TABLE FILE MUSTHLD2012 PRINT METINGFOCLIST NEW_DAT_GEBDAT NEW_OPNDAT_DATE MEET_DATUM MEET_TIJD MEET_DATUMDT antwoord KOPP_ID OBJECTID PATIENTNR EERSTEDATU EERSTETIJD METINGSTATUS AFDELING LOCATIE OPNDATHLD OPNDATDT OPNAMESTATUS OPNPATNR PLANNR BY METINGSESSIEID WHERE MUSTHLD2012.SEG01.OPLOSGEBDAT GE 18; ON TABLE HOLD AS METINGENHLDMUST FORMAT FOCUS INDEX PLANNR
END
7.7.03 OS is Windows 7 We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
January 09, 2013, 02:08 PM
Mighty Max
I don't see anything out of the ordinary. Comment out stuff until you can find the breaking point in Report Caster. 1. Comment everything below the first hold file. Does it work in Report Caster? 2. Comment out the APP HOLD. Does it work in Report Caster?
Also do as susannah suggested and specify the APP PATH at the beginning of the fex.
I Got it working. The same define and the same compute, just in a later stage.
Everyone thanx 4 the input.
JOIN METINGENHD.SEG01.MSESSIEID IN metingenhd TO MULTIPLE METINGEN_MEETSESS.METINGEN_MEETSESS.MSESSIEID IN metingen_meetsess TAG J2 AS J2 END JOIN INNER J2.METINGEN_MEETSESS.OBJECTID IN metingenhd TO MULTIPLE OPNAME_OPNAME.OPNAME_OPNAME.PLANNR IN opname_opname TAG J3 AS J3 END JOIN METINGENHD.SEG01.PATIENTNR IN metingenhd TO MULTIPLE PATIENT_PATIENT.PATIENT_PATIENT.PATIENTNR IN patient_patient TAG J4 AS J4 END
DEFINE FILE METINGENHD FSAMEDAY/I1=IF J3.OPNAME_OPNAME.ONTSLDAT EQ J3.OPNAME_OPNAME.OPNDAT THEN 1 ELSE 0; OPNDAT_JAAR/I4=HPART( J3.OPNAME_OPNAME.OPNDAT, 'YEAR', 'I4'); OPNDAT_MAAND/I2=HPART( J3.OPNAME_OPNAME.OPNDAT, 'MONTH', 'I2'); ONTSLDAT_MAAND/I2=HPART(J3.OPNAME_OPNAME.ONTSLDAT, 'MONTH', 'I2'); ONTSLDAT_JAAR/I4=HPART(J3.OPNAME_OPNAME.ONTSLDAT, 'YEAR', 'I4'); ONTSLUUR/I2=EDIT(EDIT(J3.OPNAME_OPNAME.ONTSLTIJD,'99$$$')); ONTSLMINUUT/I2=EDIT(EDIT(J3.OPNAME_OPNAME.ONTSLTIJD,'$$$99')); ONTSLDAT2/HYYMDs=HADD(J3.OPNAME_OPNAME.ONTSLDAT, 'MINUTE', ((ONTSLUUR*60) + ONTSLMINUUT), 8, 'HYYMDI'); OPNUUR/I2=EDIT(EDIT(J3.OPNAME_OPNAME.OPNTIJD,'99$$$')); OPNMINUUT/I2=EDIT(EDIT(J3.OPNAME_OPNAME.OPNTIJD,'$$$99')); OPNDAT2/HYYMDs=HADD( J3.OPNAME_OPNAME.OPNDAT, 'MINUTE', ((OPNUUR*60) + OPNMINUUT), 8, 'HYYMDI'); F24HOUR/I1=IF HDIFF(ONTSLDAT2, OPNDAT2, 'HOUR', 'D12.2') LT 36 THEN 1 ELSE 0; DDR_MAAND/I2=HPART(METINGENHD.SEG01.MEET_DATUM, 'MONTH', 'I2'); DDR_JAAR/I4=HPART(METINGENHD.SEG01.MEET_DATUM, 'YEAR', 'I4'); DDRUUR/I2=EDIT(EDIT(METINGENHD.SEG01.MEET_TIJD,'99$$$')); DDRMINUUT/I2=EDIT(EDIT(METINGENHD.SEG01.MEET_TIJD,'$$$99')); DDRDAT2/HYYMDs=HADD(METINGENHD.SEG01.MEET_DATUM, 'MINUTE', ((DDRUUR*60) + DDRMINUUT), 8, 'HYYMDI'); F24HOURDDR/I1=IF HDIFF(DDRDAT2, OPNDAT2, 'MINUTE', 'D12.2') LT 4320 THEN 1 ELSE 0; NEW_OPNDAT_DATE/YYMD=HDATE(J3.OPNAME_OPNAME.OPNDAT,'YYMD'); AFDMEEC4/I1=IF J3.OPNAME_OPNAME.AFDELING EQ 'C4' AND OPNDAT_JAAR LT 2012 THEN 1 ELSE 0; AFDMEED4/I1=IF J3.OPNAME_OPNAME.AFDELING EQ 'D4' AND OPNDAT_JAAR LT 2012 THEN 1 ELSE 0; MEETUUR/I2=EDIT(EDIT(MEET_TIJD,'99')); MEETMINUUT/I2=EDIT(EDIT(MEET_TIJD,'$$$99')); MEET_DATUMDT/HYYMDs=HADD(METINGENHD.SEG01.MEET_DATUM, 'MINUTE', ((MEETUUR*60) + MEETMINUUT), 8, 'HYYMDI'); END TABLE FILE METINGENHD PRINT METINGENHD.SEG01.FOCLIST AS 'METINGFOCLIST' METINGENHD.SEG01.MEET_DATUM METINGENHD.SEG01.MEET_TIJD METINGENHD.SEG01.MEET_DATUMDT METINGENHD.SEG01.antwoord J4.PATIENT_PATIENT.GEBDAT J3.OPNAME_OPNAME.NEW_OPNDAT_DATE J2.METINGEN_MEETSESS.KOPP_ID J2.METINGEN_MEETSESS.OBJECTID J2.METINGEN_MEETSESS.PATIENTNR J2.METINGEN_MEETSESS.EERSTEDATU J2.METINGEN_MEETSESS.EERSTETIJD J2.METINGEN_MEETSESS.STATUS AS 'METINGSTATUS' J3.OPNAME_OPNAME.AFDELING J3.OPNAME_OPNAME.LOCATIE J3.OPNAME_OPNAME.OPNDAT AS 'OPNDATHLD' J3.OPNAME_OPNAME.OPNDAT2 AS 'OPNDATDT' J3.OPNAME_OPNAME.STATUS AS 'OPNAMESTATUS' J3.OPNAME_OPNAME.PATIENTNR AS 'OPNPATNR' J3.OPNAME_OPNAME.PLANNR BY METINGENHD.SEG01.MSESSIEID AS 'METINGSESSIEID' WHERE ( J3.OPNAME_OPNAME.STATUS EQ '4' ) OR ( J3.OPNAME_OPNAME.STATUS EQ '5' ); WHERE antwoord NE ')' OR 'U' OR ' '; WHERE J3.OPNAME_OPNAME.LOCATIE NE ' '; WHERE J3.OPNAME_OPNAME.F24HOURDDR EQ 1; WHERE J3.OPNAME_OPNAME.F24HOUR NE 1; WHERE J3.OPNAME_OPNAME.OPNTYPE NE 'K' OR 'M'; WHERE J3.OPNAME_OPNAME.AFDELING NE 'A4' OR 'A5' OR 'B5' OR 'C5' OR 'UDK' OR 'ZDK' OR 'ZKD3' OR 'E2' OR 'D2' OR 'ZDO' OR 'DBOH' OR 'ZB1'; WHERE J3.OPNAME_OPNAME.AFDMEEC4 NE 1; WHERE J3.OPNAME_OPNAME.AFDMEED4 NE 1; ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE HOLD AS MUSTHLD2012 FORMAT FOCUS END
DEFINE FILE MUSTHLD2012 NEW_DAT_GEBDAT/YYMD=HDATE(MUSTHLD2012.SEG01.GEBDAT,'YYMD'); END
APP HOLD ds13_kliniek TABLE FILE MUSTHLD2012 PRINT COMPUTE VOLWASSENPAT/I8 = ( ( DATEDIF(NEW_DAT_GEBDAT, NEW_OPNDAT_DATE, 'Y') ) GE 18 ); METINGFOCLIST NEW_DAT_GEBDAT NEW_OPNDAT_DATE VOLWASSENPAT MEET_DATUM MEET_TIJD MEET_DATUMDT antwoord KOPP_ID OBJECTID PATIENTNR EERSTEDATU EERSTETIJD METINGSTATUS AFDELING LOCATIE OPNDATHLD OPNDATDT OPNAMESTATUS OPNPATNR PLANNR BY METINGSESSIEID WHERE TOTAL VOLWASSENPAT EQ 1; ON TABLE HOLD AS METINGENHLDMUST FORMAT FOCUS INDEX PLANNR
END
7.7.03 OS is Windows 7 We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)