Using Coalesce with Lookup in a custom column

[CDavidOrr]

I need to create a custom column on one of my tables.  This column will be a LOOKUP from another table.  However, if the current row has no match in the remote table, I want to use the value from the current match column.  Here is what I am attempting to use:

COALESCE(Lookup([AccMapping], [AccessorialName], [EdiCode], [Code]), [EdiCode])

So if the Lookup returns a NULL, I want EdiCode to be used.

However, this is generating a parsing error "Unhandled case label: LookupRemoteTableExpression".

I would appreciate any suggestions on how to accomplish this.

[harikm007 · Accepted solution]

Hi @CDavidOrr ,

Try 'case' statement as below:

case when Lookup([AccMapping], [AccessorialName], [EdiCode], [Code]) = null
then [EdiCode]
else Lookup([AccMapping], [AccessorialName], [EdiCode], [Code])
end

-Hari

[CDavidOrr]

Hi Hari,

Yes, that worked.  Thank you.  Would like to know what it was about the COALESCE that it didn't like though.

[javierecfpn]

Because COALESCE does not handle the use of a built-in function as lookup, COALESCE as far as I know only works with singular values from table columns, so case is needed in this situation.