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

Brad Campbell wrote:
>
> 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;
> ---------------

Logically, my dear Watson, you could have more luck with:

IF(   (instr(rpt,'NORMAL',-1) = 0)
   OR (instr(rpt,'NO EVIDENCE',-1) = 0)
   OR (instr(rpt,'NO FRACTURE',-1) = 0)

-- 

Gerard H. Pille

(The Beatles Reunion is neigh!)