I’m using @DbLookup for ODBC as follows:
@DbLookup( “ODBC” :“NoCache”; “DbName” ; “User” ; “pwd” ; “DB_USR.TABLE” ; “RETURN_COL”:“Discard” ; “KEY_COL” ; keyFld)
keyFld is a multi-value virtual field returned from an LEI Activity.
My DbLookup is only returning one value for each DISTINCT value in keyFld - even though I am not specifying this.
I have tried entering an empty string in the options ‘Distinct:Sort’ parameter. I’ve tried having the values in keyFld sorted and unsorted. I’ve also tried replacing nulls instead of discarding to make sure each item returned a value.
It seems the @DbLookup ALWAYS uses the distinct argument whether I’ve asked for it or not? Any ideas?
Alternatively, if any one has any success using a multi value (virtual) field as a key field for a second virtual field I could use LEI to get the value of my second field - I need the effect of a table join in SQL.
LEI doesn’t seem to like this though, if there is a single value in keyFld the second virtual activity (which was disable when testing @dbLookup) works, if it has multiple values it fails.