DB2 CLP - formatting text columns

[relaxedrob]

Hi All!

I am using the DB2 CLP tool and want to format my text columns in a
query to truncate the results of a wide text field - 50 char field but
I want to display only the first 10 chars.

My base query:
SELECT EPT620.FORM_TYPE, EPT620.CREATED_BY, EPT620.STATUS,
EPT634.SURNAME \
FROM DB2ADMIN.EPT620 EPT620, DB2ADMIN.EPT634 EPT634 \
WHERE EPT634.UNPAID_LEAVE_FORM_ID = EPT620.FORMID

1)
How do I specify colum formatting?

2)
Where do I find help on this? The help system here:
http://publib.boulder.ibm.com/infoce...help/index.jsp
is so confusing!

Thanks for any assistnace!

Rob
:)

[Jan M. Nelken]

re********@optushome.com.au wrote:

Hi All!

I am using the DB2 CLP tool and want to format my text columns in a
query to truncate the results of a wide text field - 50 char field but
I want to display only the first 10 chars.

My base query:
SELECT EPT620.FORM_TYPE, EPT620.CREATED_BY, EPT620.STATUS,
EPT634.SURNAME \
FROM DB2ADMIN.EPT620 EPT620, DB2ADMIN.EPT634 EPT634 \
WHERE EPT634.UNPAID_LEAVE_FORM_ID = EPT620.FORMID

1)
How do I specify colum formatting?

2)
Where do I find help on this? The help system here:
http://publib.boulder.ibm.com/infoce...help/index.jsp
is so confusing!

Thanks for any assistnace!

Rob
:)

1) For a base query:

SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA='DB2ADMIN'

which returns TABNAME as VARCHAR(128) if I want to display only 8 characters
of table name - and as a bonus get warning when actual name is longer,
I would use:

SELECT CAST(TABNAME AS CHAR(8)) FROM SYSCAT.TABLES WHERE TABSCHEMA='DB2ADMIN'

and get this output:

1
--------
CL_SCHED
DEPARTME
SQL0445W Value "DEPARTMENT" has been truncated. SQLSTATE=01004

EMP_ACT
EMP_PHOT
SQL0445W Value "EMP_PHOTO" has been truncated. SQLSTATE=01004

EMP_RESU
SQL0445W Value "EMP_RESUME" has been truncated. SQLSTATE=01004

EMPLOYEE
IN_TRAY
ORG
PROJECT
SALES
STAFF

11 record(s) selected with 3 warning messages printed.
2) I derived that from
http://publib.boulder.ibm.com/infoce...help/index.jsp

What is so confusing about it?
Jan M. Nelken