AnsweredAssumed Answered

Upgrade to 15.3 error

Question asked by Lowell on Dec 16, 2017
Latest reply on Dec 18, 2017 by Shoichi_Sugawara

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

Outcomes