I have a field name COLOR with the values BLACK or WHITE.

I am trying to code a WHERE statement that filters on either just
WHITE (e.g. WHERE COLOR EQ 'WHITE';, just BLACK or is NULL so the
report will show both BLACK and WHITE colors in the report.

The color will be provided by a user via a -prompt.

I am writing this in dialogue manager and here is what I have so far:

 

-IF &ETYPE = IF &COLOR EQ 'RED' THEN 'RED' 
-       ELSE IF &COLOR EQ 'WHITE' THEN 'WHITE' ELSE  ..... ;

I am stuck on completing this construct to allow RED and WHITE
to be included in the WHERE statement.

Can anyone help?

Thanks!

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

I could be totally misunderstanding your request, but try this and see if it helps:

ENGINE INT CACHE SET ON

-* Assume 'NULL' country will be blank character string
-* -SET &COUNTRY='';

-* Assume country is 'ENGLAND'
-SET &COUNTRY='ENGLAND';

-SET &CTYPE = '';
-SET &CTYPE = IF &COUNTRY EQ 'ENGLAND' THEN IF &CTYPE EQ '' THEN '''ENGLAND''' ELSE &CTYPE ELSE &CTYPE;
-SET &CTYPE = IF &COUNTRY EQ 'FRANCE' THEN IF &CTYPE EQ '' THEN '''FRANCE''' ELSE &CTYPE ELSE &CTYPE;
-SET &CTYPE = IF &COUNTRY EQ 'GERMANY' THEN IF &CTYPE EQ '' THEN '''GERMANY''' ELSE &CTYPE ELSE &CTYPE;
-SET &CTYPE = IF &COUNTRY EQ 'JAPAN' THEN IF &CTYPE EQ '' THEN '''JAPAN''' ELSE &CTYPE ELSE &CTYPE;
-SET &CTYPE = IF &CTYPE EQ '' THEN '_FOC_NULL' ELSE &CTYPE;

-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';

TABLE FILE IBISAMP/CAR
BY  CAR.ORIGIN.COUNTRY
BY  CAR.COMP.CAR
BY  CAR.CARREC.MODEL
BY  CAR.BODY.BODYTYPE
WHERE CAR.ORIGIN.COUNTRY EQ &CTYPE;
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET CACHELINES 100
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,
$
     SUMMARY='&WF_SUMMARY',
     TITLETEXT='&WF_TITLE',
$
ENDSTYLE
END
-RUN

I agree that FOC_NONE (or however it is spelled so WF8 and APP STUDIO
recognize it) seems to be the approach.

What I am trying to accomplish is...

If user selects RED via the -PROMPT the WHERE filter is:

 WHERE COLOR EQ 'RED'; 

If user selects WHITE via the -PROMPT the WHERE filter is:

 WHERE COLOR EQ 'WHITE';

If user selects ALL the WHERE filter is ignored, and
RED and WHITE colors will display in the report.

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

-IF &COLOR EQ 'RED' OR 'WHITE' THEN GOTO RPT1 ELSE RPT2;

-RPT1
TABLE FILE POLLYMYGOLLY
PRINT *
WHERE COLOR EQ &COLOR;
END
-RUN
-EXIT

-RPT2

TABLE FILE POLLYMYGOLLY
PRINT *
WHERE COLOR EQ 'RED' OR 'WHITE';
END
-RUN
-EXIT

Sometimes a Heineken clears the mind!

Or something like:

-SET &WHERE = IF &COLOR EQ 'RED' OR 'WHITE' THEN 'WHERE COLOR EQ ' | &COLOR | ';' ELSE '';
TABLE FILE POLLYMYGOLLY
PRINT *
&WHERE
END
-RUN

Tom,

your overthinking this.

just do this

 

TABLE FILE CAR
PRINT 
     CAR.ORIGIN.COUNTRY
     CAR.COMP.CAR
     CAR.CARREC.MODEL
     CAR.BODY.BODYTYPE
     CAR.BODY.SEATS
     CAR.BODY.DEALER_COST
     CAR.BODY.RETAIL_COST
     CAR.BODY.SALES
     CAR.SPECS.LENGTH
     CAR.SPECS.WIDTH
     CAR.SPECS.HEIGHT
     CAR.SPECS.WEIGHT
     CAR.SPECS.WHEELBASE
     CAR.SPECS.FUEL_CAP
     CAR.SPECS.BHP
     CAR.SPECS.RPM
     CAR.SPECS.MPG
     CAR.SPECS.ACCEL
     CAR.WARANT.WARRANTY
     CAR.EQUIP.STANDARD
WHERE CAR.BODY.BODYTYPE EQ '&BODYTYPE.(<ALL,_FOC_NULL>,<Convertible,Convertible>,<Sedan,SEDAN>).BODYTYPE:.';
ON TABLE NOTOTAL
END

The values after in the prompt are what you would have in your HTML page. The _FOC_NULL value tells WF to ignore the filter. No need for IF ... THEN... ELSE statements.