BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Streamline and extend SAP BusinessObjects software 
Streamline and extend SAP BusinessObjects software (Opens a new window)  

General Notice: No events within the next 45 days.

Using format option in to_char function (Data Integrator)


 
Search this topic... | Search DI: Designer and Job Design... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Author Message
LeslieMarieArensdorf
Forum Member
Forum Member



Joined: 02 Nov 2009

Posts: 1



PostPosted: Fri Oct 01, 2010 12:57 pm 
Post subject: Using format option in to_char function (Data Integrator)

When use the following format, I am getting inconsistent results:

to_char(ACAM ,'9G,999G,999G,999D.99')

It does not seem to work when the acam has 3 digits and 2 decimals

-825.51 converts to 825.51 - it is dropping the sign.

-50000.00 converts to -50,000.00 - which is correct.
-99.32 converts to -99.32 - which is correct.

I am using this in a DI job that loads a sqlserver 2008 table.

Please help.
Back to top
Nemesis
Forum Associate
Forum Associate



Joined: 08 Jun 2004

Posts: 981
Location: London, England


flag
PostPosted: Mon Oct 04, 2010 4:14 am 
Post subject: Re: Using format option in to_char function (Data Integrator

I've just tried the format you detail using Data Services 12.2.1.3 loading SQL Server 2005 tables and get exactly the same results.

I tried altering the format but if you have three digits before the decimal point you always lose the minus sign.

This looks like a bug to me.

_________________
Contracting in London again.
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Tue Oct 05, 2010 4:41 am 
Post subject: Re: Using format option in to_char function (Data Integrator

Actually, I find the format string you are using a bit odd. Why do you want to use a format string like '000..00' and not '000.00'. You said it should be two dots, well you said it should be a decimal separator D and a dot.

So I wonder if that bug faces because of that. In other words, what does happen with

Code:
to_char(ACAM ,'9G999G999G999D99')


I haven't tested it yet, I hope I am not completely wrong.

_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
Nemesis
Forum Associate
Forum Associate



Joined: 08 Jun 2004

Posts: 981
Location: London, England


flag
PostPosted: Thu Oct 07, 2010 3:02 am 
Post subject: Re: Using format option in to_char function (Data Integrator

Okay, using the value of -500.21 I tested the format '9G,999D.99' which results in a value of 500.21. I then tried Werners' suggestion of '9G,999.99' which resulted in -500.
So either way, there's a problem. Also, reading the 'to_char' documentation in the technical manuals indictaes that the format '9D.99' shouldn't result in 9..9. The D is to let the format editor know that the next character is the decimal point indicator so '9D.99' should result in 9.9.

_________________
Contracting in London again.
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Thu Oct 07, 2010 4:45 am 
Post subject: Re: Using format option in to_char function (Data Integrator

Okay, thanks. So I mixed it up with the Oracle syntax and was wrong. That's what I feared, sorry for that.
_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0559 seconds using 17 queries. (SQL 0.0032 Parse 0.0356 Other 0.0171)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo