Verbatim, but only keep leading spaces, remove trailing spaces

[#1]

Is there any way to use Verbatim but have it only apply to leading spaces? It seems that our ERP software recognizes leading spaces, but it removes the trailing spaces. This is causing a lot of linking issues wether or not I use Verbatim. I know that I could use Verbatim and then go and add rtrim() to every single field but that would be extremely cumbersome.

[#2]

Ok if such a thing does not exist, does anyone know if adding rtrim to the fields in the Select section auto applies that to all instances in the Load section?

[rwunderlich]

Yes. If rtrim() is used in the SELECT, the value received by the LOAD will already be trimmed.

-Rob

[#4]

Alright, thank you. That makes what I might have to do slightly less daunting. Still would be awesome if there was just a simple variable though.

[#5]

Since there is no variable to apply Rtrim to a document, does this mean I lose the ability to do say:

Select * From ...?  I am aware that you cannot do rtrim(*) and that it needs applied to every field, then every field needs to be labeled.

so the following needs done for every field selected?

Rtrim(field) as field,

I am now loading entire tables to QVD's using incremental load with the * function so that I do not need to waste space by pulling every field. This however stores based on the document so I would have to pull every field for the stored table and do rtrim(field) as field,  for every field to apply this correctly.

This just seems kind of ridiculous to have to do. Also, could applying trim functions affect index time?

[rwunderlich]

You could build an load statement in a loop that does the rtrim() for every field. But back to the orginal problem. You said your ERP system removes the trailing spaces. So you are pulling data from two sources, these tables and the ERP system that treat the same fields differently

-Rob

[#7]

Our ERP system stores whatever was entered into the field into the database. From within the ERP system however it disregards the trailing spaces when it determines the links between programs and uniqueness of fields. How would a load statement like that look? I am loading around 35 tables on my largest document, where most of them are stored into qvd's for other documents to reference.

[rwunderlich]

You could code up a script SUB like this:

SUB RtrimTable (_table)

          SET vComma=;

          SET vLoad=;

          FOR i = 1 to NoOfFields('$(_table)')

                    LET vField = FieldName($(i),data);

                    LET vLoad = '$(vLoad) $(vComma)rtrim([$(vField)]) as $(vField)';

                    SET vComma=,;

          NEXT i

          $(_table)_temp:

          NoConcatenate

          LOAD

                    $(vLoad)

          RESIDENT $(_table)

          ;

          DROP TABLE $(_table);

          RENAME TABLE $(_table)_temp TO $(_table);

END SUB

And then assuming your loaded table is named "data", call the sub like this:

CALL RtrimTable('data')

That will reload table data with an rtrim on every field and replace the data table.

Attached is an example.

-Rob

http://robwunderlich.com

[#9]

Is there a way to do this so it does not apply to date fields? doing this seems to screw the data up.