How can I sort months (jan,feb,march...december). Thanks in advance

I try this.
TABLE FILE SPECIAL_REPORT
PRINT
TOTAL_RECORDS
BY EACH_MONTH

But the result is not what i inspected.

EACH_MONTH TOTAL_RECORDS
APRIL 9023
AUGUST 8083
FEBRUARY 7215
JANUARY 9530
JULY 7490
JUNE 7330
MARCH 7041
MAY 8922
SEPTEMBER 8437

This message has been edited. Last edited by: <Kathryn Henning>,

Create a DEFINE or COMPUTE column that is a date formatted column based on your alpha column, then us it as a BY NOPRINT before your alpha column.

Even better - read the following:

Describing Data With WebFOCUS Language > Describing an Individual Field > Converting Alphanumeric Dates to WebFOCUS Dates

Alan B has mentioned this a few times: Converting A10V to MDYY

Let us know what the format of EACH_MONTH is.

You would benefit from the (almost) 1001 Ways to Work with Dates available PDF from www.aviter.com.

It has TONS of info on manipulating dates.

Good luck.

Vivian

As Francis said you need to carry the month numbers as otherwise you get sorted by ABC's. Usually you have the month as a number field in the database so you carry say MONTH_NR and MONTH_NM and use the MONTH_NR as the BY NOPRINT field.

Depending on how your DB is set up usually you have a translation table for the date formats - if not that might be an idea as it saves a lot of coding. Or then you create a module you can use as INCLUDE that does the most usual and annoying format changes if the DBA's don't want to play ball.

DBA provide me this. each_month is a string

Table special_report
====================
each_month, total_records
--------------------

each_month varchar(30)
total_records int(4)

MASTER FILE:
FILENAME=SPECIAL_REPORT, SUFFIX=SQLMYSQL, $
SEGMENT=SPECIAL_REPORT, SEGTYPE=S0, $
FIELDNAME=EACH_MONTH, ALIAS=each_month, USAGE=A30V, ACTUAL=A30V,
MISSING=ON, $
FIELDNAME=TOTAL_RECORDS, ALIAS=total_records, USAGE=I11, ACTUAL=I4,
MISSING=ON, $
FIELDNAME=SEQ, ALIAS=seq, USAGE=I11, ACTUAL=I4,
MISSING=ON, $

Unfortunately, it looks like DATEPATTERN cannot be used with a field that has only month in it, so here's a quick way to solve this:

DEFINE FILE FP_DTIC_SPECIAL_REPORT
EACH_MONTH_SORT/I2 = DECODE EACH_MONTH(
'JANUARY' 01, 'FEBRUARY' 02, 'MARCH'     03,
'APRIL'   04, 'MAY'      05, 'JUNE'      06,
'JULY'    07, 'AUGUST'   08, 'SEPTEMBER' 09,
'OCTOBER' 10, 'NOVEMBER' 11, 'DECEMBER'  12, ELSE 99);
END
 
TABLE FILE FP_DTIC_SPECIAL_REPORT
SUM TOTAL_RECORDS
BY EACH_MONTH_SORT NOPRINT
BY EACH_MONTH
END

Thank you all, now I understood the concept.

Dave.

DATEPATTERN should work for month only:

FILENAME=MONTHSALES, SUFFIX=SQLMYSQL, $
  SEGMENT=MONTHSALES, SEGTYPE=S0, $
    FIELDNAME=ID, ALIAS=id, USAGE=I11, ACTUAL=I4, FIELDTYPE=R, $
    FIELDNAME=MONTH, ALIAS=Month, USAGE=Mtr, ACTUAL=A12V,
      MISSING=ON,DATEPATTERN='[MONTH]', $
    FIELDNAME=SALES, ALIAS=Sales, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $

However it is not required, as WebFOCUS is intelligent enough to work with direct translation of dates,e.g.:

FILENAME=MONTHSALES, SUFFIX=SQLMYSQL, $
  SEGMENT=MONTHSALES, SEGTYPE=S0, $
    FIELDNAME=ID, ALIAS=id, USAGE=I11, ACTUAL=I4, FIELDTYPE=R, $
    FIELDNAME=MONTH, ALIAS=Month, USAGE=Mtr, ACTUAL=A12V,
      MISSING=ON, $
    FIELDNAME=SALES, ALIAS=Sales, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $

Both give:

TABLE FILE MONTHSALES
SUM SALES
BY MONTH
END

MONTH	  SALES
January	  16
February  22
March	  27
April	  36
May	  21
June	  32
August	  36
September 12
October	  11
November  29
December  43

From:

TABLE FILE MONTHSALES
PRINT ID MONTH SALES
END


ID	 MONTH	   SALES
1	 September 12
2	 October   11
3	 June	   32
4	 May	   21
5	 April	   17
6	 January   16
7	 February  22
8	 December  43
9	 November  29
10	 March	   27
11	 August	   36
12	 April	   19

That's a revelation - thanks Alan.

Too bad Mtr is not consistently recognized. This won't work:

COMPUTE EACH_MONTH2/Mtr = LAST EACH_MONTH;
COMPUTE MTH_DIFF/I2 = DATEDIF(EACH_MONTH, EACH_MONTH2, 'M');

I tried Alan's solution on my oracle dates table but it only works if I am using PRINT.

SUM only works if I let WebFOCUS do the aggregation by issuing the ENGINE SQLORA SET OPTIMIZATION NOAGGR command.

e.g.

TABLE FILE DATES
PRINT
DAY
BY MONTH
END

will work

TABLE FILE DATES
SUM
CNT.DAY
BY MONTH
END

returns the following error:

(FOC1400) SQLCODE IS 1722 (HEX: 000006BA)
: ORA-01722: invalid number
(FOC1407) SQL FETCH CURSOR ERROR. : DATES

Are there some database limitations to using this?

Interesting. I've only used this on MySQl and SQL Server with 7.703 and above. So possible, and obviously, there are limitations. Is this using DATEPATTERN?

The oracle table has a column with varchar2(3) format called CALENDARMONTHSHORTNAME, the data is like Jan, Feb etc.

I defined a column that looks like this in the master file:

FIELDNAME=MONTHSHORT, ALIAS=CALENDARMONTHSHORTNAME, USAGE=Mt, ACTUAL=A3,
MISSING=ON,DATEPATTERN='[Mon]', $

I also tried it without DATEPATTERN in the definition and the behaviour is the same for both. Works if its PRINT, doesn't if its SUM.