When I add columns with sorting, WebFocus is eliminating records. The table below should include 200TIP5-1-39.

TASK_ID      TASK_NAME
200TIP5-1-2  NameForTask200TIP5-1-2 
200TIP5-1-20 NameForTask200TIP5-1-20 
200TIP5-1-21 NameForTask200TIP5-1-21 
200TIP5-1-22 NameForTask200TIP5-1-22 
200TIP5-1-23 NameForTask200TIP5-1-23 
200TIP5-1-24 NameForTask200TIP5-1-24 
200TIP5-1-25 NameForTask200TIP5-1-25 
200TIP5-1-26 NameForTask200TIP5-1-26 
200TIP5-1-27 NameForTask200TIP5-1-27 
200TIP5-1-28 NameForTask200TIP5-1-28 
200TIP5-1-29 NameForTask200TIP5-1-29 
200TIP5-1-30 NameForTask200TIP5-1-30 
200TIP5-1-31 NameForTask200TIP5-1-31 
200TIP5-1-32 NameForTask200TIP5-1-32 
200TIP5-1-33 NameForTask200TIP5-1-33 
200TIP5-1-34 NameForTask200TIP5-1-34 
200TIP5-1-35 NameForTask200TIP5-1-35 
200TIP5-1-36 NameForTask200TIP5-1-36 
200TIP5-1-37 NameForTask200TIP5-1-37 
200TIP5-1-38 NameForTask200TIP5-1-38 
200TIP5-1-3  NameForTask200TIP5-1-3 

When I remove and re-add the columns as Detail columns (no sorting) the record is displayed. I've also found if I change the data in the TASK_NAME column to include a unique value at the beginning of the field, the 200TIP5-1-39 record is displayed as (Display repeated sort values is off):

200TIP5-1-3  A-NameForTask200TIP5-1-3 
             B-NameForTask200TIP5-1-39

This is a vanilla report (create a new report and add the two columns). The data source is an Oracle view. Here is the beginning of the synonym:

FILENAME=report_task_full, SUFFIX=SQLORA  , $
  SEGMENT=REPORT_TASK_FULL, SEGTYPE=S0, $
    FIELDNAME=TASK_ID, ALIAS=TASK_ID, USAGE=A50V, ACTUAL=A50V, $
    FIELDNAME=TASK_NAME, ALIAS=TASK_NAME, USAGE=A200V, ACTUAL=A200V,
      MISSING=ON, $

Thanks,
Brian

I know this may sound like a silly question, however since you did not include your code are you using

SUM.... (or WRITE...)
BY

or
PRINT....
BY

Sorry, here is the code

TABLE FILE REPORT_TASK_FULL
BY TASK_ID
BY TASK_NAME
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
     RIGHTGAP=0.125000,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
TYPE=REPORT,
     COLUMN=N2,
     WRAP=6.000000,
$
ENDSTYLE
END

quote:

TABLE FILE REPORT_TASK_FULL
BY TASK_ID
BY TASK_NAME
HEADING
""

This works, interesting, I do believe though as you have no print or sum statement all you are going to get is the unique instances of the by fields. If that is what you want have you tried adding say

PRINT TASKNAME NOPRINT

OR

PRINT TASKNAME
BY TASK_ID

or did something just get missed in your copy paste?

quote:

 
200TIP5-1-3  A-NameForTask200TIP5-1-3 
             B-NameForTask200TIP5-1-39

That should give you a clue: Both have TASK_ID equal to 200TIP5-1-3...

  
TABLE FILE REPORT_TASK_FULL
PRINT 
    TASK_ID
    TASK_NAME
BY TASK_ID NOPRINT
BY TASK_NAME NOPRINT
END

Tom, surprisingly the TASK_ID is equal to 200TIP5-1-39, but WebFocus is chopping off the "9" at the end. I've traced the SQL and verified that Oracle is returning the correct data. In the first example (without the "A-" and "B-"), it appears to be chopping off the "9" at the end of TASK_NAME as well and considering it a duplicate row.

Leah, adding

PRINT TASK_NAME NOPRINT

fixed the problem.

So the conclusion: to sort by all fields you must add a hidden, unsorted field.

Thanks for your help and quick replies.
Brian

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

You're most welcome.

Sometimes it's the simple things that bite you.

You may want to check your master file description to make sure the USAGE format for your field is long enough to accomodate the longest length of the field values for TASK_NAME. This may be your problem.

You should probably check the field format for TASK_ID as well.

Good advice Mickey. In this case the fields are long enough. The "truncating" is not consistent because it will appear to truncate "200TIP5-1-39" but not "200TIP5-1-38".

Brian,
starting with what we know as given:
focus doesn't truncate, just on a whim.
To truncate a field, you actually have to work at it deliberately, maybe set up an edit function, or some other activity, or as mickey suggests, perhaps apply a different master file.
and just to be clear, DETAIL and SORTing aren't related. It sounds as tho you're using the GUI, since you're using the word 'DETAIL' in your post. The word 'DETAIL' isn't in code anywhere, its just something a gui user clicks on to effect the change of the verb from SUM to PRINT. The word 'DETAIL' means don't aggregate, there are only 2 choices...either aggregate or don't.
In your fex, the first posting, you're using SUM. You don't actually see that you're using SUM, but you are. The word BY with no verb creates a SUM by default, and produces a sorted, aggregated list. The word BY with the verb PRINT creates a sorted , granular (or 'detail') list.

