Extract Cube Date w/ODBCOutput 9.1

[rfielden]

Newbie question #67: I am trying to extract Applix cube data into an Oracle table to find another application. I have created a view of the cube that I use as the Data Source and the Variable listing comes up correct. Any special way the variables should be defined? Do I need to tag as element, data, etc. and complete associated tabs?

The following script works find if I hard code the variable results but not if I use the variable names. Any clues or suggestions?

#vSql = 'INSERT INTO ( YEARS,PERIODS,BESTATES,BEMEASURES,MVALUE ) VALUES (''2009'',''All'',' #'NC'',''Metro'',1000.23);'; vSql = 'INSERT INTO [table name]( YEARS,PERIODS,BESTATES,BEMEASURES,MVALUE ) VALUES (''Years'',''Periods',''BeStates'',''BeMeasures'',Value) ;'; ODBCOpen('server', 'user', 'password'); ODBCOutput('server, 'TRUNCATE TABLE [table name];'); ODBCOutput('server',vSql); ODBCClose('server'); Also tried the following with same 'no' result. ODBCOutPut( 'appstst', Expand( 'INSERT INTO ak_rfiel.applix_test ( Years,Periods,BeStates,BeMeasures,MValue )' | ' VALUES (''Years'',''Periods'',''BeStates'',''BeMeasures'',''Value'')')); Log file shows: Error executing SQL query: "INSERT INTO ak_rfiel.applix_test ( YEARS,PERIODS,BESTATES,BEMEASURES,MVALUE ) VALUES ('Years','Periods','BeStates','BeMeasures',Value);"

[Alan Kirk]

Newbie question #67: I am trying to extract Applix cube data into an Oracle table to find another application. I have created a view of the cube that I use as the Data Source and the Variable listing comes up correct. Any special way the variables should be defined? Do I need to tag as element, data, etc. and complete associated tabs?

The element, data etc muck is only when you're using the TI wizard, which is evil. It's not relevant to your issue, but the question does suggest an answer.

The following script works find if I hard code the variable results but not if I use the variable names. Any clues or suggestions?

Which tab are you doing this on? From the sounds of it it may be the Prolog, which would explain why it works with hard coded values but not otherwise. Remember that variables have no value in the Prolog tab. They only acquire a value when the process loops through the data source in the Metadata and/or Data tab. (If the data source has been processed via one of those two tabs the variables retain the last value read in the Epilog tab, but persontally I regard it as bad practice to use data source variables in that tab... and it's pointless to use them in the Prolog tab.)

Moving the output statement to the Data tab probably won't make it immediately work; Oracle's far too fiddly for that and you may need to play around with including or excluding quote marks around string values, but it would be the most important step.

[Steve Rowe]

You also need to use the Expand TI command to convert the variables to their values at run time.
HTH

TM1 Reference Guide > TM1 TurboIntegrator Functions > Miscellaneous TurboIntegrator Functions > Expand

--------------------------------------------------------------------------------

Expand
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function "expands" TurboIntegrator variable names, enclosed in % signs, to their values at run time. If the variable name represents a string variable, the entire variable expression must be enclosed on quotes. For example, "%V1%".
A common use of the Expand function is to pass the value of TurboIntegrator variables to the ODBCOutput function. Refer to the example below for details.
Syntax
Expand(String);
Argument
String Any string that includes TurboIntegrator variable names enclosed in % signs.

Example
ODBCOutPut( 'TransData', Expand( 'INSERT INTO SALES ( MONTH, PRODUCT, SALES ) VALUES ( "%V0%", "%V1%",%V2% )' ) );
This example illustrates the use of the Expand function within the ODBCOutput function. The example inserts records into a relational table named Sales that consists of three columns: Month, Product, and Sales.
The Expand function converts the variables V0, V1, and V2 to their actual values within the view. Assuming that the first value in the view is 123.456, and is defined by the elements Jan and Widget
Expand( 'INSERT INTO SALES ( MONTH, PRODUCT, SALES ) VALUES ( "%V0%", "%V1%",%V2% )' )
becomes
'INSERTINTO SALES ( MONTH, PRODUCT, SALES ) VALUES ( Jan, Widget, 123.456 )'
at run time.

[rfielden]

Here is where I am at. The below statement works but throws in a text value of 'Value' into every row of Oracle table. I have tried versus quotes and the vSingle 'trick' with the value variable but then receive syntac TI error. This is very painful!

vSingle = '''';

vSql = 'INSERT INTO ak_rfiel.applix_test ( YEARS,PERIODS,BESTATES,BEMEASURES,VALUE ) VALUES (' | vSingle | Years | vSingle |', ' | vSingle | Periods | vSingle |', ' | vSingle | BeStates | vSingle | ', ' | vSingle | BeMeasures | vSingle | ', ''Value'');';
ODBCOutput('appstst',vSql);

Can't even get close using the std ODBCOutput/Insert/Expand documented process.

[John Hammond]

You want

Code: Select all

ODBCOutPut( 'appstst', Expand( 'INSERT INTO ak_rfiel.applix_test ( Years,Periods,BeStates,BeMeasures,MValue )' | ' VALUES (''%Years%'',''%Periods%'',''%BeStates%'',''%BeMeasures%'',%Value%)'));

The value being numeric does not require quotes

[rfielden]

That works sir, thanks! Can't tell you how many versions I have rec'd from Applix support that have yet to work in whole.