TM1 Forum

Hi All,

I having an issue loading data from TM1 into an Oracle Table (using Version TM1 10.1.1). Below is all the Code contained within the TI. Using previous posts on the forum I have attempted the following:
1. Using the Expand function (Including and excluding quotes “” “”)
2. Removing the Expand function
3. Exported CubeView Data to csv and validated data is actually coming through

I am going to continue investigate but would really appreciate if anyone has any other suggestions. Thanks in advance.

DataSources - CubeView (SecurityCube)
Department
SecurityGroup
sValue
* Zero Suppressed, Includes Consolidations

Variables
vSecurityGroup (substring to remove special character “/”)
sTable = ‘DepartmentSecurity’;

Prolog – This Works
SQLQuery = 'CREATE TABLE DepartmentSecurity (DEPARTMENT_ID VARCHAR2(50), RIGHTS VARCHAR2(50), USER_ID VARCHAR2(50))';
ODBCOutput(Source, SQLQuery);

Data
SQLQuery = Expand( 'INSERT INTO %sTable% (DEPARTMENT_ID, RIGHTS, USER_ID) VALUES ("%Department%","%sValue%","%vSecurityGroup%")' );
ODBCOutput(Source, SQLQuery);

Error Message
"Corp","Cognos\TM1 Test Group 3","WRITE",Data Source line (1) Error: Data procedure line (17): Error executing SQL query: "INSERT INTO DepartmentSecurity (DEPARTMENT_ID, rights, USER_ID) VALUES (Corp,WRITE,TM1 Test Group 3)"

How much do you know about SQL? Are you aware that string values inserted into a table have to be enclosed in apostrophes? Take a look at this:

"INSERT INTO DepartmentSecurity (DEPARTMENT_ID, rights, USER_ID) VALUES (Corp,WRITE,TM1 Test Group 3)"

Do you see apostrophes around your values Corp, WRITE, TM1 Test Group 3?

Thanks Tomok.