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     [SOLVED] Character field gets shortened for no apparent reason

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Character field gets shortened for no apparent reason
 Login/Join
 
Silver Member
posted
Hi Fellow WF-developers,

I have a field which I use in my report it has as fieldtype: A16

But when I run the report it gets shortened 2 only three characters.
It has nothing to do with the column-size.
It is just shortening it for no apparent reason.

Example.

If in SQL the field shows the following values:
00001256789
00002567456

It wil show in DS just 789 and 456.

Anyone familiar with this issue?
I have searched the forum and the help file but with no succes.

Thanx in advance,

Nordin

This message has been edited. Last edited by: Kerry,


7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
 
Posts: 32 | Location: Utrecht, Holland | Registered: December 10, 2008Report This Post
Virtuoso
posted Hide Post
Could you show the definition of this field in the MASTER file?
Could there be a DEFINE on the field with the same fieldname that takes the last 3 characters?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Silver Member
posted Hide Post
Hi Danny,

No there is no define field used here.
We have a policy that we don't temper with the masterfiles. If we want certain fields changed, we do it either in an SQL view or in the report itself.

Field that gives us a headache Smiler in the master file:

FIELDNAME=OBJECTID, ALIAS=OBJECTID, USAGE=A16, ACTUAL=A16, FIELDTYPE=R,


Any ideas?

Thanx,

Nordin


7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
 
Posts: 32 | Location: Utrecht, Holland | Registered: December 10, 2008Report This Post
Platinum Member
posted Hide Post
Nordin,

The data that you shows appears to be integer, yet the MFD says it's an A16. This leads me to suspect that the MFD is out of synch with the underlying SQL table.

Also, the FIELDTYPE=R raisses warning bells. FIELDTYPE=R is only applicable under a very narrow set of circumstances - typically when the underlying database column is an identity column.

If these thoughts do not help, suggest you turn on SQL tracing, then post that along with the full MFD & SQL table definition - mybe something will jump out at us.

EricH
 
Posts: 164 | Registered: March 26, 2003Report This Post
Silver Member
posted Hide Post
Hi Erich,


Sorry for my late response, but the DB Admin is currently not available.

So I give you mine research into source (Sql.)

In SQL the datatype is (nvarchar(16), null)
the collation is SQL_Latin1_General_CP1_CI_AS

In my opinion nothing unusual.

But in DS it still doesn't give more then three characters?

Any ideas anybody?


7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
 
Posts: 32 | Location: Utrecht, Holland | Registered: December 10, 2008Report This Post
Virtuoso
posted Hide Post
Are you quite sure that there is no other masterfile with the same name on your system that gets used first?
You can find that out by issuing the command 'WHENCE mastername MASTER' just before you would do your TABLE. It will then show you which master file will be used for the query so you can find out if it is the correct one.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
Otherwise, try to find out what sql is being sent to the DBMS and see if that's correct. Use the following commands in your fex to accomplish that:
-* Capture sql output
SET TRACEOFF=ALL
SET TRACEON=SQLAGGR//CLIENT
SET TRACEON=STMTRACE//CLIENT
SET TRACEON=STMTRACE/2/CLIENT
SET TRACESTAMP=OFF
SET TRACEUSER=ON

SET XRETRIEVAL=OFF
Hope this helps ...


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Silver Member
posted Hide Post
Hi GamP,

I Captured the SQl Output:


"FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED
SELECT
TOP 100 T1."MSESSIEID",
T1."SOURCETYPE",
T1."MEET_DATUM",
T1."MEET_TIJD",
T1."AutoID",
T1."PATIENTNR",
T1."INPUTDATUM",
T1."INPUTTIJD",
T1."SOURCE_ID",
T1."VALUETYPE",
T1."METING_ID",
T1."VALUES",
T1."GEKOPP_ID",
T2."KOPP_ID",
T2."OBJECTID",
T2."MSESSIEID",
T2."PATIENTNR",
T2."EERSTEDATU",
T2."EERSTETIJD",
T2."STATUS",
T3."PLANNR"
FROM
EZis_Report.dbo.METINGEN_METINGEN T1,
EZis_Report.dbo.METINGEN_MEETSESS T2,
EZIS_Report.dbo.OPNAME_OPNAME T3
WHERE
(T2."MSESSIEID" = T1."MSESSIEID") AND
(T3."PLANNR" = T2."OBJECTID") AND
(T1."VALUES" LIKE '%MUST%') AND
(T2."KOPP_ID" = 'OPN');
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0"


7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
 
