Null data in resultset? Use an Alias

Consider a Scribe job that reads from CRM 4.0, and joins two entities (in this example we’re joining Activity to Contact)

I want to retrieve all the columns from the activity records, along with some columns from the Contact (in this example, the Owner of the Contact).

The source query would look something like this:

Now, I want to retrieve all fields from the activity record.

From the contact record, I want just the name of the owner.

So the Fields tab looks like this:

Easy. What could possibly go wrong?

Now I ‘test’ the DTS and the results aren’t what I’m expecting:

How can the ownerid of the contact be null?

The activity must be linked to a contact (because I ticked the ‘Required (inner join)’ box on the screenshot above), and every contact has an owner. Moreover, if I open the contact record in CRM, then I can see the owner.

The problem is because the activity record is also returning an ownerid field, and Scribe doesn’t like resultsets with duplicate column names. A similar issue occurs with SQL as the source (perhaps a topic for a later post).

How to fix it?

You need to specify an alias to make the column name unique:

And now when I test the DTS, the field is populated:

Advertisements

0 Responses to “Null data in resultset? Use an Alias”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s




Scribe Certified Partner
Scribe MVP
Advertisements