I haven't coded in a while and now we are upgrading from 7.2.5 to 7.6.5. But in the back of my memory, I thought that when you have IF and multiple WHERE statements, IF (simple) is before WHERE and WHERE TOTAL should last. Also do we not need parens when the 'test' changes
and shouldn't there be a semi-colon at the end of the statements?
Or am I thinking of old ways of coding?!

Example:
WHERE DEFCATTYPE EQ '11'
IF DEFCAT EQ
'110049'
OR '110050'
WHERE CAEXCLUDE NE 'Y'
WHERE POL_NO IS-NOT '$$$$1379$$$$'
WHERE POL_NO IS-NOT '$$$$9999$$$$'
WHERE TOTAL WPREM NE 0 OR EPREM NE 0 OR ILOSSPY NE 0 OR
ECOL1 NE 0 OR ECOL2 NE 0 OR ECOL3 NE 0 OR ECOL4 NE 0 OR
ECOL5 NE 0 OR ECOL6 NE 0 OR ECOL6 NE 0 OR
ECOL7 NE 0 OR ECOL8 NE 0 OR ECOL9 NE 0 OR
ECOL10 NE 0 OR ECOL11 NE 0 OR ILOSSAY NE 0
WHERE CAYEAR EQ &YR OR &YR1 OR &YR2 OR &YR3 OR
&YR4 OR &YR5 OR &YR6 OR &YR7 OR &YR8 OR
&YR9 OR &YR10
WHERE ACCT_DT EQ &ACCTDT OR &ACCTDT1
OR &ACCTDT2 OR &ACCTDT3 OR &ACCTDT4 OR &ACCTDT5
OR &ACCTDT6 OR &ACCTDT7 OR &ACCTDT8 OR &ACCTDT9
OR &ACCTDT10

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

I don't remember anything being positional relative to IF and WHERE but I do have a couple of comments.

I rarely use IF anymore.

Multiple WHERE clauses are ANDed together. If you need to do an OR, put the conditions in the same WHERE clause and use parens for clarity.

WHERE clauses should end in a semi-colon though I forget sometimes.

If any of your variables can come into the program equated to FOC_NONE, then the WHERE should be all on one line or coded such that when the line is removed from focstack, you don't get a syntax error.

I've never really used "IF" when filtering records out; having a broad background in SQL-based databases, the "WHERE" keyword stick to me easier, but to answer your main question, I've mixed WHERE and WHERE TOTAL in the same TABLE FILE request in no particular order and have never found any issues with it.

Did you get any errors or unexpected results when your tried it in your environment?

I would say that has more to do with your own development "style". I wouldn't mix IF's and WHERE's in the same statement especially when using WHERE exclusively allows me to accomplish what I need. I also think it makes your code "neater" and easier for someone else to maintain.

In the same line of thought, I'd usually put all of my WHERE's first as they relate to detail record selection and would leave the WHERE TOTAL at the end as they are naturally evaluated last by WebFOCUS after all data has been aggregated. Once again, it's just a matter of coding style.

Semicolons are optional and in some cases are even disallowed. For instance, the following structure:

WHERE RECORDLIMIT EQ 5;

always throws a syntax error in WF 5.3 due to the semicolon, so I decided not to use them at all in any of my WHERE's.

As far as parenthesis are concerned, their use is not required in the WHERE syntax; they have a semantic meaning when you need to alter the order of evaluation of certain expressions and, when dealing with very long expressions like the ones in your code I'd probably use them to enhance readability but this is yet another personal preference and not something mandatory.

Thanks Ginny! I figured there should be a semi-colon here and there.

Thanks nsjen - You are so correct about style preference in writing code. My questions came to mind because the code was written by someone else. I guess I need to dust off my FOCUS manuals!

FYI, WHERE TOTAL is process much later that WHERE as it must work after Sorting, Aggregation and COMPUTEs.

Thanks Waz - Going back to basics. My style of coding was to use the syntax in the order of what gets processed.
Cheers!

In the training material, there is a great diagram of the process, what and happens when.

If you've been on some of the cources, you may have a copy.

Close - Unfortunately I do not have any of the materials from the course. I will have to reach out to my friends at IBI.