Re: ORACLE prepare error: ORA-00923: FROM keyword not found where

Helen -

The word AUDIT is a reserved word in Oracle. Because you're using it as
a synonym for one of your variables, Oracle stops parsing the statement,
and thus never finds the word FROM.

You can use the view V$RESERVED_WORDS to find Oracle's reserved words.

Carol Bristow
DPRA Incorporated
1300 N 17th St Suite 950
Rosslyn, VA 22209
Work: 703-841-8025
Fax: 703-524-9415

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
sunchunkui@HOTMAIL.COM
Sent: Tuesday, August 02, 2005 11:09 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: ORACLE prepare error: ORA-00923: FROM keyword not found where
expected.

Hi SAS-L,

We are using Oracle Clinical4.05 to automatically generate all SAS
programs of SAS views. The SAS views are all fine except one:ADMNSUM.
When I try to open this view, I got the following error message:

ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where
expected. SQL
statement: select STUDY STUDY, DCMNAME DCMNAME, DCMSUBNM
DCMSUBNM, SUBSETSN
SUBSETSN, DOCNUM DOCNUM, INVSITE INVSITE, INV INV, PT PT,
ACCESSTS ACCESSTS,
LOGINTS LOGINTS, LSTCHGTS LSTCHGTS, LOCKFLAG LOCKFLAG, CPEVENT
CPEVENT,
DCMDATE DCMDATE, DCMTIME DCMTIME, REPEATSN REPEATSN, ACTEVENT
ACTEVENT,
SUBEVENT_NUMBER SUBEVE, VISIT_NUMBER VISIT, QUALIFYING_VALUE
QUALIFYV,
QUALIFYING_QUESTION QUALIFYQ, LAB LAB, LABRANGE_SUBSET_NUMBER
LABSN,
LAB_ASSIGNMENT_TYPE_CODE LABATYPE, LAB_ID LAB_ID,
ADMIN_ALL_CRFS_DVN ALLCRFSN,
ADMIN_ALL_CRFS ALLCRFS, ADMIN_CENT_LAB_DVN CNTLABN,
ADMIN_CENT_LAB CNTLAB,
ADMIN_DCFS_CLOSED_DVN DCFSCLN, ADMIN_DCFS_CLOSED DCFSCL,
ADMIN_VAL_PROC_DVN
VALPRCN, ADMIN_VAL_PROC VALPRC, ADMIN_MEDICAL_REVIEW_DVN
MEDRVWN,
ADMIN_MEDICAL_REVIEW MEDRVW, ADMIN_SAES_REC_DVN SAESRCN,
ADMIN_SAES_REC
SAESRC, ADMIN_PATIENT_EVAL_DVN PTEVALN, ADMIN_PAT_EVAL PTEVAL,
ADMIN_CODING_COMP_DVN CODINGN, ADMIN_CODING_COMP CODING,
ADMIN_DRUG_REC_DVN
DRGRCN, ADMIN_DRUG_REC DRGRC, ADMIN_AUDIT_COMP_DVN AUDITN,
ADMIN_AUDIT_COMP
AUDIT, ADMIN_PATIENT_CLEAN_DVN PTCLEANN, ADMIN_PATIENT_CLEAN
PTCLEAN,
ADMIN_SCREEN_DATE DTSCRN, ADMIN_RAND_DATE DTRAND,
ADMIN_TERM_DATE DTTERM from
EPIC3$CURRENT.ADMIN_SUMMARY.

The program of OC automatically generated for this SAS view is as
follows (similar with other 27 SAS view programs, I can't tell any
differences):

