Help with compilation error
Date: Fri, 11 Oct 2002 13:06:52 -0400
Message-ID: <3da703e6$1_1_at_corp-news.newsgroups.com>
The code below gives a compilation error. The select works if I extract it
and run it independently, and the logic works when it's done in VB (pretty
sad...develop in VB and move into Oracle...am admittedly pl/sql novice).
Assume the error is in the code starting at the begin....Appreciate any
help...
CREATE OR REPLACE FUNCTION sth_liver_physical_exam (p_doc_id IN
NUMBER) return varchar as cursor c1 is
SELECT
AND DOCUMENTS.DOC_ID = 42957 /* Replace with p_doc_id when it really
becomes a function */
UNION SELECT
T1.USER_FIELD_DEFINITION_COMMENT AS EXAM_TYPE,
MAX(T1.USER_FIELD_VALUE) AS EXAM_TEXT,
MAX(T1.USER_FIELD_ID) AS FIELD_ID
FROM
(SELECT
USER_FIELD_DEFINITION_COMMENT,
USER_FIELD_VALUE,
USER_TABLE_FIELD_VALUES.USER_FIELD_ID
FROM
DOCUMENTS,
USER_TABLE_FIELD_VALUES,
USER_FIELD_DEFINITIONS
WHERE
DOCUMENTS.DOC_KIND_ID = 10076
AND DOCUMENTS.PAT_ID = USER_TABLE_FIELD_VALUES.PAT_ID
AND DOCUMENTS.DOC_ID = USER_TABLE_FIELD_VALUES.DOC_ID
AND USER_FIELD_DEFINITIONS.USER_FIELD_ID (+) =
USER_TABLE_FIELD_VALUES.USER_FIELD_ID
AND USER_FIELD_DEFINITIONS.USER_FIELD_ID IN (4478, 4479, 4480, 4481,
4482, 4483, 4484, 4671,
4672, 4673,
4674, 4853,
4854, 4855,
4856, 4858,
4859, 4860,
4857, 4861,
4862)
USER_FIELD_DEFINITION_COMMENT,
NULL AS USER_FIELD_VALUE,
USER_FIELD_ID
FROM
USER_FIELD_DEFINITIONS
WHERE
USER_FIELD_DEFINITIONS.USER_FIELD_ID IN (4478, 4479, 4480, 4481,
4482, 4483, 4484, 4671,
4672, 4673, 4674,
4853,
4854, 4855, 4856,
4858,
4859, 4860, 4857,
4861,
4862)) T1
GROUP BY
USER_FIELD_DEFINITION_COMMENT;
dummy_examtype varchar2(4000); dummy_examtext varchar2(4000); dummy_exam varchar2(4000);
begin
for rs in c1
loop
if c1.field_id < 4800 then
if dummy_exam_text <> "" then if dummy_exam <> "" then dummy_exam = dummy_exam || "; "; end; dummy_exam = dummy_exam || " " || dummy_exam_type || ": " || dummy_exam_text; dummy_exam_text = ""; end; dummy_exam_type = c1.exam_type; end; if dummy_exam_text <> "" then dummy_exam_text = dummy_exam_text || ", "; endif; dummy_exam_text = dummy_exam_text || " " || c1.exam_text;end loop;
close c1;
return dummy_exam;
end;
/ Received on Fri Oct 11 2002 - 19:06:52 CEST