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,

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?

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 in the master file:

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


Any ideas?

Thanx,

Nordin

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

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?

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.

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 ...

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"

Wouldn't a column defined as nvarchar(16) be an A16V column in the WebFOCUS master?

Even If I change the data Type 2 A16V
That doesn't change the output.
Still three Characters.

USAGE=A16V, ACTUAL=A16.

USAGE=A16V, ACTUAL=A16.

It still gives in DS the same results.

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.

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



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

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.

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

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

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.

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.

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

Thanx everyone, for the input.

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

Have a nice weekend everyone!

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

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!!