If you repeat Tom's example, above, and stick in a SET BYDISPLAY = ON (I don't know how you flip that switch in the gui, but you'll figure that out), you'll see what the underlying data in the output actually looks like, and the light bulb will go on.

if you want an aggregate list of unique pairs,
TABLE FILE..
BY FIELD1 BY FIELD2
END
will absolutely give it to you.
if you want a granular list of your entire data set,
TABLE FILE ...
PRINT FIELD2 NOPRINT
BY FIELD1 BY FIELD2
(..or any of a number of variations on that theme)
will absolutely give it to you.

As for the truncation, as mickey says, you've got something else going on...some errant field redefine perhaps, because focus doesn't perform edits without specific effort.
Keep researching...
-s.
ps.. It looks like you're new to the Point. welcome, and you might really enjoy taking the intro to focus class or two, which will get you quite comfortable with the gui use and with data exploration using it. We all highly recommend that to everyone. It'll make you smile.

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

Brian,

Another thought is maybe the actual values in the database randomly have a leading blank space? This would not be so obvious if the report results appear in HTML. You could also, just as an experiment, make the length of the TASK_ID field in the MFD even longer than you think it needs to be to see if the values then appear OK. If they do, leading blank spaces might be in some of the values.

I'm just trying to think of some reasons why this is happening. As Susannah said, I have never seen WF truncate for no reason either. There must be something causing this.

A solution was found (I've highlighted in bold above for anyone reading this later to find easily). However, perhaps it is a workaround rather than a solution.

I'll post a full sample that replicates the problem. Here is a SQL script to create the table and necessary data:

drop table test_table;

create table test_table (
    task_id varchar(50) not null primary key,
    task_name varchar(200)
);

Insert into test_table (task_id, task_name) Values ('200TIP5-1-2',  'A-NameForTask200TIP5-1-2');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-3',  'B-NameForTask200TIP5-1-3');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-20', 'C-NameForTask200TIP5-1-20');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-21', 'D-NameForTask200TIP5-1-21');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-22', 'E-NameForTask200TIP5-1-22');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-23', 'F-NameForTask200TIP5-1-23');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-24', 'G-NameForTask200TIP5-1-24');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-25', 'H-NameForTask200TIP5-1-25');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-26', 'I-NameForTask200TIP5-1-26');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-27', 'J-NameForTask200TIP5-1-27');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-28', 'K-NameForTask200TIP5-1-28');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-29', 'L-NameForTask200TIP5-1-29');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-30', 'M-NameForTask200TIP5-1-30');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-31', 'N-NameForTask200TIP5-1-31');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-32', 'O-NameForTask200TIP5-1-32');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-33', 'P-NameForTask200TIP5-1-33');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-34', 'Q-NameForTask200TIP5-1-34');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-35', 'R-NameForTask200TIP5-1-35');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-36', 'S-NameForTask200TIP5-1-36');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-37', 'T-NameForTask200TIP5-1-37');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-38', 'U-NameForTask200TIP5-1-38');
Insert into test_table (task_id, task_name) Values ('200TIP5-1-39', 'V-NameForTask200TIP5-1-39');

commit;

I created a new procedure with this code (not using the GUI):

SET BYDISPLAY = ON
TABLE FILE test_table
BY TASK_ID
BY TASK_NAME
END

Here is the output:

TASK_ID        TASK_NAME 
200TIP5-1-2    A-NameForTask200TIP5-1-2 
200TIP5-1-20   B-NameForTask200TIP5-1-20 
200TIP5-1-21   C-NameForTask200TIP5-1-21 
200TIP5-1-22   D-NameForTask200TIP5-1-22 
200TIP5-1-23   E-NameForTask200TIP5-1-23 
200TIP5-1-24   F-NameForTask200TIP5-1-24 
200TIP5-1-25   G-NameForTask200TIP5-1-25 
200TIP5-1-26   H-NameForTask200TIP5-1-26 
200TIP5-1-27   I-NameForTask200TIP5-1-27 
200TIP5-1-28   J-NameForTask200TIP5-1-28 
200TIP5-1-29   K-NameForTask200TIP5-1-29 
200TIP5-1-30   M-NameForTask200TIP5-1-30 
200TIP5-1-31   N-NameForTask200TIP5-1-31 
200TIP5-1-32   O-NameForTask200TIP5-1-32 
200TIP5-1-33   P-NameForTask200TIP5-1-33 
200TIP5-1-34   Q-NameForTask200TIP5-1-34 
200TIP5-1-35   R-NameForTask200TIP5-1-35 
200TIP5-1-36   S-NameForTask200TIP5-1-36 
200TIP5-1-37   T-NameForTask200TIP5-1-37 
200TIP5-1-38   U-NameForTask200TIP5-1-38 
200TIP5-1-3    L-NameForTask200TIP5-1-3 
200TIP5-1-3    V-NameForTask200TIP5-1-39     <- strange

My observations:
1. The last row should read "200TIP5-1-39 V-Name...."
2. The "200TIP5-1-3" should be sorted before the 200TIP5-1-30 record (notice the -2 record is sorted before -20).
3. Deleting the 200TIP5-1-2* records eliminates this problem though they should have no impact.
4. Adding a PRINT TASK_NAME NOPRINT eliminates this problem.
5. Doubling the field sizes in the synonym does not help
6. There are no leading or trailing whitespace


I've got a solution (thanks again), but it would be interesting to understand this behaviour.

Brian

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

Brian,

If you have not already done so, open a tech support case with IBI. This is strange behaviour which needs to be addressed by IBI. You should not have to work that hard to do a simple request.