Re: Help with compilation error

"Gigi Lipori" <pflugg_at_bellsouth.net> wrote in message news:<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
> 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)
>
> AND DOCUMENTS.DOC_ID = 42957 /* Replace with p_doc_id when it really
> becomes a function */
>
> UNION SELECT
> 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;
> /

Hi

Yea, I see two obvios errors:

First, when you assign a value to a variable in PL/SQL you have to use :=
EG: dummy_exam_text := dummy_exam_text || ", ";

Secondly Am if then has to end with an end if, just end won't do. (In PL/SQL it is IF ... THEN ... ELSIF ... ELSE ... END IF, The ELSIF I don't know from any where else)

Regads
Odd Morten Received on Sat Oct 12 2002 - 23:53:28 CEST