Database Insert

Post Reply
Neelutm1
Posts: 4
Joined: Tue Nov 13, 2018 3:16 am
OLAP Product: COGNOS TM1
Version: 10.2.2
Excel Version: 2010

Database Insert

Post by Neelutm1 » Fri Nov 30, 2018 4:39 am

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: "..."

declanr
MVP
Posts: 1590
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

Post by declanr » Fri Nov 30, 2018 8:31 am

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.

MGrain
Posts: 12
Joined: Wed Nov 15, 2017 11:36 am
OLAP Product: TM1
Version: Various
Excel Version: Various

Re: Database Insert

Post by MGrain » Fri Nov 30, 2018 2:02 pm

SCAN will help here too.

Neelutm1
Posts: 4
Joined: Tue Nov 13, 2018 3:16 am
OLAP Product: COGNOS TM1
Version: 10.2.2
Excel Version: 2010

Re: Database Insert

Post by Neelutm1 » Mon Dec 03, 2018 11:43 pm

Thank You, I will give a try.

User avatar
paulsimon
MVP
Posts: 651
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Database Insert

Post by paulsimon » Tue Dec 04, 2018 11:49 pm

Hi

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 ;

Post Reply