I used the BY command combined with the LOWEST and HIGHEST prefix to vertically sort fields for my report whose output is in Excel. However, when I sort the data using Excel, the order is different. How do I sort in Web Focus so the order of the data is the same I get by sorting in Excel. See examples below:

BY LOWEST ITEM_NO
A18-46672-000
A22-67400-000
HUN145138997730
03-38548-002
06-75227-000
22-66582-010

BY HIGHEST ITEM_NO
22-66582-010
06-75227-000
03-38548-002
HUN145138997730
A22-67400-000
A18-46672-000

ITEM_NO (Using Excel A to Z)
03-38548-002
06-75227-000
22-66582-010
A18-46672-000
A22-67400-000
HUN145138997730

ITEM_NO (Using Excel Z to A)
HUN145138997730
A22-67400-000
A18-46672-000
22-66582-010
06-75227-000
03-38548-002

Thank you for the help!

This message has been edited. Last edited by: <Emily McAllister>,

Petter, when I run the following code, the order is as you described for Excel A to Z:

DEFINE FILE CAR
MY_ALPHA_NUM_COLUMN/A15 =
     IF CAR EQ 'AUDI'   THEN 'A18-46672-000'
ELSE IF CAR EQ 'BMW'    THEN 'A22-67400-000'
ELSE IF CAR EQ 'JENSEN' THEN '06-75227-000'
ELSE IF CAR EQ 'DATSUN' THEN 'HUN145138997730'
ELSE IF CAR EQ 'JAGUAR' THEN '03-38548-002'
ELSE '22-66582-010';
END

TABLE FILE CAR
SUM
SALES
BY LOWEST MY_ALPHA_NUM_COLUMN
END

Perhaps the issue is platform related - is your reporting server on UNIX?

Perhaps this will work - add a number to the beginning of columns where the data starts with an alpha character:

DEFINE FILE CAR
MY_ALPHA_NUM_COLUMN/A15 =
     IF CAR EQ 'AUDI'   THEN 'A18-46672-000'
ELSE IF CAR EQ 'BMW'    THEN 'A22-67400-000'
ELSE IF CAR EQ 'JENSEN' THEN '06-75227-000'
ELSE IF CAR EQ 'DATSUN' THEN 'HUN145138997730'
ELSE IF CAR EQ 'JAGUAR' THEN '03-38548-002'
ELSE '22-66582-010';

POS1/A1 = SUBSTR(15, MY_ALPHA_NUM_COLUMN, 1, 1, 1, 'A1');
-*TEST/I6 = CHKFMT(1, POS1, '9', 'I6');
MY_ALPHA_NUM_COLUMN_SORT/A16 = IF CHKFMT(1, POS1, 'A', 'I6') EQ 0 THEN '9' | MY_ALPHA_NUM_COLUMN ELSE MY_ALPHA_NUM_COLUMN;
END

TABLE FILE CAR
SUM
-*POS1
-*TEST
MY_ALPHA_NUM_COLUMN
SALES
MY_ALPHA_NUM_COLUMN_SORT
BY LOWEST MY_ALPHA_NUM_COLUMN_SORT NOPRINT
END

Your database is sorting in EBCDIC while Excel is sorting in ASCII. To fix this you will need to set your database to sort in ASCII.

Regards,
Anatess

Is it the database? Couldn't WebFOCUS intervene? By creating a HOLD file, and then sorting the HOLD file, which would be DB independent.

quote:

Originally posted by Francis Mariani:
Is it the database? Couldn't WebFOCUS intervene? By creating a HOLD file, and then sorting the HOLD file, which would be DB independent.

Ahh yeah. In any case, whoever is sorting that on the Webfocus side is sorting in EBCDIC.

Thank you for all the answers. I will explore all the options. I have a follow up question: Is there any SET command I can use in Web Focus so it sorts in ASCII instead of EBCDIC? or alternatively, how do I set up my work environment to sort in ASCII just for me?

I'm skeptical of the notion that the data is coming out in EBCDIC seeing as Petter's signature shows his environment as Windows.

The first question to ask is "Where is the data stored?" Is it on a DB2 database on a mainframe? If that is the case, then perhaps we do have an EBCDIC vs ASCII issue.

But if the data is stored on Unix or Windows, then how is it possible that we're getting an EBCDIC sort?

I'd recommend looking at the raw data (where ever it is) and do a native query. For example, if the data is on SQL Server, do a native SQL Server query and see what you get back.

The other place to look would be your WebFOCUS server language setting. It's unlikely, but perhaps there is some server language setting that is causing this sort. You can find out your setting by either going to the Server Console and look at your NLS configuration - OR - you can do a ? LANG from the command line. It should look something like this:

>>? lang
        NATIONAL LANGUAGE INFORMATION

Language                001/AMENGLISH   (   ,en)
Code Page               1252
Client Code Page        1252
Dollar                  24($)
Lowcase alphabet        YES
Decimal notation        OFF(.)
Currency symbol         $
Date/Time format        EDA
NLS sort                NO
NLS upcase/lowcase      NO
NLS Control Characters  OFF
DBCS Flag               OFF(SBCS)
  

Wouldn't a hub-sub setup, with the sub being in EBCDIC encoding and the hub in DOS/Windows encoding, explain that?