Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have a problem that must be solved urgently because it is applied on many tables, and the start is the hardest...
What I would like to do:
i have imported a table from a qvd file, and after that I would like to add a field (flag field) at the end of the table and to populate it with 1 or 0, regarding to my ORACLE function.
Is there a way that I can execute the oracle function directly for each line from my table, and to put the result into the flag field?
Each answer is precious.
Thank you,
Silviu.
By any chance your qvd is populated from oracle?
yes it is.
the oracle function that i would like to use calculates if a policy is changed or not and had been applied to the Oracle database.
the function is something like:
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 ..................
etc...
the function returns Y for policies that are changed and N for others.
I would like to make execute the function into the load statement and to add a flag field that will contain the Y or N for each policy.
Why dont you use a store procedure to populate a new table in oracle and qlikview extracts from there?
Can you please detaliate what store procedure involves on my problem?
Thank you.
I'm saying that maybe the best idea is to resolve your problem in the oracle bundle and only use qlikview to retrieve the modified information.
Create a store procedure in oracle that uses your function and populates a new table/view. Then only retireve from qv the info of the new table/view.
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;
---------------------------------------------------------------------
Loaded table in qlikview:
policy:
LOAD POLICY_ID,
INSR_TYPE,
OFFICE_ID,
CLIENT_ID,
AGENT_ID,
INSR_BEGIN,
INSR_END
...................
FROM
[...\policy.qvd]
(qvd);
---------------------------------------------------------------------------
What I have made so far is:
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 'path';
NEXT P ;
After entering the function path, I receive the following error:
SQL##f - SqlState: S0002, ErrorCode: 942, ErrorMsg: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist
Can someone give me any idea of what to do from here?
Thak you,
Silviu.