topic SQL function adopt it into qlikview. Need help! in QlikView

SQL function adopt it into qlikview. Need help!

Wed, 15 Feb 2012 15:44:23 GMT

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;

SQL function adopt it into qlikview. Need help!

stephencredmond — Wed, 15 Feb 2012 16:26:41 GMT

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