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

[pikolikoli]

Hello everyone,

I am new to TI and I am trying to insert an numeric variable called 'Value' to a decimal column in database.

Here is my sql query :

Code: Select all

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

But this gives me this error :

Code: Select all

Error : Incorrect syntax Near '|';

I would love if someone could help me to figure out how to insert a numeric column value in an sql query.

I would appreciate it so much.

[paulsimon]

Hi

Assuming Value is of type numeric rather than string then you need to convert it to a string before you can concatenate it with other strings. Use NumberToString( Value )

Regards

Paul Simon

[pikolikoli]

Thank you very much for answering my question.

But bothe the variable in tm1 and in database is of type numeric.

So when I convert it to string I have an error saying :

Code: Select all

Converting data Type varchar to numeric 

[pikolikoli]

Hi

Assuming Value is of type numeric rather than string then you need to convert it to a string before you can concatenate it with other strings. Use NumberToString( Value )

Regards

Paul Simon

Thank you very much for answering my question.

But both the variable in tm1 and in database is of type numeric.

So when I convert it to string I have an error saying :

Code: Select all

Converting data Type varchar to numeric 

[paulsimon]

Hi

When you converted it to string did you put double quotes around it? You shouldn't do that. You are constructing a SQL statement which has to be string. Therefore the 'value' cannot be numeric as you cannot concatenate a numeric into a string. You need to put

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

As you have no quotes around the value, SQL will treat it as a number.

If it still does not work, the standard way to debug these things is to put a TextOutput statement just before this to output the SQL variable to a file and then paste the generated SQL statement into the Query editor of whatever relational database you are using and then run it there as you will probably get better error reporting from this. ODBC has a nasty habit of surpressing some errors.

However, I believe that in this case your error is really at the TM1 stage rather than SQL

I would recommend that when constructing an INSERT statements that you always use the ( column list ) before VALUES, which ensures that if there are any columns added in the future then your statement won't fail.

I would also suggest using better variable names than dim1 to make your code easier to understand. I would also avoid using variable names like SQL as they could clash with a reserved word.

Regards


Paul SImon