TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

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

https://www.tm1forum.com/viewtopic.php?t=6872

Page 1 of 1

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

Posted: Thu Feb 02, 2012 5:17 pm
by 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

Re: unable to write back data from TM1 cube to Sql server DB

Posted: Thu Feb 02, 2012 5:23 pm
by 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%'')'));

Re: unable to write back data from TM1 cube to Sql server DB

Posted: Fri Feb 03, 2012 2:55 am
by 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?

Re: unable to write back data from TM1 cube to Sql server DB

Posted: Fri Feb 03, 2012 3:08 am
by 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???????

Re: unable to write back data from TM1 cube to Sql server DB

Posted: Sun Feb 05, 2012 10:56 am
by 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.

Re: unable to write back data from TM1 cube to Sql server DB

Posted: Sun Feb 05, 2012 11:14 am
by 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

Re: unable to write back data from TM1 cube to Sql server DB

Posted: Sun Feb 05, 2012 2:26 pm
by 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.

Re: unable to write back data from TM1 cube to Sql server DB

Posted: Mon Feb 06, 2012 5:59 pm
by 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?

Re: unable to write back data from TM1 cube to Sql server DB

Posted: Mon Feb 06, 2012 8:10 pm
by Michel Zijlema
TM1New wrote: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

Re: unable to write back data from TM1 cube to Sql server DB

Posted: Tue Feb 07, 2012 4:21 am
by 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);

Re: unable to write back data from TM1 cube to Sql server DB

Posted: Tue Feb 07, 2012 4:27 am
by rmackenzie
TM1New wrote: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.

Re: unable to write back data from TM1 cube to Sql server DB

Posted: Tue Feb 07, 2012 3:30 pm
by TM1New
Hi Many thanks, It's working with this syntax.
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited