TM1 Forum

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);

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.

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. 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.