ODBC drive error for Oracle database

[qml]

@charan, sorry mate, but Wim, Michel and tomok have given you answers to all your questions already. Now it is a case of constructing a correct insert statement and putting it on the Data tab of a process that has your cube view set as its data source. If you are unable to do that then maybe you should start thinking about paying someone to do it for you.

[rmackenzie]

Hi Michel,
Thank you, I have used only '' value'' this is the syntax I used after Values in the statement and row updated in emp table but the issue is it has updated many times nearly 100 times. I have ran TI only 5 times.
Any suggestion for why the same row updated many times.

If you don't want the rows to duplicate every time you run the TI then you should TRUCNATE the table before you run the update process:

Code: Select all

# this code goes in the Prolog
OdbcOutput ( 'orcl', 'TRUNCATE TABLE dept' )

Or, based on your more recent post:

Code: Select all

# this code goes in the Prolog
OdbcOutput ( 'orcl', 'TRUNCATE TABLE Product_TM1' )

You can do a lot with OdbcOutput aside from INSERTing - CREATE, DROP and TRUNCATE tables, run stored procedures etc.

Hi Tomok,
please find the attachment what I am trying to do. Image is the table I created for loading cube view data. I want all cube view data into the table.
please suggest my syntax where I am getting error after Values statement.

The code you were using for the 'dept' table can be changed to work for the 'Product_TM1' table just by making sure that you are building your INSERT statement correctly. E.g.:

Code: Select all

OdbcOutput ( 'orcl', 'INSERT INTO Product_TM1 (Products, Budgetversion, PriceandCost, Mvalue) Values (... YOU WORK OUT THE APOSTROPHES HERE...)' );

It sounds like you had this working successfully for 'dept' as you had duplicated rows. Just work on that statement till you get it right and remember to TRUNCATE the table in the Prolog to stop the rows duplicating. Remember to use apostrophes appropriately to the type definition of each column in the table. VARCHAR, CHAR, TEXT, etc all will need the apostrophes around the values. FLOAT, INT, DECIMAL, etc don't need the apostrophes in the INSERT statement.

@charan, sorry mate, but Wim, Michel and tomok have given you answers to all your questions already. Now it is a case of constructing a correct insert statement and putting it on the Data tab of a process that has your cube view set as its data source. If you are unable to do that then maybe you should start thinking about paying someone to do it for you.

I have to agree with qml. You need to work harder on working with the answers you are given to your questions. Also, you need to work harder on explaining how you used the answers to solve your problems and what progress you made. Charan, you remind me of the old Russian proverb:

Hunger is not your Aunt, it will not bring you a pie.

[charan]

Hi Mackenzie,
thank you for your suggestion I am asking help, with What I am doing, I tried with What expertise suggestions given to me but still the process was not succeed, so I am requesting,

[charan]

HI,
This is the new query I am trying.

ODBCOutput('Ora11g', 'Insert Into [Product_TM1] ([Products],[ Budget Version],[ Price and cost], [ MValues]) Values (''%V1%'',''%Products%'',''%V3%'','' %Value%''); ');

Still my process is not successful

Suggest me for this.

[qml]

Michel suggested a slightly different syntax - using the pipe character to concatenate variables to get your end result. Your approach with the %X% notation should work as well, but you need to wrap it in the EXPAND() function to get the values from the variables instead of the literal text.

Another suggestion you received, which you seem to have ignored, is to ASCIIOUTPUT your SQL insert statement to a file and then paste it into an Oracle client to see which part of the syntax Oracle doesn't like.

[charan]

Hi Qml
Thank you.
I am trying with this syntax as well, but I am getting error for this also,

