Does anyone know how to maintain leading zeros in a characterLength16 field?
Cognos 8.3
SQL Server
When you say leading 0's were stripped, do you mean to say that you exported a CSV out of Cognos, but then tried to open it in excel and noticed the leading 0's missing? If so, that is a known issue with excel. Please open the CSV with a text editor before opening it in excel and see if the leading 0's are there. If yes, then you need to look up the excel leading 0 issue on google etc for a workaround.
Here's one:
_hxxp://social.msdn.microsoft.com/Forums/en/isvvba/thread/aae07b39-865f-4c68-a07f-7cad2dfd6733
I am not trying to open in Excel. I produce the output file as csv (tab delimited) and when I open in notepad, the leading zeros are stripped.
I am going to make a guess ... the leading zeros you see when you run in other formats might be coming from a data format applied in the layout. The padding does not exist in the datasource and would not come through when run to CSV.
If I'm correct, then you would want to modify the query item expression to include the zero padding. Something like:
right('0000000000000000' || char([Database Layer].[EU_EQPMASFL].[EMCATG]),16)
If I'm wrong ... well ... wouldn't be the first time.
Actually not. The data filed is a login ID that is 10 characters long. About half of the ID's are in the following format:
0123456789
and the rest are:
1234567890
the customer needs the leading zero to not be stripped when exporting to csv. I can force it to display the zeros in Excel but that does not serve their needs.
How are you viewing the CSV? If it's in Excel then Excel would strip out the zeroes. Try viewing the CSV in a text editor, it's possible the zeroes may actually be there.
I am reading the file in notepad and the zeros are not there.
Take a look @ https://www-304.ibm.com/support/docview.wss?uid=swg21345122