Posts: 32 | Location: Utrecht, Holland | Registered: December 10, 2008Report This Post
Expert
posted Hide Post
Wouldn't a column defined as nvarchar(16) be an A16V column in the WebFOCUS master?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Silver Member
posted Hide Post
Even If I change the data Type 2 A16V
That doesn't change the output.
Still three Characters.


7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
 
Posts: 32 | Location: Utrecht, Holland | Registered: December 10, 2008Report This Post
Expert
posted Hide Post
USAGE=A16V, ACTUAL=A16.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Silver Member
posted Hide Post
USAGE=A16V, ACTUAL=A16.

It still gives in DS the same results.


7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
 
Posts: 32 | Location: Utrecht, Holland | Registered: December 10, 2008Report This Post
Virtuoso
posted Hide Post
A16 or A16V does not make any difference in retreiving data.
But, I always like to make things as simple as possible in situations like this.
So, the request apparently is a join of 3 tables.
What I would like to see is what happens when you just access the one table that holds the problem field, so in this case a report that accesses only table METINGEN_MEETSESS.
Just print the OBJECTID field, let's say 100 records. See if that's ok or not. Then take it from there and try to find what the problem is.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Silver Member
posted Hide Post
If I only ad table METINGEN_MEETSESS in my report.
It shows the whole field correctly.

If I Join METINGEN_MEETSES like in the example above and only display the fiels from METINGEN_MEETSESS
It works correctly.

But if I print the folowing, then it goes wrong again.
To me, it doesnt make any sense.
But then agian, I am no Expert Smiler



TABLE FILE METINGEN_METINGEN
PRINT
METINGEN_METINGEN.METINGEN_METINGEN.MSESSIEID
METINGEN_METINGEN.METINGEN_METINGEN.SOURCETYPE
METINGEN_METINGEN.METINGEN_METINGEN.MEET_DATUM
METINGEN_METINGEN.METINGEN_METINGEN.MEET_TIJD
METINGEN_METINGEN.METINGEN_METINGEN.AUTOID
METINGEN_METINGEN.METINGEN_METINGEN.PATIENTNR
METINGEN_METINGEN.METINGEN_METINGEN.INPUTDATUM
METINGEN_METINGEN.METINGEN_METINGEN.INPUTTIJD
METINGEN_METINGEN.METINGEN_METINGEN.SOURCE_ID
METINGEN_METINGEN.METINGEN_METINGEN.VALUETYPE
METINGEN_METINGEN.METINGEN_METINGEN.METING_ID
METINGEN_METINGEN.METINGEN_METINGEN.VALUES
METINGEN_METINGEN.METINGEN_METINGEN.GEKOPP_ID
J0.METINGEN_MEETSESS.KOPP_ID
J1.OPNAME_OPNAME.PLANNR
J0.METINGEN_MEETSESS.OBJECTID
J0.METINGEN_MEETSESS.MSESSIEID
J0.METINGEN_MEETSESS.PATIENTNR
J0.METINGEN_MEETSESS.EERSTEDATU
J0.METINGEN_MEETSESS.EERSTETIJD
J0.METINGEN_MEETSESS.STATUS
WHERE METINGEN_METINGEN.METINGEN_METINGEN.VALUES CONTAINS 'MUST';
WHERE J0.METINGEN_MEETSESS.KOPP_ID EQ 'OPN';
WHERE READLIMIT EQ 100
WHERE RECORDLIMIT EQ 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
GRAPHCOLOR='GREEN',
$
TYPE=REPORT,
OBJECT=MENU,
COLOR='WHITE',
HOVER-COLOR=RGB(66 70 73),
BACKCOLOR=RGB(102 102 102),
HOVER-BACKCOLOR=RGB(218 225 232),
BORDER-COLOR='WHITE',
$
TYPE=REPORT,
OBJECT=STATUS-AREA,
COLOR='WHITE',
BACKCOLOR=RGB(102 102 102),
$
TYPE=REPORT,
OBJECT=CURRENT-ROW,
HOVER-BACKCOLOR=RGB(218 225 232),
BACKCOLOR=RGB(200 200 200),
$
TYPE=REPORT,
OBJECT=CALC-AREA,
COLOR='WHITE',
BACKCOLOR=RGB(102 102 102),
$
ENDSTYLE
END


7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
 
Posts: 32 | Location: Utrecht, Holland | Registered: December 10, 2008Report This Post
Virtuoso
posted Hide Post
Ok. Da's dan duidelijk.
The join from the first to the second table is probably causing this.
Check to see if PLANNR and OBJECTID have exactly the same format and size.

