how to get db2 without any appended values

select rtrim(char(PKG_AGR_IDR)),rtrim(char(STA_DTE)) 
from test FETCH FIRST 10 ROW ONLY


"0010000010. 2014-03-14"
"0010000010. 2014-03-14"

I need data as below:

0010000010 2014-03-14

I am planning to write a script to do rtrim(char(fieldname)) is there any combination of functions with which i can get proper output for both fields.

Solution

One might presume that the OP might have been written more like the following, to better describe the scenario:

Some background about what is being done will be included, such that later references [such as to field_name] will be previously-explained rather than having to be intuited by a reviewer.

The intention is to enable dynamically generating an SQL SELECT statement that will retrieve a character-representation of the data from the columns of a specified TABLE. Given the DDL create table THE_SCHEMA.TEST ( PKG_AGR_IDR NUMERIC(10, 0), STA_DTE DATE ) and given the following DML used to populate that TABLE with a sample-row insert into THE_SCHEMA.TEST VALUES(10000010. '2014-03-14'), what is desired is to obtain a result-set [limited to the first ten rows for the purpose of testing] that would include the data from each column [of the TABLE named TEST in THE_SCHEMA] as VARCHAR data, as produced from the following query that would have been generated from the metadata stored in the SYSCOLUMNS catalog VIEW:
select rtrim(char(PKG_AGR_IDR)),rtrim(char(STA_DTE)) from test FETCH FIRST 10 ROW ONLY

The single expression generated as 'RTRIM(CHAR(' CONCAT COLUMN_NAME CONCAT '))' from the SYSCOLUMNS data, as seen twice in the query noted just prior, seems unable to provide desirable results when applied to a column-name irrespective the value of the DATA_TYPE of the COLUMN_NAME being formatted by that character-expression. Specifically, for example, the result of the dynamically generated query select RTRIM(CHAR(PKG_AGR_IDR)), RTRIM(CHAR(STA_DTE)) from THE_SCHEMA.TEST FETCH FIRST 10 ROW ONLY produces the following output:

0010000010. 2014-03-14

However the expected\desired output would be:

0010000010 2014-03-14

Is there any expression like RTRIM(CHAR(column_name)) that will function for all the columns in a TABLE, to obtain the data as character-string, regardless the data-type of the columns, whether they be numeric, varchar or date?

Yet even with that more complete description of the scenario\background:

The claims about what is the output from the original expression are unexpected from the CHAR scalar effecting Decimal to Character casting, at least for the DB2 for i SQL for which the zero-scale packed decimal (DECIMAL) and zoned decimal (NUMERIC) SQL data types are represented without a decimal separator [aka decimal point] despite the optional decimal-character as the second argument. As well the CHAR scalar omits leading zeroes when casting from numeric. Thus the DB2 for i SQL would have obtained a result of the string '10000010' rather than either of '0010000010.' or '10000010.'

I suppose the issue may be specific to the DB2 for Z or the DB2 LUW, and perhaps this topic was incorrectly tagged with DB2 for i? Or perhaps there may be a[n unstated] concern about an apparent incompatibility betwixt the DB2 variants? Yet having read the documentation, the described results seem contrary to what is documented, so I suspect the actual problem for the OP may be due to having encountered a defect [in whatever is the unstated variant of the DB2 and release level that is being used].?

I do not expect that there will be any one expression that will perform what is desired for each of NUMERIC, VARCHAR, and DATE [nor for each of INTEGER, SMALLINT, NUMERIC, DECIMAL, VARCHAR, and DATE]. For omission of the decimal point, the DB2 for i SQL is probably the most like what is expressed as desired, but then the leading zeroes are always trimmed http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzscachar.htm

... Leading zeros are not returned. Trailing zeros are returned. If the scale of decimal-expression is zero, the decimal character is not returned. ...

The DB2 LUW SQL seems at least somewhat incoherent with regard to the topic of leading zeroes, as example 6 suggests none and then example 7 shows they are there, but like the above doc reference, clearly there should be no leading zero characters http://www.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000777.html

... Leading zeros are not included. Trailing zeros are included. ... If the scale of decimal-expression is zero, the decimal character is not returned. ...

I did not research a DB2 for Z doc link.

I would expect that the solution will entail using a CASE expression, perhaps for the DATA_TYPE value. That is what I did coding something similar, though I just used VARCHAR casting scalar and did not do any trimming. However my requirement for CASE was not about keeping leading zero characters, instead mostly for choosing the correct decimal-separator character. And because the second argument decimal-character [for CHAR or VARCHAR] is disallowed for the INTEGER numeric types [sqlcode -171 aka SQL0171], the CASE expression for just the numeric types would be sufficiently resolved using just the following expression CASE WHEN DATA_TYPE IN ('INTEGER', 'SMALLINT', 'BIGINT') THEN ', ' concat DecSep concat ')' ELSE ')' appended to the 'VARCHAR(' concat where DecSep was the one-character variable having either the comma or period as the chosen decimal separator. Yet because the second argument [for CHAR or VARCHAR] is specific to the data type of the first argument, the character and date\time data types had their own CASE expression CASE WHEN DATA_TYPE IN ('DATE', 'TIME') THEN ', ' concat StdFmt concat ')' ELSE ')' appended to the 'VARCHAR(' concat where StdFmt was the three-character variable having one of the standards format specifications of ISO, USA, EUR, or JIS.