Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have an Oracle function that returns if a policy has been changed or not.
How can I addopt this function to qlikview?
each information helps me...
FUNCTION IS_ANNEX_CHANGED (p_policy_id number) return varchar is
cursor c1 is select count(*)
from
(
select cfg.fract_type
from gen_risk_covered grc, cfg_gl_gen_fractions cfg
where grc.cover_type=cfg.cover_type
and grc.insr_type=cfg.insr_type
and risk_state between 0 and 12
and grc.annex_id<>0
and policy_id=p_policy_id
minus
select cfg.fract_type
from gen_risk_covered grc, cfg_gl_gen_fractions cfg
where grc.cover_type=cfg.cover_type
and grc.insr_type=cfg.insr_type
--and risk_state between 0 and 12
and grc.annex_id=0
and policy_id=p_policy_id
);
nr number;
begin
open c1;
fetch c1 into nr;
close c1;
if nvl(nr,0)>0 then
return 'Y';
else return 'N';
end if;
end;
Hi,
I would want to be able to identify a primary key - e.g. a Policy number. Then I could write something like:
Pol1:
Load
policy_id as PolicyID;
SQL select policy_id
from gen_risk_covered grc, cfg_gl_gen_fractions cfg
where grc.cover_type=cfg.cover_type
and grc.insr_type=cfg.insr_type
and risk_state between 0 and 12
and grc.annex_id<>0
and policy_id=p_policy_id;
Pol2:
Load
policy_id as PolicyID2
Where Exists (PoilicyID1, policy_id);
SQL select policy_id
from gen_risk_covered grc, cfg_gl_gen_fractions cfg
where grc.cover_type=cfg.cover_type
and grc.insr_type=cfg.insr_type
and grc.annex_id=0
and policy_id=p_policy_id;
Then, if you have anything in the PolicyID2 field (use FieldValueCount), you know the policy number.
Or something like that 😉
Regards,
Stephen