topic oracle functions in qlikview in QlikView

oracle functions in qlikview

Tue, 14 Feb 2012 17:00:38 GMT

Hello,

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

Thank you,

Silviu.

oracle functions in qlikview

fernando_tonial — Tue, 14 Feb 2012 17:40:41 GMT

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

Tue, 14 Feb 2012 18:01:23 GMT

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

CELAMBARASAN — Wed, 15 Feb 2012 04:35:16 GMT

Hi,

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

Celambarasan

oracle functions in qlikview

jagan — Wed, 15 Feb 2012 05:00:00 GMT

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

CELAMBARASAN — Wed, 15 Feb 2012 05:08:17 GMT

Hi,

You cann't use the oracle functions as macro.

Celambarasan

oracle functions in qlikview

Wed, 15 Feb 2012 08:29:24 GMT

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

rajeshvaswani77 — Wed, 15 Feb 2012 11:18:02 GMT

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

rajeshvaswani77 — Wed, 15 Feb 2012 11:18:20 GMT

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

oracle functions in qlikview

Wed, 15 Feb 2012 11:43:25 GMT

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.

Re: oracle functions in qlikview

fernando_tonial — Wed, 15 Feb 2012 11:45:13 GMT

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

oracle functions in qlikview

Wed, 15 Feb 2012 12:10:42 GMT

Thank you Fernando.

you're answer shines me:)

but I have some questions if you don't matter...

If I would chose the seccond sollution:

POLICY_ANNEX_CHANGED:

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

-what do you mean "from Dual"?

-my function is on the server, and it has to be called to return Y/N into the ANNEX_CHANGED field, for each POLICY_ID.

Thank you again,

Silviu.

Re: oracle functions in qlikview

fernando_tonial — Wed, 15 Feb 2012 12:23:44 GMT

OK,

The Dual table is widely used to select operations where there is no need to extract data in tables.

You will make a loop for each value of policy, and execute your function in the databasethrough the SQL Select command.

oracle functions in qlikview

Wed, 15 Feb 2012 12:53:25 GMT

So dual means that the path must be from 2 sides.

POLICY_ID will be from the previous loaded table ("policy") and ANNEX_CHANGED must be populated with the answers that my function IS_ANNEX_CHANGED() will retrieve. The function is on a database server, so the path will be different from "policy".

Thank you.

oracle functions in qlikview

Wed, 15 Feb 2012 12:56:12 GMT

how can this 2 different paths be called in the SELECT...FROM clause?...one for POLICY_ID and one for ANNEX_CHANGED

oracle functions in qlikview

Wed, 15 Feb 2012 13:41:27 GMT

I tried to use you're example, but I have received the following error:

SQL##f - SqlState: S0002, ErrorCode: 942, ErrorMsg: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist

Are you sure that I can call a oracle function and save the returned answer into my ANNEX_CHANGED field?

Thanx,

Silviu.

Re: oracle functions in qlikview

fernando_tonial — Wed, 15 Feb 2012 17:09:06 GMT

You can try this, Logon in your database and execute the SQL

Select IS_ANNEX_CHANGED (NumberPolicyID) as ANNEX_CHANGED from Dual;

where the NumberPolicyID is a valida Policy_id;