TM1 Forum

I'm trying to insert records of a view into a Ms access table

Prolog: odbcopen('access', 'isaac', '1234');
Data: odbcoutput('access',Expand( 'INSERT INTO table( 1,2,3,4,5,6 ) VALUES ( "%v0%", "%v1%", "%v2%", "%v3%","%v4%",%v5% )' ));

Epilog : odbcclose('access');

and I got this error .. got any idea ??

It's not fun debugging ODBCOutput. My approach is on the following lines

asciioutput('test.csv',
#odbcoutput('access',
Expand( 'INSERT INTO table( 1,2,3,4,5,6 ) VALUES ( "%v0%", "%v1%", "%v2%", "%v3%","%v4%",%v5% )' )
);
I then open test.csv in Excel and look at the SQL strings, pasting them into a query window to test if needs be. When they look back I switch the #es round.

I also don't use Expand as I don't think this does anything that enough single quotes don't do just as well [eg '(''' becomes (' ]- but that's just a preference.

Hi

I actually found the other approach easier where I have all my variables for the EXPAND statement defined first so I have all the fiddely quotes out of the way

vBU_NO=''''| vBU | '''';
vYEAR_ID=vYear;
etc

(Where the vBU_NO is a varchar2 and vYear is a number in an Orcale DB.)

Which then allows something like this in the 'Values' part of the EXPAND.

VALUES(%vBU_NO%, %vMONTH_ID%,%vYEAR_ID%,%vAsset_Type%,%vMEASURE_CODE%,%vMEASURE_DESC%,%vMEASURE_AMT%)

Also just a personal preference and works for me.

And to answer your question:
You are trying to insert a numeric value in field 5 and are missing the value for field 6.

Cheers

very tedious
if the text have single quote like 's ...

get a ETL mates. and have a happy life~