[Frustrated - Solved (temporarily)] Bug with sorting on a Time field

November 24, 2008, 05:13 PM

Francis Mariani

I'm having trouble sorting with a Time field to create a calendar type report.

Test Code:

-SET &ECHO=ON;

-*-- Create the master for TEST1 -------------------------------------
FILEDEF DATAMAST DISK TEST1.MAS
-RUN

-*--
-WRITE DATAMAST FILE=TEST1, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=COUNTRY, ALIAS=COUNTRY, USAGE=A10, ACTUAL=A10, $
-WRITE DATAMAST FIELD=,        ALIAS=FILL1,   USAGE=A01, ACTUAL=A01, $
-WRITE DATAMAST FIELD=WDATE,   ALIAS=WDATE,   USAGE=A08, ACTUAL=A08, $
-WRITE DATAMAST FIELD=,        ALIAS=FILL2,   USAGE=A01, ACTUAL=A01, $
-WRITE DATAMAST FIELD=WTIME,   ALIAS=WTIME,   USAGE=A06, ACTUAL=A06, $
-WRITE DATAMAST DEFINE DTTMX/A25 = WDATE | ' ' | EDIT(WTIME,'99:99:99') | '.000';
-WRITE DATAMAST DEFINE DATETIME/HYYMDS = HINPUT(25, DTTMX, 8, 'HYYMDS');

-*-- Create the data file for TEST1 ----------------------------------
FILEDEF TEST1 DISK TEST1.FTM
-RUN

-WRITE TEST1 ENGLAND    20081112 175305
-WRITE TEST1 CANADA     20081112 083001
-WRITE TEST1 CANADA     20081113 083001
-WRITE TEST1 CANADA     20081113 084213
-WRITE TEST1 CANADA     20081113 101010
-WRITE TEST1 CANADA     20081113 172222
-WRITE TEST1 CANADA     20081113 120934
-WRITE TEST1 CANADA     20081114 140030
-WRITE TEST1 S KOREA    20081114 101030
-WRITE TEST1 S KOREA    20081116 110956
-WRITE TEST1 USA        20081114 171030
-WRITE TEST1 USA        20081116 190956

-*-- Create the report -----------------------------------------------
DEFINE FILE TEST1
STARTDATE/YYMD    = HDATE(DATETIME, 'YYMD');
STARTDATE1/wMtDYY = STARTDATE;
STARTTIME/HHI     = DATETIME;
END
-RUN

TABLE FILE TEST1
PRINT
COUNTRY
DATETIME

BY STARTDATE NOPRINT
ON STARTDATE SUBHEAD
" "
"<STARTDATE1"

BY STARTTIME
END
-RUN

Result:

STARTTIME COUNTRY DATETIME 
  
Wed, Nov 12 2008 
 
08:30     CANADA  2008/11/12 08:30:01 
          ENGLAND 2008/11/12 17:53:05 
  
Thu, Nov 13 2008 
 
08:30     CANADA  2008/11/13 08:30:01 
          CANADA  2008/11/13 08:42:13 
          CANADA  2008/11/13 10:10:10 
          CANADA  2008/11/13 12:09:34 
          CANADA  2008/11/13 17:22:22 
  
Fri, Nov 14 2008 
 
10:10     S KOREA 2008/11/14 10:10:30 
          CANADA  2008/11/14 14:00:30 
          USA     2008/11/14 17:10:30 
  
Sun, Nov 16 2008 
 
11:09     S KOREA 2008/11/16 11:09:56 
          USA     2008/11/16 19:09:56

Expected result:

STARTTIME COUNTRY DATETIME 
  
Wed, Nov 12 2008 
 
08:30     CANADA  2008/11/12 08:30:01 
17:53     ENGLAND 2008/11/12 17:53:05 
  
Thu, Nov 13 2008 
 
08:30     CANADA  2008/11/13 08:30:01 
08:42     CANADA  2008/11/13 08:42:13 
10:10     CANADA  2008/11/13 10:10:10 
12:09     CANADA  2008/11/13 12:09:34 
17:22     CANADA  2008/11/13 17:22:22 
  
