I would like to Select all Records from a Oracle DB Table where the Part Number matches list of Part Numbers stored in CSV file. What is the best way to implement this in Knime?
Firstly, use either the CSV Reader or File Reader node - the latter is normally a better option, but check the documentation for the CSV reader in case it suits your requirements better -
Use this node if the workflow is used in a server or batch environment and the input files structure change between different invocations. In particular, this includes a variable number of input columns.
and
Note: If you are only using the desktop version of KNIME and your workflow is not meant to be used in a batch or server environment use the File Reader node. The file reader node has more configuration options and guesses meaningful default settings as it scans the input file during node configuration (in the dialog).
first to read in you csv file into a KNIME table
Then, use the Database Looping node - you need to set up a connection to your database, and then modify the SQL statement from the default
SELECT * FROM <table_name> WHERE <table_column> IN ('#PLACE_HOLDER_DO_NOT_EDIT#')
by replacing the placeholders in <> with the correct table and column names for your Part Numbers in the database, and selecting the part numbers column from the table which came from your csv file in the Column Selection setting at the bottom of the node configuration window under Loop Settings. For efficiency, you will almost certainly want to increase the No of Values per Query setting to a larger number, e.g. 250 or 500 - you will need to experiment with what your database will tolerate, as there is a limit to the maximum length of the SQL statement, which will be built up by replacing #PLACE_HOLDER_DO_NOT_EDIT# with a concatenation of your part numbers