too much whitespace in your strings

Have you ever gone to a QBE and typed in what you thought was a great Query By Example and not been given any results…  Then after a bunch of searching you find that you’ve loaded all of your data with a heap of whitespace at the end of the string.

So as above, I use and * and I see everything.  Doh, using EXCEL and SQLDeveloper to load a bunch of data and I’ve got all my fields padded.

I then generate the following SQL to trim all of the NCHAR fields:

SELECT 'update crpdta.' || table_name || ' set ' || column_name || ' = trim(' || column_name ||' );' from all_tab_columns
where table_name in ('F55ACCRM','F55ACCCH','F55ACCRC') and data_type = 'NCHAR';

This creates a pile of updates to mod the data:

update crpdta.F55ACCAM set AMJOBN = trim(AMJOBN );
update crpdta.F55ACCAM set AMPID = trim(AMPID );
update crpdta.F55ACCAM set AMUSER = trim(AMUSER );
update crpdta.F55ACCAM set AM55ITMTY = trim(AM55ITMTY );

but only if the DD type is VARCHAR (type 20).  If it’s type 2 (CHAR), the  you will need to LPAD

See that the screen above is type 2 – CHAR / NCHAR.  Loads of wasted space!

SELECT 'update crpdta.' || table_name || ' set ' || column_name || ' = rpad(' || column_name ||', ' || data_length/2 ||' );' from all_tab_columns
where table_name in ('F55ACCAM') and data_type = 'NCHAR';

update crpdta.F55ACCRC set CA55ITMCID = rpad(CA55ITMCID, 40 );
update crpdta.F55ACCRC set CA55PAID = rpad(CA55PAID, 20 );
update crpdta.F55ACCRC set CAJOBN = rpad(CAJOBN, 10 );
update crpdta.F55ACCRC set CAPID = rpad(CAPID, 10 );
update crpdta.F55ACCRC set CAUSER = rpad(CAUSER, 10 );
update crpdta.F55ACCRC set CA55RENYR = rpad(CA55RENYR, 1 );
update crpdta.F55ACCRC set CA55DETCOR = rpad(CA55DETCOR, 1 );

Note that I’m dividing by two, because the character length is half the byte length, because there are “2 bytes for eeevvvrrryyyy char” Sung to the same tune of beach boys “Two girls for eeeevvvrrryy boy” in their legendary  Surf City song. 

Of course, JDE knows all of this.  If your pacakge build and deploy have occurred over the correct DD items, then the SQL Statement will include the correct amount of spaces.  BUT. If there are mistakes, or you’ve had to perform emergency DD surgery – then the runtime size of the data items is probably wrong. (Like me for example).