6 Replies Latest reply: Feb 15, 2012 8:57 AM by silviu andries RSS

    add some fields and populate them using a function. please help

    silviu andries

      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.

        • add some fields and populate them using a function. please help

          By any chance your qvd is populated from oracle?

            • add some fields and populate them using a function. please help
              silviu andries

              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.

                • add some fields and populate them using a function. please help

                  Why dont you  use a store procedure to populate a new table in oracle and qlikview extracts from there?

                    • add some fields and populate them using a function. please help
                      silviu andries

                      Can you please detaliate what store procedure involves on my problem?

                       

                      Thank you.

                        • add some fields and populate them using a function. please help

                          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.

                            • add some fields and populate them using a function. please help
                              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;

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

                               

                              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.