Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SQL function adopt it into qlikview. Need help!

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;

1 Reply
stephencredmond
Partner - Specialist II
Partner - Specialist II

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