Fri, Nov 14 2008 
 
10:10     S KOREA 2008/11/14 10:10:30 
14:00     CANADA  2008/11/14 14:00:30 
17:10     USA     2008/11/14 17:10:30 
  
Sun, Nov 16 2008 
 
11:09     S KOREA 2008/11/16 11:09:56 
19:09     USA     2008/11/16 19:09:56

What's going on here?

This message has been edited. Last edited by: Francis Mariani,

November 25, 2008, 03:10 AM

<JG>

That has got to be one of the strangest things I've seen in a while. Definitely a bug.

This works

 
TABLE FILE TEST1
PRINT
 COMPUTE XSTARTTIME/HHI= STARTTIME;
COUNTRY
DATETIME

BY STARTDATE NOPRINT
ON STARTDATE SUBHEAD
" "
"<STARTDATE1"

BY  STARTTIME NOPRINT
END
-RUN

November 25, 2008, 03:21 AM

GamP

This is strange indeed.
It only works as expected when you remove the NOPRINT from the by-field.
I'd make this a case for IBI, since this can not be correct behaviour.

November 25, 2008, 04:27 AM

OPALTOSH

The field you are sorting by STARTDATE has no time component so he result is hardly surprising.
Sort by the field that has the time compnent.

November 25, 2008, 05:03 AM

<JG>

quote:
DEFINE STARTTIME/HHI = DATETIME;

SORT BY STARTTIME

OPALTOSH I seem to be reading different code to you

November 25, 2008, 09:36 AM

PBrightwell

I think the problem is in your master definition. This gives you the desired result:

 
-SET &ECHO=ON;

-*-- Create the master for TEST1 -------------------------------------
FILEDEF DATAMAST DISK /usr/ibi/apps/wfmast/test1.mas
-RUN

-*--
-WRITE DATAMAST FILE=TEST1, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=COUNTRY, ALIAS=COUNTRY, USAGE=A10, ACTUAL=A10, $
-WRITE DATAMAST FIELD=,        ALIAS=FILL1,   USAGE=A01, ACTUAL=A01, $
-WRITE DATAMAST FIELD=WDATE,   ALIAS=WDATE,   USAGE=A08, ACTUAL=A08, $
-WRITE DATAMAST FIELD=,        ALIAS=FILL2,   USAGE=A01, ACTUAL=A01, $
-WRITE DATAMAST FIELD=WTIME,   ALIAS=WTIME,   USAGE=A06, ACTUAL=A06, $
-WRITE DATAMAST DEFINE DTTMX/A25 = WDATE | ' ' | EDIT(WTIME,'99:99:99') | '.000';
-WRITE DATAMAST DEFINE DATETIME/HYYMDS = HINPUT(25, DTTMX, 8, 'HYYMDS');

-*-- Create the data file for TEST1 ----------------------------------
FILEDEF TEST1 DISK /cisfocrpt/data/test.dat
-RUN

-WRITE TEST1 ENGLAND    20081112 175305
-WRITE TEST1 CANADA     20081112 083001
-WRITE TEST1 CANADA     20081113 083001
-WRITE TEST1 CANADA     20081113 084213
-WRITE TEST1 CANADA     20081113 101010
-WRITE TEST1 CANADA     20081113 172222
-WRITE TEST1 CANADA     20081113 120934
-WRITE TEST1 CANADA     20081114 140030
-WRITE TEST1 S KOREA    20081114 101030
-WRITE TEST1 S KOREA    20081116 110956
-WRITE TEST1 USA        20081114 171030
-WRITE TEST1 USA        20081116 190956
-RUN

-*-- Create the report -----------------------------------------------
DEFINE FILE TEST1
STARTDATE/YYMD    = HDATE(DATETIME, 'YYMD');
STARTDATE2/A8YYMD=STARTDATE;
STARTDATE1/wMtDYY = STARTDATE;
STARTTIME/HHI     = DATETIME;
STARTTIME2/A5=EDIT(WTIME,'99:99');
END
-RUN


TABLE FILE TEST1
PRINT  
COUNTRY
DATETIME
BY STARTDATE2 NOPRINT
BY DATETIME  NOPRINT

