Clarity

  • 1.  Jasper Soft Load Data Warehouse Failing

    Posted Jul 28, 2017 05:14 AM

    Hello All,

    Greetings!!!

     

    I have a problem, as the Jasper Soft Load Data Warehouse (Daily; 4:00a CT) is failing daily.

    It is failing with an error message as below.

    I am not sure on next steps of this. Any suggestions would help a lot.

    dwh_db_check_ - An error occurred executing this job entry :  Couldn't execute SQL: BEGIN RPT_CALENDAR_SP(); RPT_INV_HIERARCHY_SP(); END; [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SC51339T.RPT_CALENDAR_SP", line 330 ORA-06512: at line 2

    I verified the bg-log and it shows thr info as follows:-

    2017/07/22 04:00:06 - dwh_db_check_ - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :
    2017/07/22 04:00:06 - dwh_db_check_ - Couldn't execute SQL: BEGIN
    2017/07/22 04:00:06 - dwh_db_check_ - RPT_CALENDAR_SP();
    2017/07/22 04:00:06 - dwh_db_check_ - RPT_INV_HIERARCHY_SP();
    2017/07/22 04:00:06 - dwh_db_check_ - END;
    2017/07/22 04:00:06 - dwh_db_check_ -
    2017/07/22 04:00:06 - dwh_db_check_ - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01422: exact fetch returns more than requested number of rows
    2017/07/22 04:00:06 - dwh_db_check_ - ORA-06512: at "SC51339T.RPT_CALENDAR_SP", line 330
    2017/07/22 04:00:06 - dwh_db_check_ - ORA-06512: at line 2
    2017/07/22 04:00:06 - dwh_db_check_ - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :
    2017/07/22 04:00:06 - dwh_db_check_ - Couldn't execute SQL: BEGIN
    2017/07/22 04:00:06 - dwh_db_check_ - RPT_CALENDAR_SP();
    2017/07/22 04:00:06 - dwh_db_check_ - RPT_INV_HIERARCHY_SP();
    2017/07/22 04:00:06 - dwh_db_check_ - END;
    2017/07/22 04:00:06 - dwh_db_check_ -
    2017/07/22 04:00:06 - dwh_db_check_ - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01422: exact fetch returns more than requested number of rows
    2017/07/22 04:00:06 - dwh_db_check_ - ORA-06512: at "SC51339T.RPT_CALENDAR_SP", line 330
    2017/07/22 04:00:06 - dwh_db_check_ - ORA-06512: at line 2
    2017/07/22 04:00:06 - dwh_db_check_ - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :
    2017/07/22 04:00:06 - dwh_db_check_ - Couldn't execute SQL: BEGIN
    2017/07/22 04:00:06 - dwh_db_check_ - RPT_CALENDAR_SP();
    2017/07/22 04:00:06 - dwh_db_check_ - RPT_INV_HIERARCHY_SP();
    2017/07/22 04:00:06 - dwh_db_check_ - END;
    2017/07/22 04:00:06 - dwh_db_check_ -
    2017/07/22 04:00:06 - dwh_db_check_ - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01422: exact fetch returns more than requested number of rows
    2017/07/22 04:00:06 - dwh_db_check_ - ORA-06512: at "SC51339T.RPT_CALENDAR_SP", line 330
    2017/07/22 04:00:06 - dwh_db_check_ - ORA-06512: at line 2

     

     

    Thanks and Regards,

    Sai.



  • 2.  Re: Jasper Soft Load Data Warehouse Failing

    Broadcom Employee
    Posted Jul 28, 2017 05:36 AM

    HI Sai,

     

    Did you run the update report tables and then ran the DWH jobs?

     

    Regards

    Suman Pramanik 



  • 3.  Re: Jasper Soft Load Data Warehouse Failing

    Posted Jul 31, 2017 07:23 AM

    Hey Suman,

    Greetings!!!!

     

    Thanks for helping me in taking  this forward.

    I am not able to include a dynamic lookup now in DWH.

    I do not have a cmn_lookups_v table present in my query to pull out language_code and language_id ,last_updated_date.

    Would you please suggest me how to include these three columns.

     

    Here is my query.

     

    select @select:solution_owner_id:solution_owner_id@,
        @select:solution_owner_name:solution_owner_name@
       
    from
    (select  nvl(sr.id ,(select sr1.id
         from srm_resources sr1
         where sr1.unique_name='TBD')) solution_owner_id,
             nvl(sr.full_name ,(select sr1.full_name
           from srm_resources sr1
           where sr1.unique_name='TBD')) solution_owner_name,
       odf.id
    from   srm_resources sr
    right outer join odf_ca_z_attribute_mapping odf on   sr.id=odf.solution_owner_1 
    where  odf.id=nvl(@WHERE:PARAM:USER_DEF:INTEGER:sol_id@,(select sr.id from srm_resources sr where sr.unique_name='TBD'))
    @BROWSE-ONLY:
    and odf.z_is_active=1
    :BROWSE-ONLY@
    union
    select sr1.id solution_owner_id,sr1.full_name solution_owner_name,0 id from srm_resources sr1 where sr1.unique_name='TBD' and @WHERE:PARAM:USER_DEF:INTEGER:sol_id@ is null)



  • 4.  Re: Jasper Soft Load Data Warehouse Failing
    Best Answer

    Broadcom Employee
    Posted Jul 31, 2017 07:32 AM

    Hi Sai,

     

    This looks to be a different problem than what you asked before, earlier your DWH was failing and now you want to include look up to be included in DWH. Here it the tip CA PPM Tech Tip: Including Dynamic Query Lookup Attributes in the Data Warehouse 

     

    However I would request you to open a different thread so that any one else going through the same problem don't get confused.

     

    Regards

    Suman Pramanik 



  • 5.  Re: Jasper Soft Load Data Warehouse Failing

    Posted Jul 31, 2017 12:31 PM

    Yeah. will do that.. I was able to get the load data ware house job completed.

    Thanks for the help.

     

    Thanks and Regards.

    Sao.



  • 6.  Re: Jasper Soft Load Data Warehouse Failing

    Broadcom Employee
    Posted Jul 31, 2017 02:52 PM

    Thanks Sai, may I request you to mark the thread as answered



  • 7.  Re: Jasper Soft Load Data Warehouse Failing

    Posted Aug 01, 2017 04:25 AM

    Sure.