SQL Query containing the output of a previous SQL query

CloverDX Platform
1

Hi I’m using the DatabaseReader to read the following data from SQL:

Field
1,2,3,4,5

I then send this to the input port of another DatabaseReader component but i’m trying to use the output from the previous component as part of the SQL query e.g.

Select *
From Table
Where X in (‘$in.0.Field’)

I can’t seem to get this to work. Is it even possible this way or should I be doing something else?

2

Hi Nick,

You can use the DatabaseReader to work with data from the input port, whether it originates from another DatabaseReader or any other data source. However, keep in mind that the SQL query editor cannot directly resolve placeholders from the input port. To overcome this, you need to build the complete SQL query for instance, in a Map component and pass it via the edge to the input port of the second DatabaseReader.

Here is an example:

obrazek
obrazek624×119 24.3 KB

  • The first DatabaseReader fetches Customer.city from the database.
    (the Dedup component only trims the number of records to 100)

  • The Map component Builds the SQL query dynamically based on the City data received:
    $out.0.SQLquery = "SELECT customers.id FROM customers WHERE customers.city = ’ " + $in.0.field1 + " ’ " ;

This creates 100 queries like this:

obrazek
obrazek467×114 24.9 KB

  • The configuration of the DatabaseReader2 is simple, instead of using the SQL Query property, set the Query URL with syntax: port:$0.SQLquery:discrete
    obrazek
    obrazek360×222 28.8 KB

Alternatively, in the Query URL dialog window go to Port tab and slect input port field (in this case SQLquery) and discrete.

obrazek
obrazek486×180 20.4 KB

The result of the DatabaseReader2 on the output port looks like this:
obrazek

I hope this helps. Let me know if you need any further assistance.

Best regards, Tom.

3

Great thanks - will try this out.