Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Prevent trimming of trailing spaces?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Prevent trimming of trailing spaces?
 Login/Join
 
Virtuoso
posted
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,


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Expert
posted Hide Post
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)




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
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!


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Guru
posted Hide Post
Would it be an option to store it as a hex string instead of ascii text?


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Master
posted Hide Post
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,




Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
 
Posts: 822 | Registered: April 23, 2003Report This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Prevent trimming of trailing spaces?

Copyright © 1996-2020 Information Builders