To demonstrate this concept below I have used the application users description column in applsys.fnd_user which is a varchar2(240)...
SELECT fu.user_id user_id, fu.user_name user_name, fu.description description FROM applsys.fnd_user fu;In most cases when you write a report you substring the column to the size of the column in your report. Whilst sub stringing the column value is the easy solution and in your mind acceptable, you should remember your reports are aimed at end users and wouldn’t it look more professional if you added three (3) full stops to a sub stringed value to clearly indicate the column has been truncated.
SELECT fu.user_id user_id, fu.user_name user_name, substr(fu.description, 1, 30) decode(sign(length(fu.description) - 30), 1, '...') description FROM applsys.fnd_user fu;If your report column size is 30, set the two (2) values in the example SQL to 27, that is 27 characters plus 3 for the full stops.
A very simple piece of code but what a difference. You should consider using this for any user report where a data column that has been truncated.
Remember: “Always make life easier for your target user”
Last update: Mar 2012