WHERE field EQ field ...would you use WHEN or IF

Would you use WHEN or IF to conditionally activate a WHERE statement?

WHERE field1 EQ field2 IF field3 EQ 7;
or
WHERE field1 EQ field2 WHEN field3 EQ 8;

How would several WHERE statements appear with each containing conditional criteria? Does the WHEN/IF come before or after the WHERE statement?

WHERE field1 EQ field2
WHEN field3 EQ 8;
WHERE field1 EQ field2
WHEN field3 EQ 9;
WHERE field1 EQ field2
WHEN field3 EQ 10;

------------------------------------------------------------------------
PROD: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode
TEST: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode

Posts: 13 | Location: Pittsburgh, PA USA | Registered: November 18, 2005

Adam Young

Member

posted

Hide Post

Does the WHEN affect only the WHERE statement just above it, of all WHERE statement's above it?

{WHERE|IF} logical_expression[;]
WHEN logical_expression[;]

{WHERE|IF} logical_expression[;]
{WHERE|IF} logical_expression[;]
{WHERE|IF} logical_expression[;]
{WHERE|IF} logical_expression[;]
{WHERE|IF} logical_expression[;]
WHEN logical_expression[;]

------------------------------------------------------------------------
PROD: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode
TEST: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode

Posts: 13 | Location: Pittsburgh, PA USA | Registered: November 18, 2005

Ignored post by Adam Young posted

Show Post

N.Selph

Guru

posted

Hide Post

I don't believe you can use WHEN to conditionaly activate a WHERE statement. It is used to conditionaly activate SUBFOOT lines, and in STYLESHEET code.
I would use Dialog Manager -IF to conditionaly activate WHERE statements, or compound logical expressions depending on the requirement.

In your example

WHERE (field1 EQ field2 AND field3 EQ 8)
OR
      (field1 EQ field2 AND field3 EQ 9)

(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)

Posts: 391 | Location: California | Registered: April 14, 2003

Ignored post by N.Selph posted

Show Post

Francis Mariani

Expert

posted

Hide Post

And I thought this was some newfangled v7 thing!

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

susannah

Expert

posted

Hide Post

heres the doc on WHERE DOC LIB on WHERE
WHERE is used with TOTAL
as in WHERE TOTAL fieldname GT 100
or when comparing 2 fields
WHERE field1 GT field2
IF can only be used when comparing one field to a value
although WHERE can be used in place of IF, IF is older syntax; IF is a special case of WHERE.
WHEN i have only seen in the stylesheet syntax
WHEN=field GT 100
i've never seen it placed in code as a filter
it doesn't seem to work; I tried creating a boolean and testing, as in your example, but no luck. Have you tried it in some complex filtering situation ? would you share a CAR file example? thanks.

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

In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

Show Post

Adam Young

Member

posted

Hide Post

Alright, so WHEN is used for stylesheet and SUBFOOT/HEAD and will not function elsewhere, but
-IF can be used along with a GOTO to skip about the WHERE statements so that only certain ones will be applied to the report.

I'll try that.

Thanks everyone!

------------------------------------------------------------------------
PROD: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode
TEST: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode

Posts: 13 | Location: Pittsburgh, PA USA | Registered: November 18, 2005

Ignored post by Adam Young posted

Show Post

Adam Young

Member

posted

Hide Post

Is my syntax correct here? The output I am getting leads me to believe the GOTO's are not functioning, but are being ignored.

My goal is to apply only one of the two WHERE statements to the report. I am not familiar with the CAR file, but will review it in the near future.

-IF CATOR1 EQ 1 GOTO WARP1;
-IF CATOR1 EQ 2 GOTO WARP2;
-WARP1
WHERE (FIELD1 EQ FIELD1) OR (FIELD1 EQ MISSING);
-GOTO NEXT1
-WARP2
WHERE (FIELD EQ '&VARIABLE1');
-NEXT1

------------------------------------------------------------------------
PROD: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode
TEST: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode

Posts: 13 | Location: Pittsburgh, PA USA | Registered: November 18, 2005

Ignored post by Adam Young posted

Show Post

Adam Young

Member

posted

Hide Post

-IF and GOTO are a Dialogue Manager commands, and will not function within the Report portion of a procedure.
That is why it is being ignored.

Back to square 1.

Other strategies?

------------------------------------------------------------------------
PROD: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode
TEST: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode

Posts: 13 | Location: Pittsburgh, PA USA | Registered: November 18, 2005

Ignored post by Adam Young posted

Show Post

susannah

Expert

posted

Hide Post

Adam, you can use DM commands inside of the body of code of a fex, most assuredly.
but in your example, CATOR1 needs to be a DM variable, or an &variable, in order to work in a DM statment.
-IF &CATOR1 EQ 1 GOTO WARP1 ;
I would in this case, just have
-GOTO WARP&CATOR ;
as my goto statment;
your goto statements MUST terminate with a ;
-GOTO NEXT1 ;
The whole thing of putting ; at the end of an IF or WHERE statement makes me crazy,some GUI thing, so if you're hand coding,,,take 'em out.
WHERE (FIELD EQ '&VARIABLE1');
i would say
IF FIELD EQ '&VARIABLE1'

If you're thinking that DM can evaluate a value within your data set, ie if your CATOR1 is a variable in your file, no it wont work like that.

In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

Show Post

<JG>

posted

Actually the only Dialogue Manger commands that should be terminated by a semi-colon ';' are –SET and –IF.

All other DM commands should not have a semi-colon.
In most cases they still work if you put one there, however with the code tightening that has been taking place
in recent releases it’s becoming more important to get these minor details correct.

j.gross

Virtuoso

posted

Hide Post

-REPEAT label WHILE expression ;

also requires a closing semicolon.

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Show Post

<JG>

posted

That's not exactly, absolutely correct.
The documentation does not say you need a semi-colon to terminate a WHILE in a –REPEAT.
Except you do unless you code it with END on the following line (not documented either).

Also even though the documenation very clearly states that -IF and -SET must be terminated with
a semicolon END on a new line works for them as well.

-SET &COUNTER =0;
-REPEAT ENDREPEAT WHILE &COUNTER NE 10
END
-SET &COUNTER = &COUNTER +1;
-TYPE COUNTER is &COUNTER
-ENDREPEAT TYPE The End

-SET &COUNTER =0
END
-IF &COUNTER EQ 0 GOTO ENDREPEAT
END
-ENDREPEAT TYPE The End &COUNTER

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

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

WHERE field EQ field ...would you use WHEN or IF

Focal Point | Privacy Statement | Terms of Service