Add a column with nested source_lookup

[RonFusionHSLLC]

In Replication, I need to synthesize a column from a nested source_lookup function...doesnt seem to work; or did I do it wrong...?

Trying to add a column to a table called AccountDetails, and fill it from a lookup function. 

The table has a column called AccountID (fk to Account.ID)

The table Account, has a column called OrgID (fk to Orgs.ID)

The table Orgs has a value AccountNumPfx, I want populate the value of a new column with AccountNumPfx

Something like this...

The inner source_lookup picks up the ID field from Orgs.ID using $AccountID from the current table, passes that back to the outer source_lookup, which returns the AccountNumPf

source_lookup(10000,'dbo','Orgs','[AccountNumPfx]','[ID]=?',source_lookup(10000,'dbo','Account','[OrgID]','[ID]=?',$AccountID))

Ron

[Heinvandenheuvel · Accepted solution]

No can do. No nesting. And you really don't want to do this anyway if performance is of any concern. You Don't want to send a singleton select  to the source DB twice when conceptionally one singleton joined lookup will do.

Documentation (12 Customizing tasks, Data Enrichment input parameters)  states  for  'EXP' : "Note: The expression syntax must be native to the endpoint it accesses.". Well, source_lookup is native to Replicate, not to the DB.

Your best, cleanest, bet is a VIEW on the source to join Orgs and Account

*untested* You might try to create the join in table/expression arguments by specifying 'Orgs, Account' for TBL and  something like  'Orgs.ID = Account.OrigID AND Account.ID = ?' for EXP.

hth,

Hein

[RonFusionHSLLC]

I see.  Good to know, totally makes sense.

A follow up question...

Can Replicate call a sp or function?

[Heinvandenheuvel]

>> Good to know, totally makes sense.

The query arguments in the source_lookup statement are used to create a SQl statement using simple text replacement. Like a sprintf.

Replicate simply creates  "SELECT EXP FROM CHM.TBL WHERE COND;"

You can verify that with with verbose logging (just use a 1 table, 1 row test case) as well as in the database SQL cache.

>>Can Replicate call a sp or function?

Yes - it indicates EXP for Expression.

Hein.

[RonFusionHSLLC]

Nice...thanks for the tip