Clarity

  • Private Community
 View Only

  • 1.  Upgrade to 15.3 error

    Posted Dec 16, 2017 02:18 AM

    I have an error when attempting to upgrade to 15.3.  The query appears to be checking for "invalid rows", but gives an error that there are more than 1 row for a subquery.

    My question is:

    1: What exactly is this query trying to return?

    2: How do I find the bad data?

     

    When I split out the central piece of the query (in red), I get 4,500 rows before it gives the subquery contains more than 1 row error.

     

    D:\Install\15.3\checkinstall\scripts\check-matrix-invalid-rows.xml:36: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01427: single-row subquery returns more than one row

     

     

    12/15/17 11:01 AM (SQLTask) Failed to execute:  SELECT abc.description matdescription, matrix_data.FROMDATE,matrix_data.TODATE,matrix_data.MATRIXKEY matrixkey, matrix_data.RESOURCE_CODE resourcecode FROM ( SELECT matrixcodekeys.MATRIXKEY,FROMDATE,TODATE, CASE WHEN matrixcodekeys.resource_code = 1 THEN VALUE1 WHEN matrixcodekeys.resource_code = 2 THEN VALUE2 WHEN matrixcodekeys.resource_code = 3 THEN VALUE3 WHEN matrixcodekeys.resource_code = 4 THEN VALUE4 WHEN matrixcodekeys.resource_code = 5 THEN VALUE5 WHEN matrixcodekeys.resource_code = 6 THEN VALUE6 WHEN matrixcodekeys.resource_code = 7 THEN VALUE7 WHEN matrixcodekeys.resource_code = 8 THEN VALUE8 WHEN matrixcodekeys.resource_code = 9 THEN VALUE9 WHEN matrixcodekeys.resource_code = 10 THEN VALUE10 END resource_code FROM ( SELECT matrixkeys.MATRIXKEY, (SELECT def.COLUMNNO FROM PPA_MATRIXCOLDEF def WHERE def.MATRIXKEY = matrixkeys.MATRIXKEY AND (def.FIELDNAME = 'resource_code' ) ) resource_code FROM (SELECT MATRIXKEY FROM PPA_MATRIX WHERE MATRIXTYPE != 'G' ) matrixkeys ) matrixcodekeys, PPA_MATRIXVALUES PPAM WHERE PPAM.MATRIXKEY = matrixcodekeys.MATRIXKEY ) matrix_data, PPA_MATRIX abc where resource_code in ( select SRM.UNIQUE_NAME from SRM_RESOURCES SRM,PRJ_RESOURCES PRJ where PRJ.PRID=SRM.ID and PRJ.PRISROLE=1 ) and abc.MATRIXKEY=matrix_data.MATRIXKEY
    12/15/17 11:01 AM (UnknownElement) Task "nsql" finished with error.
    D:\Install\15.3\checkinstall\scripts\check-matrix-invalid-rows.xml:36: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01427: single-row subquery returns more than one row



  • 2.  Re: Upgrade to 15.3 error

    Broadcom Employee
    Posted Dec 17, 2017 08:51 PM

    Hi Lowell,

     

    The query (in red) seems to return Matrix Rows.

    Please review Matrix Rows by access to ADMINISTRATION->Finance->Manage Matrix->Click Matrix->Edit Matrix Rows tab.

     

    Thank you.