Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to add zeros to a character type column ?

Status
Not open for further replies.

NicolaTesla1

Programmer
Feb 9, 2009
37
CA
Hi,

We use Report Studio 8.3
We need to format a column of type "character" in our list.

The column has 5 to 7 characters.

We want to format the column in order to ADD "zeros" when less than 7 characters.

For example a column with the value:
12345
would become
0012345

Is this possible and how ?

Thanks !
 
Simple, use the character_length function to determine the length of the string. Depending on the outcome use concatenate function ('||') to add the zeroes:

Code:
CASE CHARACTER_LENGTH([SOMEFIELD])
WHEN 5 THEN '00'||[SOMEFIELD]
WHEN 6 THEN '0'|| [SOMEFIELD]
............
............
ELSE NULL
END

Ties Blom

 
Thanks blom 0344 !

I will try it and let everyone know.

Just hope i wont have any strange error messages as for when i tried something else before for this same problem.

I used the function
cast([DataItem]; INTEGER)
or
cast([DataItem]; NUMBER)

When i validated the report there were no errors but when executing the report i got an error message. I noticed that when in the expression editor, there was a small red exclamation mark next to the name of the cast function.

Could that mean that because we are using packages against cubes coming from BW we cannot use the cast function ??

More information soon.

:)
 
Possibly, you should check if CASE can be used with Cube packages. I recall that you should If Then Else instead.

Ties Blom

 
Hello to all,

Just for your information:

This is not possible for our team as for many functions are unable to us and have exclamation marks ...
this includes character_length, cast, extract etc ...

I managed to use the key column instead and i am now having what expected.

Good day to all !
 
The difference in the use of functions lies in whether you are using a relational / dimensional package. Relational uses SQL functions, Dimensional MDX types..

Ties Blom

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top