Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Kamran Butt
    Custom BI Publisher Report SQL ErrorAnswered
    Topic posted September 16, 2019 by Kamran ButtRed Ribbon: 250+ Points, tagged BI Publisher, Fusion Financial reporting, OBI Answers, OBIEE Answers, OTBI, Public Sector, Reports 
    118 Views, 14 Comments
    Title:
    Custom BI Publisher Report SQL Error
    Summary:
    SQL or ORA exceptions error.
    Content:

    Hello,

    We are receiving the following error when running a custom built BI Publisher report. This report was functioning without any issues 2-3 weeks prior. No changes were made, but now we are receiving this error when trying to run.

    Please see attached for data model and report.

    SQL or ORA exceptions caused due to invalid sql, database package state or some database exceptions.

    Please advise on how best to debug. Thanks!

    Version:
    Oracle Cloud 19C (11.13.19.07.0)
    Image:

    Best Comment

    Tom Strudley

    Hi Kamran

    What are you trying to get from the inner query? Just emp name?

    If so try (SELECT PPNF.display_name from per_person_names_f ppnf where ppnf.person_id = prh.preparer_id AND PPNF.name_type = 'GLOBAL' AND SYSDATE BETWEEN PPNF.effective_start_date and PPNF.effective_end_date)

    Try to avoid DISTINCT where possible unless you REALLY need it, it can mask things

    Thanks

    Tom

      

    Comment

     

    • Prateek Parasar

      Whats the Datamodel based on , I assume SQL, open the SQL and verify all the SQLs are still valid and there are no logical issues such as subquery returning more than one row.

      • Kamran Butt

        Yes, the DM is based on SQL. I have this same DM deployed in our DEV instance without any issues, but I only receive an error in our Prod environment. I have checked with Oracle and both our DEV and Prod environments are identical. I will review the SQLs but I do not think any of the sub-queries are returning more than one row.

    • Senthilrajan Vaithianathan

      Hi Kamran,

      Looks like there is some issue in this report template, try below steps and see it works...

      Try to create a simple report with few fields in the data model in Production environment and see if it works to eliminate issues in the Data Model.

      If that works try to restore a back of the report from previous versions if it is available, else recreate the report and migrate it to Production. And while migrating to Prod you first edit the report and point it to right data model before up open it up.

      Senthil

      • Kamran Butt

        Hi Senthil,

        I broke apart the three large union queries and ran them separately, the third query runs without issues, but the first and second queries throw the ORA-01427 error. I am in the process of commenting out individual sub-queries and trying to find the problematic portion. Will keep you posted on results.

        My concern is, if the error doesn't happen in our lower environments but it does in Production, how are we supposed to catch these types of issues in the future? Also, there is no clear reason for this error to occur in Production - the lower environments are recent clones of Production.

        -Kamran

    • Tom Strudley

      Hi Kamran

      Is you SQL query complex? It could be that you have an inner query that is bringing back duplicates that your query cannot handle based upon a new scenario that hadn't come across during your test cycles

      I am unable to unarchive your data model, if you could archive again it could be checked

      First step would be to migrate the exact data model into your support environment (hopefully this is a clone of production with a similar/same data set). It is works but still doesn't work in your production it may be the issue I have explained

      Thanks

      Tom

      • Amar

        Tom-> For Archive issue, You need to upload the *.xdoz &*.xdmz from /xmlpserver

        • Senthilrajan Vaithianathan

          Hi Amar,

          Did you check my comments? Your Data model works fine (see attached screen shot), I am able to retrieve data from it, check the report definition...

          -Senthil

          • Kamran Butt

            Hi Senthil,

            This is exactly what I am noticing as well. When I download the data model and report from DEV and upload the objects to PROD, I get the above SQL error (ORA-01427: single-row subquery returns more than one row). In DEV there are no issues, but in PROD it throws an error. I am now debugging the sub-queries line by line to find the problematic portion.

            Will keep you posted on results.

            -Kamran

            • Prateek Parasar

              I always suggest to check sub query for multiple rows as only difference another environment generally has is the data.

               

      • Kamran Butt

        Hi Tom,

        The SQL query isn't overly complex, but it isn't simple either. Generally, the whole query is three larger queries union-ed together, with sub-queries in each of those larger queries. Hope that makes sense.

        Were you able to upload the data model/report as Amar mentioned below? I downloaded them straight from the /xmlpserver website. Our Support environment runs the report just fine, it is an identical copy of Production. The issue is only in Prod.

        -Kamran

    • Kamran Butt

      Hi all,

      I broke apart the three larger queries that are union-ed together and ran them individually. The third query shows no issues, but the first and second ones result in the error: "ORA-01427: single-row subquery returns more than one row".

      I further checked each sub-query and found the culprit to be:
      (select distinct name from per_employees_current_x pex , per_departments pd where pex.organization_id = pd.organization_id and pex.person_id = prh.preparer_id )

      This query is in both the first and second larger queries of the three query union. When running the above query separately in DEV and PROD, I receive no errors, but when combined together with everything else in PROD I receive an error.

      I am going to continue debugging and provide you with updates. Any assistance or hints in the matter would be helpful.

      -Kamran

      • Tom Strudley

        Hi Kamran

        What are you trying to get from the inner query? Just emp name?

        If so try (SELECT PPNF.display_name from per_person_names_f ppnf where ppnf.person_id = prh.preparer_id AND PPNF.name_type = 'GLOBAL' AND SYSDATE BETWEEN PPNF.effective_start_date and PPNF.effective_end_date)

        Try to avoid DISTINCT where possible unless you REALLY need it, it can mask things

        Thanks

        Tom

          

        • Kamran Butt

          Hi Tom,

          Thanks for the response! I made a similar update actually. Changed the query to the following and it worked: 

          (select distinct name from per_employees_current_x pex , per_departments pd  where pex.organization_id = pd.organization_id and pex.person_id = prh.preparer_id and sysdate between pd.effective_start_date and pd.effective_end_date )

          Thanks for everyone's help and input!

          -Kamran