Ask Your Question

JDBC Lookup on Not Found Records

asked 2017-12-14 20:38:06 -0500

Vivian Y gravatar image

updated 2017-12-15 18:17:48 -0500

metadaddy gravatar image

How can i handle JDBC lookup if the lookup field does not exist in the database?

Example : table_product

|col_1   | col_2   |
|a       | apple   |
|b       | boy     |
|c       | cat     |

In my database and tables, these are 3 records. Hence my jdbc query will be

select col_1 , col_2 from table product where col_1 = '${record:value('/product_id')}'

If my product_id is "d", there is no matching records found, hence , streamsets now will return error stating "No Matching Record Found".

In this instance, how can i handle the no matching record while using JDBC Lookup?

My Expected Results will be if the product_id is not found, return "NA"

edit retag flag offensive close merge delete

2 Answers

Sort by ยป oldest newest most voted

answered 2017-12-15 18:22:35 -0500

metadaddy gravatar image

You can do this in the product, without changing your query. Set a Column Mapping with a Default Value for product_id, like so:

image description

edit flag offensive delete link more

answered 2017-12-14 21:12:42 -0500

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Found a solution for this but not sure is it a good solution to implement. Did extra function in sql where to return the text if its null ,


    COALESCE(col_1,'${record:value('/product_id')}') as Column1, 
    COALESCE(col_2, 'NA') as Column2, 
    COUNT(0) as Records 
WHERE col_1 = '${record:value('/product_id')}'
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-12-14 20:38:06 -0500

Seen: 729 times

Last updated: Dec 15 '17