ODBCOutput('Ora11g', 'Insert Into [Product_TM1] ([PRODUCTS],[ BUDGETVERSION],[ PRICEANDCOST], [ MVALUE]) Values (''' | V1 |''','''| Products |''','''| V3 |''',' | Trim(Str(Value)) | ' ) ; ');

The error is like,

Syntax error on or before value)) |');
Invalid numeric expression.

[qml]

Syntax error on or before value)) |');
Invalid numeric expression.

STR() function requires three numeric parameters and you are only providing one.

[charan]

Hi Qml,

Thank you

I have tried with this query,

ODBCOutput('Ora11g', 'Insert Into Product_TM1 ([ PRODUCTS ], [ BUDGETVERSION], [ PRICEANDCOST], [ MVALUE ]) Values (''' | V1 |''','''| Products |''','''| V3 |''',' | Trim(Str(Value)) | ' ) ');

Getting an error unexpected parenthesis.

[qml]

STR() function requires three numeric parameters and you are only providing one.

[charan]

Hi Qml,
I did not get you as I am poor in oracl databaseknowledge.
Please suggest what to do.

Thanking you

[charan]

Hi Qml,

In variables tab I have selected values to numeric before it was string when I changed, now the error I am facing is unexpected parenthesis.

Please help me in getting the result.

[qml]

STR() is a TM1 Rules/TI function that you are trying to use incorrectly in your code. Please have a look at the TM1 Reference Guide to see how it should be used.

[charan]

Hi Qml,

Thank you very much for your support I have added 9,0 their and finally cube view data has uploaded into Oracle table,

But when I tried with another cube view I am facing an error like

"Americas","Department Store","Average Monthly Revenue","Full Size Store","Franchise Store","DATA","233446.82",Data Source line (1) Error: Data procedure line (7): Error executing SQL query: "Insert Into StoreCost (COUNTRYANDREGION, RETAILERS, ASSUMPTIONS, STORESIZE, STORETYPE, DATA, MEASUREVALUE) Values ('Americas','Department Store','Average Monthly Revenue','Full Size Store','Franchise Store','DATA',233447)"

Can you please guide me one more time what the wrong I am doing in this.

ODBCOutput('Ora11g','Insert Into StoreCost (COUNTRYANDREGION, RETAILERS, ASSUMPTIONS, STORESIZE, STORETYPE, DATA, MEASUREVALUE) Values ('''| V1 |''','''| Retailers |''','''| Assumptions |''','''| V4 |''','''| V5 |''','''| DATA |''','|Trim(Str(Value,50,0))|')');

Thanking you

[qml]

Try enclosing your numeric value in single quotes.

Also, let me repeat the suggestion you have been given already:

ASCIIOUTPUT your SQL insert statement to a file and then paste it into an Oracle client to see which part of the syntax Oracle doesn't like

Ultimately, you will have to learn to debug your own code or pay someone to do it. We're a helpful bunch here, but we don't really have the capacity to help you out with every single line of code you produce.

[charan]

Thank You Qml,

I do not have knowledge on Oracle or sqlserver as of now, for this requirement I have started learning it.

I am not understanding what you said in last post regarding running the Sql query.
Please can you elaborate that.

Qml thank you very much for your support to get the output.

[Wim Gielis]

Thank You Qml,

I do not have knowledge on Oracle or sqlserver as of now, for this requirement I have started learning it.

I am not understanding what you said in last post regarding running the Sql query.
Please can you elaborate that.

Qml thank you very much for your support to get the output.

Charan

I hope that, by now, you have understood that ODBCOutput is executing a query and that the query is a string of text that you form.

If you did not understood this part, it's time to take a step back and think about the point of doing this exercise.
If you understood (that's the only other option that is left), you will notice that the SQL query, the string, the chain of characters, can be written to a text file.
Use the ASCIIOUTPUT function for that. In a text file, you will be able to read (and copy/paste) what query you send to the relational database.

Copy/paste this string (without the usual " " surrounding your query) and execute this query manually in SQL Server. For example, using SQL Server Management Studio.
See what is happening, or what is not happening, or what messages you get.
Correct any error messages by changing the SQL query in the ODBCoutput function and ASCIIOUTPUT function.
Repeat all above steps until the query is working.

If at this point you think that I was writing in Chinese language, please reconsider again the whole exercise you are doing, and/or hire an experienced person.

[charan]

thank you, I understood that.

[charan]

Hi,
Now I am trying with diffrent scenario, now the scenario is I have taken table from oracle database and mapped it in TM1through TI see the view in the attachment.
Now when I write back same data from TM1 to same table in the dtabase,I am facing an error as Executing in SQL statement my problem here is I am unable to match TM1 dimensions to Oracle table.
In TM1 i have only two dimension, in oracle table I have seven columns so in writing syntax I am not update to mark and I have tried something with new formulas in TM1 variables tab for mapping it but not able to achive it.
Please help me.

TM1 10.1
Excel2007

Thank you.

[charan]

HI,
I tried with QML suggestion, by using the textOutput function, then I got follwoing error.Please find the attachment,can you help me further.

Thank you.

[charan]

Hi Qml,

Any suggestions please.