Create query based on input data

[Hamder83]

Hi guys 

Im in desperate need of a solution for this.

I have a input file containing:

Customer - Shipment id
Dennis        123456785
Martin         1111122257
Jens            222333564

I want to compare the shipment id to my mysql database

So I would love to be able to do something like 


SELECT * FROM 
Integration.booking_line bl

where

bl.shipment_awb  IN (

[Shipment_id]
)

is this in any way possible?

[RolandSchubert]

Hi @Hamder83 ,

it should be possible using the Dynamic Input tool. Create a query as you would do using the Input Data tool in "Input Data Source Template" including the "Where" statement. Modify SQL Statement provides the option to replace your template where clause by the specific list of Ids from your file. This list can be generated using the Summarize tool (concatenate entries). 

Let me know if it works for you or if you need more detail.

Best,

Roland

[Hamder83]

Hi Robert 

I dont think i have the knowledge to do that..

May I ask for you to create a super simple flow just with a text input and the dynamic input joined witha  db connection?

[RolandSchubert]

No problem. I enclosed the IDs in quotes, this is only needed if the database field is numeric. But to use "concatenate" in the Summarize tool, the field has to be a string data type.

[Hamder83]

Hi Roland 

Ive tried setting it up like this : 

ive added a DB (without a where clause?) 

But nothing happends when i run the flow ..

Do i need to add anything here?

[RolandSchubert]

The SQL is missing the template where clause. I tihkn, you should add something like "WHERE b.Id IN('999999')" to your SQL (if b.Id is the field the restriction should be applied on).

In the "Update SQL" section, click on EDIT. In the drop-down, you have to select your where condition, Text to Replace would be '999999' and the Replacement Field is the concatenated field you created before.

[Hamder83]

Hi again Robert, 

It almost works.

I changed it as you suggested - though clicking (Group Replacement Value For SQL IN Clause) on.

The query runs perfectly, but i does not return anything.

And if i run this query in my sql editor, i get results:

select

b.consignee_number, bl.carrier_name, bl.shipment_AWB
from
Integration.booking b
inner join Integration.booking_line bl on b.Id = bl.booking_id

where
bl.shipment_AWB in (

'4477179818',
'4631330927',
'4631369529',
)

So, what am i doing wrong?

[RolandSchubert · Accepted solution]

Hi @Hamder83 ,

double checked it with my database, it seems, concatenation and adding quotes are not needed, as the complete column is used for an "IN" statement. I've modified the sample, hopefully it works now as expected.

Best,

Roland

[Hamder83]

Hi 

As always, your ideas work like a charm! 🙂 

I have no knowledge of this setup. But i tried running a sql getting all data from past 90 days (280.000 records) and i did it this way.

The first query finished in 3mins. This one has run for an hour without ending (only 80.000 records). Is this a slow way of doing it or am i missing something?