Re: PL/SQL conditions being ignored (query for a children's hospital)

Maybe something like

IF( ((instr(rpt, 'CERVICAL') > 0 ) and (instr( rpt, 'FRACTURE', instr(rpt, 'CERVICAL') > 0) ) or

      ((instr(rpt, 'CERVICAL') > 0) and (instr(rpt, 'SUBLUXATION', instr(rpt, 'CERVICAL') > 0) ) , ...

I may not have the (s right, but you get the idea.

"Brad Campbell" <lx29000_at_yahoo.com> wrote in message news:743366d0.0203290925.1783a159_at_posting.google.com...
> I'm a struggling query writer who's trying to figure out how to do a
> word search on long data type. The query executes without error.
> However, it doesn't recognize my nested if that's supposed to further
> limit which ID's are to be inserted into a temp table. For example, I
> don't want it to insert into that table any ID's where the text
> contains the string 'CODE%N' (where % is a wildcard). But it does get
> inserted despite the nested if that has a not condition for this
> (among others). Any help would be greatly appreciated. Thanks. Brad
> C.
>
> Below is my code:
>
> --------
> TRUNCATE TABLE research_list;
> DECLARE
> rpt varchar2(32000);
> crlf VARCHAR2(2);
> cursor csr is
> select ...; --statement not shown to avoid vendor hassle
> begin
> for prc in csr loop
> select result_text.long_result_value into rpt from result_text
> where result_text.result_addendum_id = prc.result_addendum_id;
> crlf := CHR(13)||CHR(10);
> rpt := upper(REPLACE(rpt,crlf, NULL));
> IF(rpt LIKE '%CERVICAL%FRACTURE%' or rpt LIKE '%CERVICAL%SUBLUXATION%'
> or rpt LIKE '%ATLANTO%OCCIPITAL DISSOCIATION%' or instr(rpt,'CHANCE
> FRACTURE')>0 or instr(rpt,'PELVIC FRACTURE')>0 or instr(rpt,'CHILD
> ABUSE')>0 or instr(rpt,'SHAKEN BABY')>0) THEN
> IF((instr(rpt,'NORMAL',-1) = 0) OR (instr(rpt,'NO EVIDENCE',-1) = 0)
> OR (rpt NOT LIKE '%CODE%N%') OR (instr(rpt,'NO FRACTURE',-1) = 0))
> then
> insert into research_list(result_Addendum_id, result_value)
> values (prc.result_addendum_id, rpt);
> end if;
> end if;
> end loop;
> end;
> ---------------
Received on Fri Mar 29 2002 - 19:31:42 CET