Syntax for deleting records from table using sql inside of TI job

[Cashford]

After multiple syntax fails, I have been able to load an external table using TM1 with the following syntax. However, I continue to get fails when trying to delete the data from the table. I have verified that my account has delete access because I am able to remove all of the data from the table with a simple Delete sql function but, I want to be able to select the items I want to remove.


Syntax that works:
ODBCOpen (Source, ClientName, Password);

sSQL = 'INSERT INTO "TableName" VALUES(''' | tblcolumnvar1 |''','''| tblcolumnvar2 |''','''| tblcolumnvar3 |''','''| tblcolumnvar4 |''','''| tblcolumnvar5 |''','''| tblcolumnvar6 |''','''| tblcolumnvar6 |''','''| tblcolumnvar7 |''','''| tblcolumnvar8 |''','''| tblcolumnvar9 |''','''| tblcolumnvar10 |''','''| tblcolumnvar11 |''','''| tblcolumnvar12 |''','''| tblcolumnvar13 |''','''| tblcolumnvar14 | ''')' ;

ODBCOutput ( Source, sSQL);
_______________________________________________________________
Syntax that fails:
ODBCOpen (Source, ClientName, Password);

sSQL = 'DELETE FROM "TableName" WHERE( Period = ( '| tblcolumnvar1 |' AND '| tblcolumnvar2 |' ))';

ODBCOutput ( Source, sSQL);

[David Usherwood]

TM1 doesn't require special SQL syntax - it just sends what you give it.

The best way to debug ODBCOutput SQL statements is to replace
ODBCOutput(Source,sSQL);
by
AsciiOutput('test.csv',sSQL);
and inspect the text you are trying to send. Very likely you haven't got the quotes right.

[gtonkin]

Once you have done what David suggests, I think you will see an issue with your WHERE clause - looks to ultimately be saying
WHERE PERIOD = X AND Y - cannot be both, maybe you meant BETWEEN.

Also, a better approach for these kinds of expressions is to use Expand() e.g.

Code: Select all

sSQL = EXPAND('DELETE FROM "TableName" WHERE( Period = ( %tblcolumnvar1% AND  %tblcolumnvar2% ) )');

Makes it easier to read and removes risk with messing up quotes.

Lastly, assuming too that the data type for Period and your TI variables are the same i.e. string/varchar otherwise you will have mismatches there.
Again, best is to follow David's advice to output to test and from there you can pick up the SQL and review/execute.