SQL Update Query

[nsalvarez]

I need to add a simple update query to my prolog for a process. How can I accomplish this? The data source I want to run the query against is the datasource the process is using. It does not work if I try to put it in the query box. Here is the query I need to add:

Code: Select all

Update ViewDailySalesStaging

set MaterialCost = (Amount * .40)

WHERE 
(

LEFT(CONVERT(VARCHAR,TransactionDate,102),7) = LEFT(CONVERT(VARCHAR,GETDATE(),102),7)

OR  LEFT(CONVERT(VARCHAR,TransactionDate,102),7) = LEFT(CONVERT(VARCHAR,DATEADD(m,-1, GETDATE()),102),7)

)

AND

DetailType = 'Intake'

AND

CompanyCode = 22

Here is my select statement for the process data source:

Code: Select all

select 
*
from  ViewDailySalesStaging
WHERE 
LEFT(CONVERT(VARCHAR,TransactionDate,102),7) = LEFT(CONVERT(VARCHAR,GETDATE(),102),7)
OR  LEFT(CONVERT(VARCHAR,TransactionDate,102),7) = LEFT(CONVERT(VARCHAR,DATEADD(m,-1, GETDATE()),102),7)

[qml]

Three words for you:

ODBCOpen()
ODBCOutput()
ODBCClose()

Have a look at the documentation or search this forum, there's plenty of resource available on how to run any SQL command from TI script.

[nsalvarez]

I tried the following but I keep getting an error. It wants me to add a comma but I am not sure why.
odbcopen('MyDB', 'Admin', 'password');

odbcoutput('MyDB', 'Update ViewDailySalesStaging set MaterialCost = (Amount * .40) WHERE (LEFT(CONVERT(VARCHAR,TransactionDate,102),7) = LEFT(CONVERT(VARCHAR,GETDATE(),102),7)OR LEFT(CONVERT(VARCHAR,TransactionDate,102),7) = LEFT(CONVERT(VARCHAR,DATEADD(m,-1, GETDATE()),102),7))AND DetailType = 'Intake' AND CompanyCode = 22 ');

odbcclose ('MyDB');

The error is: Syntax error on or before:
Intake' AND CompanyC
missing comma

[declanr]

I tried the following but I keep getting an error. It wants me to add a comma but I am not sure why.
odbcopen('MyDB', 'Admin', 'password');

odbcoutput('MyDB', 'Update ViewDailySalesStaging set MaterialCost = (Amount * .40) WHERE (LEFT(CONVERT(VARCHAR,TransactionDate,102),7) = LEFT(CONVERT(VARCHAR,GETDATE(),102),7)OR LEFT(CONVERT(VARCHAR,TransactionDate,102),7) = LEFT(CONVERT(VARCHAR,DATEADD(m,-1, GETDATE()),102),7))AND DetailType = 'Intake' AND CompanyCode = 22 ');

odbcclose ('MyDB');

The error is: Syntax error on or before:
Intake' AND CompanyC
missing comma

TM1 uses the ' character to signify the start and end of a string input, therefore anything between ' and ' is one string item, you have 4 ' characters in what TM1 is expecting to be just 1 input. There are a number of ways you can get around this, all of which will be covered on the forum in one place or another... personally I tend to go with:

Code: Select all

String = ''' | 'Turnips''' | ' and ''' | 'Carrots''';

[tomok]

The error is: Syntax error on or before:
Intake' AND CompanyC
missing comma

That's because the apostrophe is the string delimiter in TM1. When you enclosed Intake in apostrophes you broke the string and you are getting a syntax error. You can use the EXPAND function, in conjunction with double apostrophes to fix:

Code: Select all

odbcopen('MyDB', 'Admin', 'password');
sql = EXPAND('Update ViewDailySalesStaging set MaterialCost = (Amount * .40) WHERE (LEFT(CONVERT(VARCHAR,TransactionDate,102),7) = LEFT(CONVERT(VARCHAR,GETDATE(),102),7)OR LEFT(CONVERT(VARCHAR,TransactionDate,102),7) = LEFT(CONVERT(VARCHAR,DATEADD(m,-1, GETDATE()),102),7))AND DetailType = ''Intake'' AND CompanyCode = 22 ')
odbcoutput('MyDB', sql);
odbclose('MyDB');

[nsalvarez]

Thanks for the help everyone!