Modify SQL

[adamturnbull]

Hi All,

Not sure if i'm doing this the right way if or it's even possible.

There's 2 part to my workflow.

The first part works fine. It gets a list of account numbers from an excel file and concatenates them into a string.

The second part is where i'm stuck.

I'm connecting to relational database and have a SQL query written, but I want the string from the first part of the workflow to be added to the SQL query without me having to go in and manually update the query each time.

Is there a way for this to be automatically updated/modified?

The account numbers from the first part of the workflow will be different each time i run it so i need the SQL query to be updated each time.

[apathetichell]

First question:

Do you have your database set up via an In-DB connection? You can do this by setting it up under advanced options.

Next part:

Your hunch to concatenate your number into a string is fantastic. What i'd do is use a summarize tool with (  ,   ) in my 3 concatenate fields. (i thin you need the beginning (' and ending ') but I cant quite remember) - if your field you are checking against is a string field you'll obviously need '' somewhere along the way to separate your list of entries.

Next up figure out where in your query does it go. Do you need an and or is just a where? assume you are are adding it to the end of your query.

put the rest of the your query in a text input tool.

use a formula tool to include a "where ....  IN  "+[contactenated text] (where ... is the name of your sql field - noted with quote marks). attach a browse tool to see how your new SQL looks and if it makes sense.

Now use a union field to add your new query line to your pre-existing text input query - making sure to put your concatenated where clause at the end.

use summarize tool one more time to add it all together - i use /n as a divider for readability.

use a formula tool to create the connection name - this is what you called your in-db connection. it could be SNOWFLAKE or DATABRICKS or MY_IN-DB or MODIFY_SQL.

now comes the fun part - drag a dynamic connect in-db tool. Attach this to what should be one row of your data - your query and your connection. tell in-db what your query and connection are.

Voila. you have now updated your where clause for your huge list of values

now

[adamturnbull]

Thanks for that :)

I hadn't set up an In-DB connection. I was just connecting to the database via the input tool. I've been able to read data using this method.

I just tried setting up an In-DB connection but i'm getting an error.

Do you know what could be causing this?

Thanks in advance.

[adamturnbull]

N

[apathetichell]

the connection works in input data but not in in-db? that would be weird... I don't use Terradata much so I can't help with that error. Sorry.

[guptav_pfizer]

Below is what I would suggest and hoping it works for you.  Under the Developer Group, you should be able to locate Dynamic Input.

Set up your query in this and then provide your variable part in the Modify SQL Query Section.  

SQL Statement

Modify SQL Query

TEXT INPUT for Query (just like a parametrized query)

[adamturnbull]

Thanks Guptav.

I'm a little confused by this. Would you be able to step me through each part of this in more detail?

[adamturnbull]

n

[guptav_pfizer]

You have stated: The account numbers from the first part of the workflow will be different each time i run it so i need the SQL query to be updated each time.

My assumption is that the Account Number from WF1 (Work Flow 1) is going in the WHERE clause in your SQL Statement.  If that is true, then you could use the Dynamic Input.  If the above is true then.  Using Dynamic Input, you can create your connection to the SQL Server and write your sql statement in the Table or Query.  In the Query, in the WHERE statement, you put in your criteria and make the declaration with 'xxx' or some thing like that.

Once your SQL Statement is set in the query, test it and OK.

In the Modify SQL Query section, use ADD to insert information incoming from your WF1 that will go and replace the 'xxx' in the SQL Statement.

Hoping this is able to help.  

[adamturnbull]

Thanks guptav_pfizer.

I'm getting there now :)

Though, i'm stuck with something else now. 

I concatenated the numbers to a string so they can be updated in the where clause.

But im getting an error in the dynamic input tool saying a character string failed conversion to a numeric value. Do you know a way around this?