[SOLVED] How to filer on field formatted as A800V

Hello, I am using WF version 7.7.03, on a Oracle platform. I have a synonym that has a field formatted at A800v. There are 3 values that I'm trying to filter out in order to do drill downs. The values are "HIGH", "Moderate", "Low.". There are other values in the same field with a max of 18 characters that I'm not concerned with. My problem is that I have tried various ways of manipulating the field for example using DEFINE's: The field is called Risk_Level and I have tried the following: (Note: I know the WHERE statements are commented) they are just examples of what I have tried!

DEFINE FILE Tablename

RISKLEVEL2/A8 = RISK_LEVEL;
-*MOD_RISKL/A8 = EDIT (RISKLEVEL2, '99999999');
MOD_RISKL/A8 = EDIT (RISK_LEVEL, '99999999');

END

Once I do my table file and filter ie

Print

field

field

field

-*WHERE MOD_RISKL EQ 'HIGH'
-*WHERE RISKLEVEL2 EQ 'HIGH';

Each time I get zero records meet the criteria and I know for a fact this is incorrect. Any help on my issue would be greatly appreciated!!!

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

7.7.03

OS/Oracle

Windows
Output:All

Posts: 20 | Registered: January 22, 2010

TexasStingray

Master

posted

Hide Post

Here is what I would try first.

See if you can query the table using the CONTAINS on real A800V field like this.

TABLE FILE tablename
PRINT somefields
WHERE RISK_LEVEL CONTAINS 'HIGH' OR 'Moderate' OR 'Low';
END

then if that works you have several options. here is one.

if the 3 values always start in position then you should be able to do and define and filter on it like so

 
DEFINE FILE tablename
RISK/A8 = IF EDIT(RISK_LEVEL, '9999') EQ 'HIGH' THEN 'HIGH' ELSE 
          IF EDIT(RISK_LEVEL, '999') EQ 'Low' THEN 'Low' ELSE 
          IF EDIT(RISK_LEVEL, '99999999') EQ 'Moderate' THEN 'Moderate' ELSE 
            'X';
END


TABLE FILE tablename
PRINT somefields
WHERE RISK EQ 'HIGH' OR 'Moderate' OR 'Low';
END

Scott