I am trying to create a custom sql lookup enricher but it does not seem to be working. Another small thing I have set a separate fallback value but it is not working either. Any advice would be great
Lets say I have two tables
Dogs and DogTypes. Dogs is a table to store data about a specific dog, dog types is a canonical list of values for known/ allowable dog types.
I want to lookup the DogTypeID based on InputDogType. Return the value ID if there is a match, return the ID 4 for 'Case Insensitive Match' if there is a match once both values are lowered and then return the ID 5 for 'Unknown' if there is no match.
So for my input data in order for FID I want to return - 1,4,5
My SQL currently looks like this:
COALESCE(
(SELECT DOG_TYPE_NAME AS LOOKUP_COLUMN, DOG_TYPE_ID AS OUTPUT_COLUMN1 FROM GD_DOG_TYPE WHERE INPUT_DOG_TYPE_NAME = DOG_TYPE_NAME),
(SELECT DOG_TYPE_NAME AS LOOKUP_COLUMN, DOG_TYPE_ID AS OUTPUT_COLUMN1 FROM GD_DOG_TYPE WHERE DOG_TYPE_NAME = 'Case Insensitive Match' AND EXISTS (SELECT 1 FROM GD_DOG_TYPE WHERE LOWER(DOG_TYPE_NAME) = LOWER(INPUT_DOG_TYPE_NAME))),
(SELECT DOG_TYPE_NAME AS LOOKUP_COLUMN, DOG_TYPE_ID AS OUTPUT_COLUMN1 FROM GD_DOG_TYPE WHERE DOG_TYPE_NAME = 'Unknown')
)
I have seen other examples on the forum where is has been suggested to use COALESCE in conjunction with the look up, however I am not sure about the parts where I am referencing the input value in the custom SQL as I have not seen examples on that neither on the forum or in the documentation.
1 Comment
M
Matthieu DE PILLOT DE COLIGNY
said
2 months ago
Hello
The difficult part of your need is that there isn't a native way to have a fuzzy lookup, so if you want to both look for exact matches, and approximate matches (case insensitive matches for example), I don't think it is doable in one enricher. The design I will show below uses two lookup enrichers, one after the other.
The first enricher uses
- customSQL parameter : SELECT DOG_TYPE_NAME AS LOOKUP_COLUMN, DOG_TYPE_ID AS OUTPUT_COLUMN1 FROM GD_DOG_TYPE
- fallback behavior parameter : use_fallback (which is the default one, so no need to specify it)
- fallback value : NULL (which also is the default value)
That enricher will only enrich the exact matches, and for the other set the FID to null
The second enricher has a filter, and that filter is "FID is NULL", that way only the records that have yet to be enriched will be considered, and the ones enriched by the previous enricher will be ignored. The second enricher uses
- customSQL parameter : SELECT lower(DOG_TYPE_NAME) AS LOOKUP_COLUMN, 4 AS OUTPUT_COLUMN1 FROM GD_DOG_TYPE
- fallback behavior parameter : use_fallback (which is the default one, so no need to specify it)
- fallback value : 5
Please remember to use lower(INPUT_DOG_TYPE_NAME) as an input
This second enricher will put the value 4 if any case insensitive match is found, and 5 if none was found.
Max. bonnell
Hi,
I am trying to create a custom sql lookup enricher but it does not seem to be working. Another small thing I have set a separate fallback value but it is not working either. Any advice would be great
Lets say I have two tables
Dogs and DogTypes. Dogs is a table to store data about a specific dog, dog types is a canonical list of values for known/ allowable dog types.
e.g.
Input Data for Dogs
{'DogID': 1, 'DogName' : 'Buddy', 'Age': 3, 'Owner': 'Ruth', 'InputDogTypeName': 'Labrador', 'FID_DOGTYPE': <TO BE ENRICHED>},
{'DogID': 2, 'DogName' : 'Scooby-Doo', 'Age': 10, 'Owner': 'Shaggy', 'InputDogTypeName': 'GREAT DANE', 'FID_DOGTYPE': <TO BE ENRICHED> },
{'DogID': 3, 'DogName' : 'Lassie', 'Age': 5, 'Owner': 'Helen', 'InputDogTypeName': 'bahabhabhb', 'FID_DOGTYPE': <TO BE ENRICHED> }
]}
{'DogType': [
{'DogTypeID': 1, 'DogTypeName': 'Labrador'},
{'DogTypeID': 2, 'DogTypeName': 'Great Dane'},
{'DogTypeID': 3, 'DogTypeName': 'Collie'},
{'DogTypeID': 4, 'DogTypeName': 'Case Insensitive Match'},
{'DogTypeID': 5, 'DogTypeName': 'Unknown'}
]}
I want to lookup the DogTypeID based on InputDogType. Return the value ID if there is a match, return the ID 4 for 'Case Insensitive Match' if there is a match once both values are lowered and then return the ID 5 for 'Unknown' if there is no match.
My SQL currently looks like this:
COALESCE(
(SELECT DOG_TYPE_NAME AS LOOKUP_COLUMN, DOG_TYPE_ID AS OUTPUT_COLUMN1 FROM GD_DOG_TYPE WHERE INPUT_DOG_TYPE_NAME = DOG_TYPE_NAME),
(SELECT DOG_TYPE_NAME AS LOOKUP_COLUMN, DOG_TYPE_ID AS OUTPUT_COLUMN1 FROM GD_DOG_TYPE WHERE DOG_TYPE_NAME = 'Case Insensitive Match' AND EXISTS (SELECT 1 FROM GD_DOG_TYPE WHERE LOWER(DOG_TYPE_NAME) = LOWER(INPUT_DOG_TYPE_NAME))),
(SELECT DOG_TYPE_NAME AS LOOKUP_COLUMN, DOG_TYPE_ID AS OUTPUT_COLUMN1 FROM GD_DOG_TYPE WHERE DOG_TYPE_NAME = 'Unknown')
)
I have seen other examples on the forum where is has been suggested to use COALESCE in conjunction with the look up, however I am not sure about the parts where I am referencing the input value in the custom SQL as I have not seen examples on that neither on the forum or in the documentation.