libname extract '\\xxx\xxx\xxxxxx\';
proc sql;
connect to oracle(user="ops$myExtract" password="xxx"
path="@xxx.xxxxxx.xx") ;
create view EXTRACT.ADMNSUM as select
STUDY as STUDY label ="Clinical Study" format $15.
,DCMNAME as DCMNAME label ="DCM Name" format $16.
,DCMSUBNM as DCMSUBNM label ="DCM Subset Name" format $8.
,SUBSETSN as SUBSETSN label ="DCM Subset Number" format 3.
,DOCNUM as DOCNUM label ="Document Number" format $20.
,INVSITE as INVSITE label ="Site" format $10.
,INV as INV label ="Investigator" format $10.
,PT as PT label ="Patient" format $10.
,ACCESSTS as ACCESSTS label ="Accessible TS" format datetime.
,LOGINTS as LOGINTS label ="Login TS" format datetime.
,LSTCHGTS as LSTCHGTS label ="LstChg Ts" format datetime.
,LOCKFLAG as LOCKFLAG label ="Data Lock Flag" format $1.
,CPEVENT as CPEVENT label ="CPE Name" format $20.
,DCMDATE as DCMDATE label ="DCM Date" format $8.
,DCMTIME as DCMTIME label ="DCM Time" format $6.
,REPEATSN as REPEATSN label ="Repeat #" format 3.
,ACTEVENT as ACTEVENT label ="Actual Event" format 8.2
,SUBEVE as SUBEVE label ="Sub Event" format 10.
,VISIT as VISIT label ="Visit" format 10.
,QUALIFYV as QUALIFYV label ="Qualifying Value" format $70.
,QUALIFYQ as QUALIFYQ label ="Qualifying Question" format 10.
,LAB as LAB label ="Lab" format $10.
,LABSN as LABSN label ="Lab Range Subset Number" format 10.
,LABATYPE as LABATYPE label ="Lab Assignment Type Code" format
$15.
,LAB_ID as LAB_ID label ="Lab Id" format 10.
,ALLCRFSN as ALLCRFSN label ="ALL CRFs in-house and double
entered-DVN" format 10.
,ALLCRFS as ALLCRFS label ="ALL CRFs in-house and double entered"
format $15.
,CNTLABN as CNTLABN label ="Central lab data recieved and
valida-DVN" format 10.
,CNTLAB as CNTLAB label ="Central lab data recieved and
validated" format $15.
,DCFSCLN as DCFSCLN label ="All DCFs for the patient are
closed-DVN" format 10.
,DCFSCL as DCFSCL label ="All DCFs for the patient are closed"
format $15.
,VALPRCN as VALPRCN label ="All validation procedures run-DVN"
format 10.
,VALPRC as VALPRC label ="All validation procedures run" format
$15.
,MEDRVWN as MEDRVWN label ="Medical review complete-DVN" format
10.
,MEDRVW as MEDRVW label ="Medical review complete" format $15.
,SAESRCN as SAESRCN label ="SAEs reviews and reconciled-DVN"
format 10.
,SAESRC as SAESRC label ="SAEs reviews and reconciled" format
$15.
,PTEVALN as PTEVALN label ="Patient defined as evaluable-DVN"
format 10.
,PTEVAL as PTEVAL label ="Patient defined as evaluable" format
$15.
,CODINGN as CODINGN label ="AE and Med coding complete and
revie-DVN" format 10.
,CODING as CODING label ="AE and Med coding complete and
reviewed" format $15.
,DRGRCN as DRGRCN label ="Study drug reconciliation complete-DVN"
format 10.
,DRGRC as DRGRC label ="Study drug reconciliation complete"
format $15.
,AUDITN as AUDITN label ="Data entry audit complete-DVN" format
10.
,AUDIT as AUDIT label ="Data entry audit complete" format $15.
,PTCLEANN as PTCLEANN label ="Patient Clean-DVN" format 10.
,PTCLEAN as PTCLEAN label ="Patient Clean" format $15.
,DTSCRN as DTSCRN label ="Screening Date" format $8.
,DTRAND as DTRAND label ="Randomization Date" format $8.
,DTTERM as DTTERM label ="Termination Date" format $8.
from connection to oracle (select
STUDY STUDY,
DCMNAME DCMNAME,
DCMSUBNM DCMSUBNM,
SUBSETSN SUBSETSN,
DOCNUM DOCNUM,
INVSITE INVSITE,
INV INV,
PT PT,
ACCESSTS ACCESSTS,
LOGINTS LOGINTS,
LSTCHGTS LSTCHGTS,
LOCKFLAG LOCKFLAG,
CPEVENT CPEVENT,
DCMDATE DCMDATE,
DCMTIME DCMTIME,
REPEATSN REPEATSN,
ACTEVENT ACTEVENT,
SUBEVENT_NUMBER SUBEVE,
VISIT_NUMBER VISIT,
QUALIFYING_VALUE QUALIFYV,
QUALIFYING_QUESTION QUALIFYQ,
LAB LAB,
LABRANGE_SUBSET_NUMBER LABSN,
LAB_ASSIGNMENT_TYPE_CODE LABATYPE,
LAB_ID LAB_ID,
ADMIN_ALL_CRFS_DVN ALLCRFSN,
ADMIN_ALL_CRFS ALLCRFS,
ADMIN_CENT_LAB_DVN CNTLABN,
ADMIN_CENT_LAB CNTLAB,
ADMIN_DCFS_CLOSED_DVN DCFSCLN,
ADMIN_DCFS_CLOSED DCFSCL,
ADMIN_VAL_PROC_DVN VALPRCN,
ADMIN_VAL_PROC VALPRC,
ADMIN_MEDICAL_REVIEW_DVN MEDRVWN,
ADMIN_MEDICAL_REVIEW MEDRVW,
ADMIN_SAES_REC_DVN SAESRCN,
ADMIN_SAES_REC SAESRC,
ADMIN_PATIENT_EVAL_DVN PTEVALN,
ADMIN_PAT_EVAL PTEVAL,
ADMIN_CODING_COMP_DVN CODINGN,
ADMIN_CODING_COMP CODING,
ADMIN_DRUG_REC_DVN DRGRCN,
ADMIN_DRUG_REC DRGRC,
ADMIN_AUDIT_COMP_DVN AUDITN,
ADMIN_AUDIT_COMP AUDIT,
ADMIN_PATIENT_CLEAN_DVN PTCLEANN,
ADMIN_PATIENT_CLEAN PTCLEAN,
ADMIN_SCREEN_DATE DTSCRN,
ADMIN_RAND_DATE DTRAND,
ADMIN_TERM_DATE DTTERM

from EPIC3$CURRENT.ADMIN_SUMMARY);
disconnect from oracle;
quit;

When I run the above program, there is no error in log and the SAS view
ADMNSUM is defined. But when I try to open it, I got the above error
message. Why is that?

Any comments are appreciated.

Helen