unable to write back data from TM1 cube to Sql server DB tab

[TM1New]

Hi,

I am trying to load data from tm1 cube to DB table.

my datasource is

V1 V2 V3 V4
Product1 Jan M1 100

Prolog tab:-
ODBCopen('TestODBC','sa','sa');

data:-
ODBCoutput('TestODBC', Expand( 'INSERT INTO TM1_cube_Test (products, months, measures, Values) values (''%V1%'', ''%V2%'', ''%V3%'',''V4'')'));

epilog:
ODBCclose('TestODBC');

I am getting below error

"Product1","Jan","M1","100.",Data Source line (1) Error: Data procedure line (5): Error executing SQL query: "INSERT INTO TM1_cube_Test (products, months, measures, Values) values ('Product1', 'Jan', 'M1','V4')"

Please help me in loading this data to DB table.

Thanks

Cheers

[qml]

Are you sure you want to (and can) insert the string value "V4" into column Values?

If not, try this instead:

Code: Select all

ODBCoutput('TestODBC', Expand( 'INSERT INTO TM1_cube_Test (products, months, measures, Values) values (''%V1%'', ''%V2%'', ''%V3%'',''%V4%'')'));

[TM1New]

I have tried this. It's not working i am getting below error this time.

"Product1","Jan","M1","100.",Data Source line (1) Error: Data procedure line (5): Error executing SQL query: "INSERT INTO TM1_cube_Test (products, months, measures, Values) values ('Product1', 'Jan', 'M1',' 100.000')"

why it's not loading these values into table? I have given the correct data types in the table. product, month and measures are char and Values is Numeric.

can plz someone help me on this?

[tomok]

You are aware, if course, that encapsulating a value inside apostrophes indicates to SQL it is a string and not numeric don't you???????

[TM1New]

you are right but even if i try remove the double quotes for measure, it's throwing an error. i have tried all the possible ways but it's not working.

[TM1New]

I have tried this as well;

sql = 'INSERT INTO TM1_cube_Test (products, months, measures, Values) values ('''|V1|''', '''|V2|''', '''|V3|''',|V4|)';

ODBCoutput ('TestODBC', sql);

but no luck, it's the same error.

Cheers

[tomok]

Please read the "Request for assistance guidelines" post. More specifically, item #4. When you have complied with this I will be happy to help.

[TM1New]

Hi Thanks a lot for your suggestion. I have gone through the guide lines.

I am writing this code in Data tab of the TI for loading cube data into Database table.

Code: Select all

sql = 'INSERT INTO TM1_cube_Test (products, months, measures) values ('''|V1|''', '''|V2|''', '''|V3|''', |V4|)';

ODBCoutput ('TestODBC', Expand(sql));

I am getting the below error.

"Product1","Jan","M1","100.",Data Source line (1) Error: Data procedure line (7): Error executing SQL query: "INSERT INTO TM1_cube_Test (products, months, measures) values ('Product1', 'Jan', 'M1', |V4|)"

If i try below code by ignoring the V4(Numeric) variable then it is loading successfully.

Code: Select all

sql = 'INSERT INTO TM1_cube_Test (products, months, measures) values ('''|V1|''', '''|V2|''', '''|V3|''')';

ODBCoutput ('TestODBC', Expand(sql));

only problem is with Numeric values. Can someone plz help me to load the numeric values in table?

[Michel Zijlema]

Hi Thanks a lot for your suggestion. I have gone through the guide lines.

I am writing this code in Data tab of the TI for loading cube data into Database table.

Code: Select all

sql = 'INSERT INTO TM1_cube_Test (products, months, measures) values ('''|V1|''', '''|V2|''', '''|V3|''', |V4|)';

ODBCoutput ('TestODBC', Expand(sql));

I am getting the below error.

"Product1","Jan","M1","100.",Data Source line (1) Error: Data procedure line (7): Error executing SQL query: "INSERT INTO TM1_cube_Test (products, months, measures) values ('Product1', 'Jan', 'M1', |V4|)"

If i try below code by ignoring the V4(Numeric) variable then it is loading successfully.

Code: Select all

sql = 'INSERT INTO TM1_cube_Test (products, months, measures) values ('''|V1|''', '''|V2|''', '''|V3|''')';

ODBCoutput ('TestODBC', Expand(sql));

only problem is with Numeric values. Can someone plz help me to load the numeric values in table?

You forgot the (single) single quotes before and after the V4 variable, in effect making the variable and concatenate operators part of the string.
The below code should do the trick:

Code: Select all

sql = 'INSERT INTO TM1_cube_Test (products, months, measures) values ('''|V1|''', '''|V2|''', '''|V3|''', ' |V4| ')';

ODBCoutput ('TestODBC', sql);

Michel

[TM1New]

Hi Michel,

Thanks for your help. But when i try to include the strings, i am getting syntax error, it's not allowing me to put the quotes to numeric values.
the below code is throwing me the syntax errors at V4 position.

Code: Select all

sql = 'INSERT INTO TM1_cube_Test (products, months, measures, Values) values ('''|V1|''', '''|V2|''', '''|V3|''', ' |V4| ')';

ODBCoutput ('TestODBC', sql);

[rmackenzie]

But when i try to include the strings, i am getting syntax error, it's not allowing me to put the quotes to numeric values.
the below code is throwing me the syntax errors at V4 position.

That's right - because TI doesn't want to let you use a string operator on a numeric value... Can you try using NumberToString on V4 like this:

Code: Select all

sql = 'INSERT INTO TM1_cube_Test (products, months, measures, Values) values ('''|V1|''', '''|V2|''', '''|V3|''', ' |NumberToString(V4)| ')';

ODBCoutput ('TestODBC', sql);

You're not actually converting the number to a string within the TI, just for the purposes of sending a string-based instruction to the ODBC driver. Equally, the RDBMS at the other end will understand that you are trying to insert a numeric value to the relevant field.

[TM1New]

Hi Many thanks, It's working with this syntax.