oracle functions in qlikview

[#1]

Hello,

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

Thank you,

Silviu.

[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

[#3]

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.

[fernando_tonial]

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.

[#5]

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.

[#6]

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

[#7]

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.

[fernando_tonial]

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;