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

Post Reply
pikolikoli
Posts: 3
Joined: Sun May 17, 2020 2:37 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2013

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

Post by pikolikoli » Sun May 17, 2020 3:24 pm

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.

User avatar
paulsimon
MVP
Posts: 749
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

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

Post by paulsimon » Sun May 17, 2020 4:12 pm

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
Posts: 3
Joined: Sun May 17, 2020 2:37 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2013

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

Post by pikolikoli » Sun May 17, 2020 5:18 pm

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
Posts: 3
Joined: Sun May 17, 2020 2:37 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2013

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

Post by pikolikoli » Sun May 17, 2020 5:46 pm

paulsimon wrote:
Sun May 17, 2020 4:12 pm
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 

User avatar
paulsimon
MVP
Posts: 749
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

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

Post by paulsimon » Sun May 17, 2020 6:00 pm

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

Post Reply