Page 1 of 1
SQL Update Query
Posted: Tue Sep 24, 2013 3:40 pm
by 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)
Re: SQL Update Query
Posted: Tue Sep 24, 2013 4:25 pm
by 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.
Re: SQL Update Query
Posted: Tue Sep 24, 2013 5:10 pm
by 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
Re: SQL Update Query
Posted: Tue Sep 24, 2013 5:31 pm
by declanr
nsalvarez wrote: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''';
Re: SQL Update Query
Posted: Tue Sep 24, 2013 5:34 pm
by tomok
nsalvarez wrote: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');
Re: SQL Update Query
Posted: Tue Sep 24, 2013 8:24 pm
by nsalvarez
Thanks for the help everyone!