cancel
Showing results for 
Search instead for 
Did you mean: 

Using Coalesce with Lookup in a custom column

CDavidOrr
8 - Cloud Apps
8 - Cloud Apps

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.

1 ACCEPTED SOLUTION

harikm007
13 - Data Warehouse
13 - Data Warehouse

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

View solution in original post

3 REPLIES 3

harikm007
13 - Data Warehouse
13 - Data Warehouse

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

Hi Hari,

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

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.