I am trying to join a field (ATTENDING_PHYSICIAN/A10 in MICUCHGC table) to
another field (PCDOCNUM/A6 IN CPDOCTOR).

I think I am doing this correctly, and I've made changes to original coding. Yet I still get the error captioned below.

Any ideas

[
-*
SET ALL = ON, ASNAMES = ON
-*
-*FILEDEF MG_CDE DISK \\CLEVELANDPOINT\IBI\APPS\CIMDATA\MG_CDE.TXT
-*RUN
-*
-SET &BEG1 = '20080901';
-SET &END1 = '20080901';
-*
JOIN CLEAR *
JOIN PCDOCHGP IN CPDOCTOR TO ALL PCGRPGRP IN CPGRP AS J111
-RUN
-*
TABLE FILE CPDOCTOR
PRINT
PCDOCSPC
PCDOCHSB
PCGRPNAM
PCDOCNAM
BY PCDOCNUM
ON TABLE HOLD AS DRNMGRP
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 6546 LINES= 6546
?FF DRNMGRP
-RUN
FILENAME= DRNMGRP
PCDOCNUM E01 A6
PCDOCSPC E02 A5
PCDOCHSB E03 A3
PCGRPNAM E04 A25
PCDOCNAM E05 A25
-*
TABLE FILE PTCSCENS
HEADING
" MICU PATIENTS "
" "
PRINT
PT_NO
CH_NURS_STA_NEW
BY PT_NO
WHERE CH_NURS_STA_NEW EQ 'MICU';
WHERE CH_EFF_DATE FROM '20080901' TO '20080901';
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE HOLD AS MICUPTS
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 17 LINES= 17
-*
TABLE FILE MICUPTS
HEADING
" MAX MICU PATIENTS "
" "
SUM
MAX.PT_NO
BY
PT_NO
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE HOLD AS MICUPTSMX
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 17 LINES= 15
-*
DEFINE FILE MICUPTSMX
NEWPT_NO/A20 = EDIT(PT_NO, '$$$$$$$99999999');
END
-*
TABLE FILE MICUPTSMX
HEADING
" NEWPT_NO MICU PATIENTS "
" "
PRINT
NEWPT_NO
PT_NO
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE HOLD AS MICUPTSMX1
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 15 LINES= 15
-*
JOIN CLEAR *
JOIN NEWPT_NO IN MICUPTSMX1 TO ALL ENCOUNTER_NUMBER IN T2CHGDTL AS J222
-RUN
-*
DEFINE FILE MICUPTSMX1
SVC_CD/A7 =EDIT(FEEDER_KEY,'$$$$$$9999999');
END
-*
TABLE FILE MICUPTSMX1
HEADING CENTER
" JOIN MICU PATIENTS TO T2CHGDTL "
" FROM FILE MICUPTSMX1 "
PRINT
SVC_CD
FEEDER_KEY
ENCOUNTER_NUMBER
BY NEWPT_NO
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE HOLD AS MICUCHG
END
-RUN
SENT
SENT
0 NUMBER OF RECORDS IN TABLE= 18367 LINES= 18367
-*
JOIN CLEAR *
JOIN NEWPT_NO IN MICUCHG TO ENCOUNTER_NUMBER IN T2ENCNTR AS J333
-RUN
-*
DEFINE FILE MICUCHG
VENTCNT/I5 = IF SVC_CD EQ '3370085' OR '3160407' OR '3370502'
OR '3370065' OR '3370165' OR '3370265'
OR '3370508' OR '3370510' OR '3370613'
OR '3370614' OR '3370615' OR '3370616'
OR '3370617' OR '3370618' THEN 1 ELSE 0;
-*
TRCHCNT/I5 = IF SVC_CD EQ '3160116' OR '3160117' THEN 1 ELSE 0;
-*
LOS/D10.1 = LENGTH_OF_STAY * 1.000001;
END
-*
TABLE FILE MICUCHG
SUM
MAX.LOS
MAX.VENTCNT
MAX.TRCHCNT
BY NEWPT_NO AS 'PT_NO'
BY MEDICAL_RECORD_NUMBER AS 'MRNO'
BY ATTENDING_PHYSICIAN
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE HOLD AS MICUCHGC
-*ON TABLE HOLD AS MICUCHGC FORMAT FOCUS INDEX NEWKEY
END
-RUN
SENT
SENT
0 NUMBER OF RECORDS IN TABLE= 18367 LINES= 15
-*
JOIN CLEAR *
JOIN ATTENDING_PHYSICIAN IN MICUCHGC TO ALL PCDOCNUM IN DRNMGRP AS J444
-RUN
-*
DEFINE FILE MICUCHGC
DOCID/A6 = EDIT(ATTENDING_PHYSICIAN,'999999$$$$');
END
-*
TABLE FILE MICUCHGC
HEADING
" Let's Test These Values "
" "
PRINT
DOCID
ATTENDING_PHYSICIAN
PCDOCNUM
PCDOCNAM
IF RECORDLIMIT EQ 50
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
(FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED
-*
-EXIT
]


Thanks!

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

quote:

I am trying to join a field (ATTENDING_PHYSICIAN/A10 in MICUCHGC table) to
another field (PCDOCNUM/A6 IN CPDOCTOR).

What I see is not that join

quote:

JOIN CLEAR *
JOIN ATTENDING_PHYSICIAN IN MICUCHGC TO ALL PCDOCNUM IN DRNMGRP AS J444
-RUN

Is DRNMGRP a flat file if it is then that's the problem. Convert it to a FOCUS DB with an index on PCDOCNUM.

ALSO make sure that ATTENDING_PHYSICIAN and PCDOCNUM are the same size ie BOTH A10 or A6 not a mix as you have now

JG is on the money,

DRNMGRP is a flat file, definitely hold it as a FOCUS file and index DRNMGRP.

Was you intention to join ATTENDING_PHYSICIAN to PCDOCNUM, or do a define based join with DOCID?

You can also sort MICUCHGC by ATTENDING_PHYSICIAN first and not need the FOCUS file.

And of course, make sure that the JOIN fileds are the same size, although FOCUS should (used to) augment the shorter field with spaces.