Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

oracle functions in qlikview

Hello,

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

Thank you,

Silviu.

16 Replies
fernando_tonial
Valued Contributor

oracle functions in qlikview

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.

Not applicable

oracle functions in qlikview

thanx.

What I wold like to do is to execute an Oracle function (like the one that you mention) in edit module script of Qlikview.

Best regards.

Silviu.

oracle functions in qlikview

Hi,

     You can use oracle functions but it possible only in the load script i.e, loading data from the database.

Celambarasan

MVP
MVP

oracle functions in qlikview

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

Hi,

     You cann't use the oracle functions as macro.

Celambarasan

Not applicable

oracle functions in qlikview

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);

rajeshvaswani77
Valued Contributor III

oracle functions in qlikview

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

rajeshvaswani77
Valued Contributor III

Re: oracle functions in qlikview

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

Not applicable

oracle functions in qlikview

Thank you!

The best way for me is a method to call this function into the qlikview load script, but I don't know how.

Can you please detaliate the 1'st point that you mention earlier?

Best regards,

Silviu.

Community Browser