BY STARTTIME2 
ON STARTDATE2 SUBHEAD
" "
"<STARTDATE1"

END
-RUN

November 25, 2008, 09:59 AM

Francis Mariani

Pat,

There is no difference in the Master in your solution, you simply have a different way of determining the Time portion of the Date/Time column in the DEFINE statements.

I can't see anything wrong with the way I am doing it. I'm sorting on the Date/Time column that has been reformatted to display only the Hours:Minutes, the result sure looks like a bug to me.

November 25, 2008, 10:02 AM

Francis Mariani

OPALTOSH,

quote:
BY STARTTIME

is the field that has the time component.

November 25, 2008, 10:40 AM

FrankDutch

Francis

DEFINE FILE TEST1
STARTDAG/YYMD    = HDATE(DATETIME, 'YYMD');
STARTDATE1/wMtDYY = STARTDAG;
STARTTIME/HHI     = DATETIME;
DUMMY/A1='';
END
-RUN

TABLE FILE TEST1
SUM
COUNTRY
DATETIME

BY STARTDAG NOPRINT
BY DUMMY AS ''
BY  STARTTIME 
ON STARTDAG SUBHEAD
" "
"<STARTDATE1"


END

I created an extra dummy field as BY field and then is works, but strange it is!!!!

November 25, 2008, 10:55 AM

Hua

I think STARTDATE, STARTDATE1, STARTTIME are all the same internally which pointing to the value of DATETIME.
But why NOPRINT before STARTTIME triggers this behavior?!

November 25, 2008, 10:57 AM

Francis Mariani

Frank, thanks a million!

That is an acceptable temporary fix to the problem. I may still open a case so that someone at IBI can explain the issue, something to do with the internal matrix not doubt.

Thanks a lot,

Francis.

------------------------------------------------------------------------------------
Doesn't work:

-SET &ECHO=ON;

-*-- Create the master for TEST1 -------------------------------------
FILEDEF DATAMAST DISK TEST1.MAS
-RUN

-*--
-WRITE DATAMAST FILE=TEST1, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=COUNTRY,  ALIAS=COUNTRY,  USAGE=A10,    ACTUAL=A10, $
-WRITE DATAMAST FIELD=,         ALIAS=FILL1,    USAGE=A01,    ACTUAL=A01, $
-WRITE DATAMAST FIELD=DATETIME, ALIAS=DATETIME, USAGE=HYYMDS, ACTUAL=A17, $

-*-- Create the data file for TEST1 ----------------------------------
FILEDEF TEST1 DISK TEST1.FTM
-RUN

-WRITE TEST1 ENGLAND    20081112175305000
-WRITE TEST1 CANADA     20081112175305000
-WRITE TEST1 CANADA     20081112083001000
-WRITE TEST1 CANADA     20081113083001000
-WRITE TEST1 CANADA     20081113172222000
-WRITE TEST1 USA        20081113172222000
-WRITE TEST1 CANADA     20081113120934000
-WRITE TEST1 CANADA     20081114140030000
-WRITE TEST1 S KOREA    20081114101030000
-WRITE TEST1 W GERMANY  20081114101030000
-WRITE TEST1 S KOREA    20081116110956000
-WRITE TEST1 USA        20081114171030000
-WRITE TEST1 USA        20081116190956000

-*-- Create the report -----------------------------------------------
DEFINE FILE TEST1
STARTDATE/YYMD    = HDATE(DATETIME, 'YYMD');
STARTDATE1/wMtDYY = STARTDATE;
STARTTIME/HHIA    = DATETIME;
END
-RUN

TABLE FILE TEST1
PRINT
COUNTRY
DATETIME
STARTTIME

BY STARTDATE NOPRINT
ON STARTDATE SUBHEAD
" "
"<STARTDATE1"

BY STARTTIME
END
-RUN

------------------------------------------------------------------------------------
Works:

-SET &ECHO=ON;

-*-- Create the master for TEST1 -------------------------------------
FILEDEF DATAMAST DISK TEST1.MAS
-RUN

