Re: Pragma Violation

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Mon, 8 Mar 1999 21:27:25 +0100
Message-ID: <36e432ae$0$17512@newton>

Arjan van Bentem wrote
> -- if in_batch_id1 is null, then always true:
> and ( (pft.batch_id = nvl(in_batch_id1, pft.batch_id)
> or (pft.batch_id = in_batch_id3)
> or (pft.batch_id = in_batch_id3)
> or (pft.batch_id = in_batch_id4)
> ...
> )

... or you could break up in_batch_ids yourself and loop over the query a couple of times. Like

cursor c(pBatchID as varchar2) is

    select NVL(SUM(MAINT_PROCEDURE_CPT_CD), 0) Hash     from ...
    ...
    and pft.batch_id = pBatchID
    and ...

begin

    iStart := instr(in_batch_ids, ',');     while iStart > 0 loop

        iEnd := ...
        vBatch := rtrim(ltrim(
            substr(in_batch_ids, 1, iStart, iEnd - iStart +1))
            );
        if vBatch is not null then
            for r in c(vBatch)
            loop -- in fact, only "looped" once for each vBatch
                vHash := vHash + r.Hash;
            end loop;
        end if;
        in_batch_ids := substr( ... );
        iStart := instr(in_batch_ids, ',');

    end loop;
    return ...
end;

Arjan. Received on Mon Mar 08 1999 - 14:27:25 CST