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