16 Replies Latest reply: Feb 15, 2012 12:09 PM by Fernando Tonial RSS

    oracle functions in qlikview

    silviu andries

      Hello,

      Is there a way to use a oracle function with in and out parameters?

       

      Thank you,

      Silviu.

        • oracle functions in qlikview
          Fernando Tonial

          It is possible.

           

          See my function.

           

          create or replace function f_md5 (cValor varchar) return varchar2 is
          cInput varchar2(2000) := cValor;
          cHexkey varchar2(32) := null;
          begin
          cHexkey := rawtohex(dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(cInput)));
          return nvl(cHexkey,'');
          end;
          /

           

          Using the function.

           

          select f_md5('senha') as MD5 from dual;

           

          Best Regards.

          Tonial.

          • oracle functions in qlikview
            jagan mohan rao appala

            Hi,

             

            Oracle functions can be used in the qlikview in the Load script by using the Select queries.  Can you tell what function you need to use it in Edit Module. 

             

            Regards,

            Jagan.

              • oracle functions in qlikview
                silviu andries

                Hello.

                 

                My function returns if a policy is changed or not.

                 

                What i would like to do is to load the POLICY table, to apply the function into the load script, and to add one field at the end of the table that will contain Y/N(changed/not changed) for each row(each policy ID).

                 

                Oracle function:

                 

                 

                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;

                ---------------------------------------------------------------------

                 

                policy:

                LOAD POLICY_ID,

                     INSR_TYPE,

                     OFFICE_ID,

                     CLIENT_ID,

                     AGENT_ID,

                     INSR_BEGIN,

                     INSR_END

                    ...................

                FROM

                [...\policy.qvd]

                (qvd);

                  • oracle functions in qlikview
                    Rajesh Vaswani

                    There are multiple ways you could do this in.

                     

                    1) Oracle Functions or any normal oracle database queries can be called in the Qlikview level by preceding with SQL.

                     

                    2)Convert this logic in to Set analysis and no need to use the oracle function then.

                     

                    3)Have this value readily stored in the QVD where you would use the part of the function logic statements and load them in to policy.qvd

                     

                    Thanks,

                    Rajesh Vaswani

                    • Re: oracle functions in qlikview
                      Rajesh Vaswani

                      There are multiple ways you could do this in.

                       

                      1) Oracle Functions or any normal oracle database queries can be called in the Qlikview level by preceding with SQL.

                       

                      2)Convert this logic in to Set analysis and no need to use the oracle function then.

                       

                      3)Have this value readily stored in the QVD where you would use the part of the function logic statements and load them in to policy.qvd

                       

                      Thanks,

                      Rajesh Vaswani

                      • Re: oracle functions in qlikview
                        Fernando Tonial
                        You can do one of two ways.
                        1) If you have the policy table in your database you can do a SQL.
                        POLICY_ANNEX_CHANGED:
                        SQL Select
                        POLICY_ID,
                        IS_ANNEX_CHANGED (
                        $(vPolicyID) ) as ANNEX_CHANGED
                        from Dual;
                        2) If you don´t have a policy table in your database.
                        Making a loop to get information for each policy.

                        POLICY:
                        LOAD
                        POLICY_ID,
                        INSR_TYPE,
                        OFFICE_ID,
                        CLIENT_ID,
                        AGENT_ID,
                        INSR_BEGIN,
                        INSR_END
                        FROM [...\policy.qvd] (qvd);

                        FOR P =0 to NoOfRows('POLICY')-1;

                        LET vPolicyID = Peek('POLICY_ID',$(P));

                        POLICY_ANNEX_CHANGED:
                        LOAD
                        '$(vPolicyID)'
                        as POLICY_ID,
                        ANNEX_CHANGED;
                        SQL Select IS_ANNEX_CHANGED ($(vPolicyID)) as ANNEX_CHANGED from Dual;

                        NEXT P