TM1 : Error :Incorrect syntax Near '|' (inserting numeric value into sql query)

Question

I am trying to insert a numeric variable 'Value ' into a decimal database column.

I used this query syntax :

SQL= 'INSERT INTO DB_Archive VALUES ( '''| dim1|''' , '''| dim2|''', '|Value|') ';

But it seems it's not correct way to declare a numeric column, which gave me this error :

Error : Incorrect syntax Near '|'

I tried then converting the numeric value to a string value like this :

zAmoun= NumberToString(Value);
SQL= 'INSERT INTO DB_Archive VALUES ( '''| dim1|''' , '''| dim2|''', '''|StringToNumber(zAmount)|''') ';

But it was a bad move to do , since it worked in the compilation and didnt return no error but in the execution it return this error here since the table column is a decimal :

Error : Error Converting data Type varchar to numeric

I would love if someone could help me know what is the correct syntax to declare a numeric column in sql query , since the first declaration is not working.

I would appreciate it very much.

Answer 1

Maybe this Information can help you:

At the start: Please don't start variable names with upper cases. Make something like vSQL or sqlRequest.

You try to build a SQL-Command with a string. The hole string cannot include numeric. TM1 is really strict with it. Every number must be converted to String.

vsValue = NumberToString(value);
vsSQL= 'INSERT INTO DB_Archive VALUES ( '''| dim1|''' , '''| dim2|''', '''|vsValue|''') ';

Maybe your SQL-Target need the value in a specific form. For example with a "," as decimal seperator. Therefore you can us the command NumberToStringEx().

vsValue = NumberToStringEx(value, '0.0####', ',', '.');
vsSQL= 'INSERT INTO DB_Archive VALUES ( '''| dim1|''' , '''| dim2|''', '''|vsValue|''') ';

here you find additional information

With quotation marks you will give your sql-target the information, if the value is numeric or string. So maybe it's necessary to delete the quotation marks:

vsValue = NumberToString(value);
vsSQL= 'INSERT INTO DB_Archive VALUES ( '''| dim1|''' , '''| dim2|''', '|vsValue|') ';