Appending Tables: One Issue

[Anonymous]

Hello. Here is what I want to do:

1. During runtime, use SQL to get a *value* (date) from the target table (such as the last update date).

2. Use the *value* in a SQL WHERE clause to pull just the needed subset of data from the source table.

3. Append the subset of source data to the target table.

The problem I am having is injecting the *value* into the SQL WHERE clause. I can get the *value* through a lookup (tMSSqlInput), but I do not know how to pass that value into the SQL code. 

Example code.

SELECT
ID,
Name,
Address1,
Address2,
City,
Zip,
UpdateDate
FROM dbo.DatabaseSource.Table
WHERE UpdateDate > passedIn*Value*

Thanks for any help!

Alllen

[TRF · Accepted solution]

Store the value into a global variable (as the result of a tMSSQLInput for example) the reuse the variable to construct the select like this:

"SELECT
ID,
Name,
Address1,
Address2,
City,
Zip,
UpdateDate
FROM dbo.DatabaseSource.Table
WHERE UpdateDate > '" + (String)globalMap.get("yourVariableHere") + "'"

Hope this helps.

[Anonymous]

Hello,

if you can get the *value* you have to put it on globalMap with the following code :  globalMap.put("myValue", *value*)

then in your sql code you can write this :  "select * from table where column = ' "+(String)globalMap.get("myValue")+" ' "

hope this help,

Good luck,

SGV

[Anonymous]

Hi Everybody,

I'm very new to TOS and I have almost a similar situation in MS SQL where i'm suppose to insert latest records to a table AC. So I'm trying to get max Date from AC to know which records to fetch from another SQL DB. For this as per above diagram i have started with a tMSSQLinput_1 component with above mention query then i want to store the result set of  tMSSQLinput_1 to tSetGlobalVar_2 (not sure if this is the best approach). As you can see i have added a Key and just to test if the results are stored i have used tLogRow_1 Component. but i am getting this error

"Execution failed : object is not an instance of declaring class
Job AgentCubeNew ended at 20:59 06/03/2019. [exit code=0]

I would like to know is this the right approach?, also where should i write globalMap.put("myValue",*value*) to set the value in my variable from the query ?

Once i'm able to set the variable i would like to call it in another MSSQLinput component to get records from another db table which > maxdate of table AC

Please guide
"