TRUNC(DECIMAL_COLUMN_NAME,0)

dataservices job design
1

Hi

I was trying to apply the TRUNC function for a decimal(18,2) column. My aim is to just get the whole number without any decimals and store it as VARCHAR
DECIMAL_COLUMN_NAME[decimal(18,2)] --> TRUNC(DECIMAL_COLUMN_NAME,0) --> DECIMAL_COL_NAME_VCHAR[varchar(18)]

I expect like
1234.98 --> TRUNC(1234.98,0) --> 1234 in a varchar column

But I am getting the result as 1234.98.

Amazingly this function works all fine if I have the TARGET Column Datatype as INTEGER instead of VARCHAR

Is there any known bug in this?

I am using 11.7.3

ganeshxp :us: (BOB member since 2008-07-17)

2

I would expect the integer datatype to work as an integer cannot store decimal points so it just strips them off.

It looks like the trunc() function simply changes all decimanl positions to zeroes but keeps the values.

If you simply make the make the datatype in the query transform an integer but keep the target column as a varchar, it should work fine with the trunc() function.

DECIMAL_COLUMN_NAME[decimal(18,2)] --> TRUNC(DECIMAL_COLUMN_NAME,0) as int --> DECIMAL_COL_NAME_VCHAR[varchar(18)]

djordan :ireland: (BOB member since 2006-03-01)

3

Yup that is what in fact I do.

But is that what really intended action of a TRUNC function?

Hope we know the TRUNC() in Oracle works different as I have expected.

ganeshxp :us: (BOB member since 2008-07-17)

4

Is the omnipresent buddy ready to answer this??!!!

ganeshxp :us: (BOB member since 2008-07-17)

5

Yes, back from windsurfing and still alive. Almost.

I think the initial chain of conversions sums up the problem neatly

DECIMAL_COLUMN_NAME[decimal(18,2)] --> TRUNC(DECIMAL_COLUMN_NAME,0) --> DECIMAL_COL_NAME_VCHAR[varchar(18 )]

What is the datatype of the DECIMAL_COLUMN_NAME? decimal.
What is the returning datatype of the TRUNC() function? decimal!!!
Now you assign this decimal value to a varchar column, hence the decimal-to-varchar code kicks in.

My statement would be that

trunc(1234.98,0) = 1234.00 (decimal)
decimal-to-varchar conversion is using the format 99999990D.000000 and therefore I would expect the value ‘1234.00’ in the varchar.

To answer the question, what I would do is
ltrim_blanks(to_char(trunc(1234.98, 0), ‘999999999999’))

To proof I have created a job with a script:


print(ltrim_blanks(to_char(trunc(1234.98, 0), '999999999999')));
print(trunc(1234.98, 0));

Result is

1234
1234.000000

Werner Daehn :de: (BOB member since 2004-12-17)

6

Hmm doing a lot of windsurfing. Great. And glad that you are back. Thought you escaped outta here :nonod:

Wow that small piece of internal conversion do make a lot of sense. Great of sharing that info.

Thanks about that.

ganeshxp :us: (BOB member since 2008-07-17)

7

$LOC_NUMBER = 1234.98;
print( word_ext($LOC_NUMBER,1,’.’));

PRINTFN 1234
JOB Job <JOB_DUMMY> is completed successfully.

data_guy :us: (BOB member since 2006-08-19)