1 Reply Latest reply: Feb 15, 2012 11:26 AM by Stephen Redmond RSS

    SQL function adopt it into qlikview. Need help!

    silviu andries

      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!
          Stephen Redmond

          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