Hi Team,
We are on tm1 10.2.2, I am trying to insert cube values in to SQL table using ODBCOUTPUT option.
Data in one of the field value has a special char like (Women's Special) and it is failing to load.
How do you handle special char when inserting data in to SQL server.
Thanks in advance.
this is my insert statement
SQLInsert = EXPAND ('INSERT INTO [TM1].[BudgetForecast_CIRC_Stage] (Version,ProductCode,TitleVersion,IssueCode,Channel, MeasureType, Qty) VALUES (''%vVersion%'', ''%vTitle%'',''%vTitleVersion%'', ''%vIssue%'',''%vChannel%'',''%vCircMeasure%'', %vValue% )');
statement dailed to laod -[A03955] MEN'S STYLE","
"Budget","RLS","[A03955] MEN'S STYLE","201803","[AU5010] Subscriptions Mail Paid Regular","Sales","627",Data Source line (27) Error: Data procedure line (28): Error executing SQL query: "..."
Database Insert
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Database Insert
This isn't a TM1 thing as such, it's a SQL thing. If you try and do an insert statement in SQL directly you need to use a double apostrophe e.g. MEN''S STYLE in the insert statement and it will get into the database as you want it displayed MEN'S STYLE.
This is particularly common with names etc - this is why in the first instance its good practice to avoid special characters in all systems but that's not always practical and TM1 being at the end or middle of the chain means it often has to put up with and resolve the issue.
To do this you take your variable/s that might have offending characters. Create a loop and check each character with SUBST. If the character is an apostrophe replace it with 2 apostrophes, keep looping until the variable is replaced with your checked variable. Then your insert statement will work.
This is particularly common with names etc - this is why in the first instance its good practice to avoid special characters in all systems but that's not always practical and TM1 being at the end or middle of the chain means it often has to put up with and resolve the issue.
To do this you take your variable/s that might have offending characters. Create a loop and check each character with SUBST. If the character is an apostrophe replace it with 2 apostrophes, keep looping until the variable is replaced with your checked variable. Then your insert statement will work.
Declan Rodger
-
- Posts: 16
- Joined: Wed Nov 15, 2017 11:36 am
- OLAP Product: TM1
- Version: Various
- Excel Version: Various
Re: Database Insert
SCAN will help here too.
-
- Posts: 19
- Joined: Tue Nov 13, 2018 3:16 am
- OLAP Product: COGNOS TM1
- Version: 10.2.2
- Excel Version: 2010
Re: Database Insert
Thank You, I will give a try.
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: Database Insert
Hi
Here is some sample TI
Here is some sample TI
Code: Select all
# Delete Bad Character from line
vText = ;
vBadCh = '' ;
vPos = Long( vText ) ;
WHILE( vPos > 0 ) ;
vPos = vPos - 1 ;
vCh = subst( vText , vPos , 1 ) ;
IF( vCh @= vBadCh ) ;
vText = DELET( vText , vPos , 1 ) ;
ENDIF ;
END ;
# Replace Character in line
vText = ;
vFindCh = '/' ;
vReplaceCh = '_' ;
vNewText = '' ;
vLen = Long( vText ) ;
vPos = 0 ;
WHILE( vPos < vLen ) ;
vPos = vPos + 1 ;
vCh = subst( vText , vPos , 1 ) ;
IF( vCh @= vFindCh ) ;
vCh = vReplaceCh ;
ENDIF ;
vNewText = vNewText | vCh ;
END ;
vYourVar = vNewText ;
# Replace String in Line
vText = ;
vFindText = 'abc' ;
vReplaceText = 'cba' ;
vFindLen = LONG( vFindText ) ;
vLen = Long( vText ) ;
vPos = SCAN( vFindText , vText ) ;
WHILE( vPos > 0 ) ;
vLeftText = subst( vText , 1 , vPos - 1 ) ;
vRightText = subst( vText , vPos + vFindLen , 255 ) ;
vText = vLeftText | vReplaceText | vRightText ;
vPos = SCAN( vFindText , vText ) ;
END ;
vYourVar = vText ;