Think on it, both fields will have to have exactly the same format - and contents - to be able to do a join correctly. So if the formats are indeed exactly the same,check the contents of noth fields. It would not surprise me if they are indeed quite different, apart from the few that do match.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
quote:
...If in SQL the field shows the following values:
00001256789
It wil show in DS just 789 and 456.
...


It appears it's skipping the first 8 characters, and displaying the final 8 (which happen to consist of 3 digits followed by 5 blanks).
00002567456
^^^^^^^^********
Given the way HTML treats blanks, that will look like just 3 characters, but View Source would show the trailing blanks.

I suggest you HOLD the result and verify the usage and actual generated for the field in question:
TABLE ...
ON TABLE HOLD
END
?FF HOLD 
-EXIT
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Silver Member
posted Hide Post
If I run it with "ON TABLE HOLD
END
?FF HOLD
-EXIT"

I get the following result.
By the way HTML, PDF or Excel.. show the same results (three characters.)

0 NUMBER OF RECORDS IN TABLE= 50 LINES= 50
FILENAME= HOLD
MSESSIEID E01 A10
SOURCETYPE E02 A1
MEET_DATUM E03 HYYMDs
MEET_TIJD E04 A8
OBJECTID E05 A16V
MSESSIEID E06 A10
PATIENTNR E07 A13
EERSTEDATU E08 HYYMDs
EERSTETIJD E09 A5
STATUS E10 A1


7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
 
Posts: 32 | Location: Utrecht, Holland | Registered: December 10, 2008Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by GamP:
Ok. Da's dan duidelijk.
The join from the first to the second table is probably causing this.
Check to see if PLANNR and OBJECTID have exactly the same format and size.

Think on it, both fields will have to have exactly the same format - and contents - to be able to do a join correctly. So if the formats are indeed exactly the same,check the contents of noth fields. It would not surprise me if they are indeed quite different, apart from the few that do match.


I am quite sure the join is fine see, my previous post August 29, 2012 11:07 AM.


7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
 
Posts: 32 | Location: Utrecht, Holland | Registered: December 10, 2008Report This Post
Virtuoso
posted Hide Post
Syntactically the join may very well be ok. That does not mean it will work ok.
Joining things is not a syntactical job, it's totally dependant on the formats of the fields and the data in them. These things will have to be exactly the same to allow the join to work correctly.
Which is why I asked to look at the field definitions to make sure they are the same and to check the data in the individual tables to see if they match.
Thusfar I have only read that reading from this one table only will produce the correct values.
Now take the next steps - check formats and values in the other table.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Platinum Member
posted Hide Post
Two questions:

1) If you run the SQL that WebFOCUS generates

SELECT
TOP 100 T1."MSESSIEID",
T1."SOURCETYPE",
etc
FROM
EZis_Report.dbo.METINGEN_METINGEN T1,
EZis_Report.dbo.METINGEN_MEETSESS T2,
EZIS_Report.dbo.OPNAME_OPNAME T3
WHERE
etc
  


directly in SQL Server Management Studio do you get the correct results?

If the answer to that question is "Yes", then

2) Do you still have the FIELDTYPE=R, in the field definition for your MFD

If the answer to that question is "Yes", then remove the FIELDTYPE parm and try again.


EricH
 
Posts: 164 | Registered: March 26, 2003Report This Post
Silver Member
posted Hide Post
Thanx everyone, for the input.

Weekend has begon for me, so my offical testing and reply will come monday.

Have a nice weekend everyone!


7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
 
Posts: 32 | Location: Utrecht, Holland | Registered: December 10, 2008Report This Post
Silver Member
posted Hide Post
Thanx Eric and Gamp,

To summarize, yes in SQL I get the correct output. Removing Fieldtype= R has no impact on the output.

And the join is still correct.


When I was trying your sollutions. I was commenting a lot of lines.

But suddenly I found the evil-doer Smiler

If I place this Where statement in my report: "WHERE METINGEN_METINGEN.METINGEN_METINGEN.VALUES CONTAINS 'MUST';"

I get three Characters in my OBJECTID field.
If I comment the WHERE statement, OBJECTID is shown correct.

I don't understand the connection between those two, in SQL I get the correct results with the Where included.


But From here I Can move on. I think I will write my hold files in different stages. So that it wont interfere
with each other.

Everyone thanx!!


7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
 
Posts: 32 | Location: Utrecht, Holland | Registered: December 10, 2008Report 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     [SOLVED] Character field gets shortened for no apparent reason

Copyright © 1996-2020 Information Builders