Skip to main content

Hi.

We’re hoping to create an OnServer CREATE_OUTPUT_FILE type migration job that would return all active employees in our organization, including the address data for their listed company work locations. Work location data already exists in the table, but the address data for the work location doesn’t. This is a copy of an older migration job that returns other employee results, and based on that I’ve created a SELECT statement in the File Mapping tab as follows: 

(SELECT ADDRESS FROM IFSAPP.PERS_COMMS4 WHERE COMPANY_ID = IFSAPP.COMPANY_PERSON.COMPANY_ID AND PERSON_ID = IFSAPP.COMPANY_PERSON.PERSON_ID)

 

With this query, the job runs but returns only partial results and the following error:

 ORA-01427: single-row subquery returns more than one row

 

As I understand it the Address field is a concatenation of multiple Address fields (Address1, Address2 etc). Is there a viable workaround this issue due to the nature of the Address field?
I’ve also attempted a JOIN clause in the Database Information tab of the job with no results.

 

Any help would be appreciated.

Thank you!

 

 

Hi,

The subquery error is indicating that there are multiple matching rows in the PERS_COMMS4 view for at least one employee.  This can happen because of emails, phone numbers etc (in the comm_data field of the view).

You can restrict the number of rows returned by the subquery by adding a ROWNUM check.

Does changing your query to the following give you the information you need?

(SELECT ADDRESS FROM IFSAPP.PERS_COMMS4 WHERE COMPANY_ID = IFSAPP.COMPANY_PERSON.COMPANY_ID AND PERSON_ID = IFSAPP.COMPANY_PERSON.PERSON_ID AND VALID_TO > SYSDATE AND ROWNUM=1)

Note though that if the multiple records are not because of other contact methods and there are actually multiple different currently valid addresses then this query will randomly just return the first one.

Hope this helps!

mmck

Hi,

The subquery error is indicating that there are multiple matching rows in the PERS_COMMS4 view for at least one employee.  This can happen because of emails, phone numbers etc (in the comm_data field of the view).

You can restrict the number of rows returned by the subquery by adding a ROWNUM check.

Does changing your query to the following give you the information you need?

(SELECT ADDRESS FROM IFSAPP.PERS_COMMS4 WHERE COMPANY_ID = IFSAPP.COMPANY_PERSON.COMPANY_ID AND PERSON_ID = IFSAPP.COMPANY_PERSON.PERSON_ID AND VALID_TO > SYSDATE AND ROWNUM=1)

Note though that if the multiple records are not because of other contact methods and there are actually multiple different currently valid addresses then this query will randomly just return the first one.

Hope this helps!

mmck

Hi.

Based on initial tests, replacing the old Address query with the above seems to be returning the correct amount of fields.

 

Thank you!

Reply


Terms & ConditionsCookie settings