We have an Oracle 11g database here that has a table with a varchar2 field with values that sometimes contain trailing spaces. This field is part of the primary key of the table.

We use a TABLE request to write this data to a different RDBMS table with the same primary key, but we're getting PK violations because of duplicate keys!

What turns out to happen is that WebFOCUS trims the trailing spaces off these values, and this creates duplicates! Is there some option to turn off this behaviour?

To illustrate the problem, say we have an Oracle table:

?FF FOO
 FILENAME=  FOO
 FOO           FOO           A18V
 DELETION_DATE DELETION_DATE HYYMDS

Where Oracle has a PK on field FOO.
The field contains 2 records (shown in CSV format):
"FOO", "2020-04-17 11:27:00"
"FOO ", "2020-04-17 11:27:00"

Let's TABLE that:

TABLE FILE FOO
PRINT DELETION_DATE
BY FOO
END

This results in:

FOO 2020-04-17 11:27:00
    2020-04-17 11:27:00

WebFOCUS grouped the two different values for FOO as if they are the same. But they're not!

Now you can imagine what would happen were we to HOLD these results to an RDBMS table with the same primary key...
How do we differentiate between the two values?

This message has been edited. Last edited by: FP Mod Chuck,

And what if you define the FOO USAGE as A18 instead of A18V ?

Or can you create another "dummy" FOO field only to be used as in the key where you will replace the trailing spaces with something else ?
Something such as (or anything else with combining other function)

DEFINE FILE abc
FOO_K /A18V = RPAD(FOO, 18, '_');
END
TABLE FILE abc
PRINT DELETION_DATE
BY FOO_K
BY FOO
END

Those were actually things I had already tried (I could have mentioned that...). Still, thanks for the suggestions.

Changing the field definition to USAGE A18 adds MORE trailing spaces than in the original value, I end up with:

'FOO              '

I also tried replacing the trailing spaces with something else, but then WF just fills up the remainder of the length of the field with the replacement character.
Worse, it does the same to 'FOO' (the variant w/o the trailing space)!

For example, if I replace space with '#', then it turns 'FOO ' into 'FOO###############' instead of 'FOO#'. I end up with a duplicate key 'FOO###############'.

And I now also have to replace the replacement character back to a space in the target table somehow... Ooph!

And finally, since either solution would do the same to either 'FOO' or 'FOO ', I would still get a duplicate key violation in the target table.


Let's see if I can ignore those values with trailing spaces, they're probably user input errors in the first place... I'm replicating data though, so that's a somewhat dubious decision to make.

My suggestion regarding FOO_K was to add this new field as element for the key
So, instead of having only FOO as key you will have FOO_K and FOO

In below sample I end up with two different COUNTRY_K for England and Italy
Where when seats is 2 there is no # added otherwise yes

DEFINE FILE CAR
COUNTRY_V /A10V = IF SEATS EQ 2 THEN (IF COUNTRY EQ 'ENGLAND' THEN 'ENGLAND' ELSE IF COUNTRY EQ 'ITALY' THEN 'ITALY' ELSE COUNTRY) ELSE COUNTRY;
COUNTRY_K /A10V = REPLACE(COUNTRY_V, ' ', '#');
L_K       /I2   = LENV(COUNTRY_K, 'I2');
L_V       /I2   = LENV(COUNTRY_V, 'I2');
L_C       /I2   = LENV(COUNTRY  , 'I2');
END
TABLE FILE CAR
PRINT L_K
      L_V
      L_C
BY SEATS
BY COUNTRY_K
BY COUNTRY_V
BY COUNTRY
END

So, using both fields where COUNTRY_K is in a BY but NOPRINT create twos rows for England & Italy

DEFINE FILE CAR
COUNTRY_V /A10V = IF SEATS EQ 2 THEN (IF COUNTRY EQ 'ENGLAND' THEN 'ENGLAND' ELSE IF COUNTRY EQ 'ITALY' THEN 'ITALY' ELSE COUNTRY) ELSE COUNTRY;
COUNTRY_K /A10V = REPLACE(COUNTRY_V, ' ', '#');
END
TABLE FILE CAR
SUM RETAIL_COST
BY COUNTRY_K NOPRINT
BY COUNTRY
END

Have you tried:

TABLE FILE abc
PRINT DELETION_DATE
PRINT FOO/A3
END

?
Where PRINT can be SUM and A3 can be A(any number)

I'm pretty sure the underlying problem is that WebFOCUS apparently does have no concept of an alphanumeric field with values that contain trailing spaces that are not padding the field value to the full length. I would appreciate being proven wrong here.

If that is indeed the case, there is no way to fix this from within WF, as there is no way that WF can distinguish the two different values from each other, no matter how much you fiddle around with An and AnV fields.

To WF, 'FOO' and 'FOO ' in an A5 or A5V field are the same values, namely twice 'FOO ' (A5) and 'FOO' (A5V) respectively. The distinction between 'FOO' and 'FOO ' is lost as soon as WF reads the value into its matrix. That the value is written to another RDBMS (that does understand this distinction) immediately, does not seem to matter.

I was hoping for a setting somewhere to make WF keep the original value instead of either trimming off the trailing spaces or padding the value to the full field length, both of which change the value.

A possible workaround would be to also read the LENGTH() of the value from the RDBMS (using DB_EXPR) and write the output to a staging table in the target DB. After that we can construct the value back with an SQL query there using substring on the value with the given length, but holy flying spaghetti monster!

Would it be an option to store it as a hex string instead of ascii text?

quote:

...WebFOCUS grouped the two different values for FOO as if they are the same....How do we differentiate between the two values?...

Within the TABLE FILE construct maybe try something like this?

DEFINE FILE GGSALES
 WEP/A20V = IF SEQ_NO EQ 1 THEN 'FOO' ELSE 'FOO ';
 DUMMYS0RT/A21V = WEP | 'X';
END
TABLE FILE GGSALES
 PRINT SEQ_NO     
 BY    DUMMYS0RT NOPRINT  
 BY    WEP 
 IF SEQ_NO LE 2
 ON TABLE SET STYLE *
  INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/jellybean_combo.sty, $
 ENDSTYLE
END  

Same idea, storing data in a MS SQL server temporary table..

Department is a table in the AdventureWorks2017 MS SQL Server sample database.

This message has been edited. Last edited by: David Briars,

Those are some interesting results...

It appears that the trailing spaces don't get lost until I write the value to the target DB using FORMAT SQLPSTGR.

I can insert a record straight into the database that does contain trailing spaces though, so it is still something that WF is doing.

Could this be adapter-specific?
It doesn't appear to be something configurable on the adapter, unfortunately.