-*--
-WRITE DATAMAST FILE=TEST1, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=COUNTRY,  ALIAS=COUNTRY,  USAGE=A10,    ACTUAL=A10, $
-WRITE DATAMAST FIELD=,         ALIAS=FILL1,    USAGE=A01,    ACTUAL=A01, $
-WRITE DATAMAST FIELD=DATETIME, ALIAS=DATETIME, USAGE=HYYMDS, ACTUAL=A17, $

-*-- Create the data file for TEST1 ----------------------------------
FILEDEF TEST1 DISK TEST1.FTM
-RUN

-WRITE TEST1 ENGLAND    20081112175305000
-WRITE TEST1 CANADA     20081112175305000
-WRITE TEST1 CANADA     20081112083001000
-WRITE TEST1 CANADA     20081113083001000
-WRITE TEST1 CANADA     20081113172222000
-WRITE TEST1 USA        20081113172222000
-WRITE TEST1 CANADA     20081113120934000
-WRITE TEST1 CANADA     20081114140030000
-WRITE TEST1 S KOREA    20081114101030000
-WRITE TEST1 W GERMANY  20081114101030000
-WRITE TEST1 S KOREA    20081116110956000
-WRITE TEST1 USA        20081114171030000
-WRITE TEST1 USA        20081116190956000

-*-- Create the report -----------------------------------------------
DEFINE FILE TEST1
STARTDATE/YYMD    = HDATE(DATETIME, 'YYMD');
STARTDATE1/wMtDYY = STARTDATE;
STARTTIME/HHIA    = DATETIME;
DUMMY1/A1         = '';
END
-RUN

TABLE FILE TEST1
PRINT
COUNTRY
DATETIME
STARTTIME

BY STARTDATE NOPRINT
ON STARTDATE SUBHEAD
" "
"<STARTDATE1"

BY DUMMY1 AS ''

BY STARTTIME
END
-RUN

November 25, 2008, 01:25 PM

j.gross

Looks like a bug to me too.

If you run

TABLE FILE TEST1
PRINT STARTTIME/HYYMDS
BY STARTTIME
END

it will be apparent that
(a) the series of Defines in the original post copies the full date-and-time value from DATETIME into STARTTIME; and
(b) BY STARTTIME sorts on the [invisible] date and [visible] time value in the natural manner.

The use of HHI format just hides the other components (year, month, date, second) at print time.

So WF is sorting correctly by STARTTIME -- the bug is in suppressing the repeated display of the sort field within sort break groups: WF is testing for a change in just the date components in the STARTTIME column, rather than change in the overall date-time value.

November 25, 2008, 01:44 PM

Francis Mariani

Jack,

Thanks for the explanation, but does that explain why adding a DUMMY BY statement as Frank suggested make it work?

November 25, 2008, 02:47 PM

FrankDutch

Nor does it explain why it also gives the proper sorting when you remove the NOPRINT after the BY STARTDATE

November 25, 2008, 03:44 PM

j.gross

Hey Frank -

We agree there's a bug: There is some set of circumstances (such as the original posted code) where WF misbehaves in handling an H-time-formatted sort field, and I characterized what it seems to do wrong, but made no assertion as to where the borderline of the bug-domain lies. -- Is it my fault when WF behaves correctly?

But there's another issue people should be aware of -- Htime formats can violate WYSIWYG expectations: If you use a HYYMDS-to-HH cast to get the Hour, and sum a statistic sorted on that HH field to obtain distribution by hour-of-day using a month's data, you'll be disappointed.

November 26, 2008, 11:23 AM

PBrightwell

Francis,

I didn't redo your master file definitions, I only redid the define. I guess I should have made that clear in my previous post. I was looking for a quick solution to give you the results you wanted.

November 26, 2008, 11:38 AM

<JG>

The problem is only going to HTML,HTMTABLE and DHTML.

Using Francis' original code and using PDF, EXL2K and XML it works fine.

November 26, 2008, 03:50 PM

Francis Mariani

JG!

Thank you for your further investigation! I didn't try the other formats.

I'll definitely report this as a bug.

	*Frank*
	prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7

- Using AS 8.2.01 on Windows 10 - IE11.
	in Focus since 1988