December 22, 2006, 02:24 PM
<michka>"Hidden" count field when using Sum feature
We have recently upgraded from 4.3.6 to 7.1.3 and have found an unusual addition showing up in some of our FEXES.
When using the SUM function and HOLDing the results as an ALPHA format, we are seeing a new field showing up that contains the number of records. Here is an example:
TABLE FILE GROUPS
SUM GROUPNM
ON TABLE HOLD AS GRPHOLD FORMAT ALPHA
END
-RUN
-READ GRPHOLD &GRPNM.A30.
The result is that &GRPNM contains the following value - '000010HIGHLAND MARKET'.
As we come across the issues, we are modifying the -READ statement to read the first 6 characters into a field that we don't use.
I am just curious is anyone else has experienced this and if there is a command or setting that can be used to turn off that "counting" function.
December 22, 2006, 03:09 PM
Tony AMichka,
I take it that your data source is SQL based?
Use SQL SQLMSS SET VARCHAR OFF for MS SQL to get the old behaviour back
T
December 29, 2006, 10:50 AM
<michka>Tony,
Thanks for the tip. My next question is how & where do I put this statement. I have tried it in a few different spots and I get errors.
Also, will this work if we use a combination of "TABLE FILE" statements and SQL pass-thru code ?
December 29, 2006, 11:42 AM
RichHHi Michka,
Normally these types of commands are added to the profiles, like the global server profile, edasprof.prf if you want the changes in effect for everyone.
Rich
January 05, 2007, 11:44 AM
<michka>Thanks Rich. That makes sense. I will ask our Admin to add this statement to the edasprof.prf.
January 05, 2007, 11:51 AM
hammo1jThe numbers are non blank lengths of variable fields.
wf generally works better with fixed length fields, this being the way it way designed. The implementation of variable length data is quite clunky - this is one thing RDBMSs do better.
January 05, 2007, 11:56 AM
hammo1jYou'll have to remake your synonyms once you put VARCHAR in EDASPROF.
Also would suggest ENGINE SQLORA SET ORACHAR VAR as well as ENGINE SQLORA SET VARCHAR OFF
Perverse I know but FOCUS managed JOINs to RDBMS will stop working otherwise!