At Summit this year, many of the presentations still had 'IF' statements. It was my understanding - some time ago! - that we should be using 'WHERE' instead of 'IF'.

Can someone explain to me - again! - what the difference is between the two?

Thanks!

I haven't used an IF statement for years.

There are several differences, one of which is that an IF statement will allow alpha values without quotes, which I think is sloppy:

TABLE FILE CAR
PRINT
SEG.COUNTRY
SEG.MODEL
IF COUNTRY EQ ENGLAND
END

I'm sure others will tell you why they still use IF statements.

Tom,

I agree with Francis, here is some documentation from IBI:

quote:

This topic is also covered in IBI Course(s): Course 102

In TABLE, IF tests only recognize OR connectors. A field can't be equal to two different values. Internally, 'AND' is treated as 'OR' in an IF test. For example, the test "IF COUNTRY EQ 'ENGLAND' AND 'FRANCE'" is converted internally to "IF COUNTRY EQ 'ENGLAND' OR 'FRANCE'".

The same is not true of WHERE tests. "WHERE COUNTRY EQ 'ENGLAND' AND 'FRANCE'" will return 0 records. In a WHERE test, 'AND' is used to connect two expressions. Using parenthesis, this expression can be rewritten as WHERE (COUNTRY EQ 'ENGLAND') AND ('FRANCE'). In this example, COUNTRY EQ 'ENGLAND' is the first expression. The second expression, 'FRANCE' is evaluated as "WHERE 'FRANCE' EQ 'TRUE'". The second expression can never be true, so the entire expression WHERE COUNTRY EQ 'ENGLAND' AND 'FRANCE' will never be true. Therefore, zero records will always be returned when using this type of syntax.

By-the-Way, are you from the Univ of Nebraska???

Tom

Thank you for the input!!

Tom, I am not from UNO. I am from Physicians Mutual.

Tom

I have only found a single instance where it was better to use an IF instead of WHERE. That is when I was comparing a field to a list of values in an external file, along with many other complex criteria. The syntax is different for an IF vs. WHERE and for whatever reason, I could get IF to work right with multiple complex criteria but WHERE would not. Probably something I was just missing, but in any case the IF worked so I stuck with it. In all other case, I always use WHERE.

As mentioned, IF only seemed to work consistently (or at least what I consider to be consistent) when the criteria were simple. Any compound/complex statements required a WHERE. In most cases, its user preference. I would suggest sticking with WHERE.

Darin,

Your problem may have been the size of the file you were comparing against. I can't remember the exact limits, but I know that with a WHERE it is very small. The syntax is a little easier with an IF when searching for a value in a file and I agree that it is about the only time I still use IF.

I have lots of code where I used IF before WHERE was an option and there used to be a problem where:

WHERE datefield IS MISSING

would not be passed to Oracle and

IF datefield IS MISSING

would be passed, but that has been fixed. The only time I use IF now is when using a list in an external file and the file is larger than 16K. The file can be 32K in size with IF and only about 16K with WHERE.

There is a form of selection test available with IF but not with WHERE:

"IF x IS TRUE", where x is a numeric variable, is treated as "IF x NE 0".

That allows you to express decision logic quite naturally:

DEFINE FILE CAR
 TAKE/I1=(COUNTRY CONTAINS 'Y') 
      OR (CAR CONTAINS 'A');  
END
TABLE FILE CAR
 LIST CAR BY COUNTRY 
 IF TAKE IS TRUE
END

1. When WHERE was first introduced (way back when), it was because of SQL that had a WHERE clause and, people being what they are, WHERE became a necessary feature.

2. IF is very straightforward: IF field OP value. Nothing else, no field to field comparison, no OR between conditions. In order to do that, one had to create a DEFINE field and use IF on the result.

3. So, when WHERE came along, FOCUS translated it to an IF where possible, otherwise to a DEFINE and IF.

4. With SQL passthru, FOCUS examines the WHERE and if it decides that it is "good" SQL then the WHERE is sent to the database, otherwise FOCUS will do the screening (hence the importance of tracing).

5. With FOCUS files what is described in (3) is, as far as I know, still used.

Old hands at FOCUS, like me, cannot abandon IF so quickly: 2 characters instead of 5! What a savings!

Danny,

The savings part is not that important. I'm used to lining up my code, so my IF also takes 5 positions.
But, I'm still using the IF statement more than the WHERE, probably because it is what I grew up with. You're right, WHERE used to be translated into a DEFINE, which is being executed after having filtered on the database values, so after any direct IF. Don't know if this is still the case, if it is, using IF where possible might improve performance (a little).

I apologise in advance for this but it's been begging to be written so here it is.

I prefer to use where where possible, although if I were to use if then where would I use if in preference to where and where would I use if? If I were you and where you are then I would probably not worry at all and decide where and if I’d use where or if.

Sorry again

T

GamP, it was a joke, of course.

Tony, very nice! No need for apologies.

switching to the format:
IF &field EXACTLY &operator &value

allowed our heavily-parameterized reports to work fine even when the user entered special characters such as dollar signs or ampersands. this wasn't possible without a LOT of extra code when using WHERE.

Danny,
Of course it was a joke, I saw through that.

Tony - great, could not have thought that one up - I'll keep it and use it if needed where and whenever I can.

Dan, nice thing this EXACTLY option. Didn't know it was there - Thanks!

And another undocumented feature. Of course you can find them in the cases but why aren't they in the MANUAL???

Careful Danny you're starting to encroach on Francis' domain

T

Hello everyone, I am glad this post exists, because it talks to heart of our problem we just discovered. Thanks Dan, your IF &field EXACTLY &operator &value worked perfectly with our issue.

Now to my issue and hopefully your help. We have a lot of parameterized that contain listboxes of codes (Service, Discount, Package Etc..) The codes can be setup using any combination of enterable keyboard characters. We ran across the probem with the + sign and worked around that issue, but the $ sign is a problem. We used Dan's technique, but I was wandering if there is a setting to turn off the masking behavour associated with the $ sign?

For example code 3$: The SQL statment would be WHERE SERV_CDE LIKE '3_ ' instead of WHERE SERV_CDE = '3$' I now understand why this occurs and the other masking behaviours using $ and * together. We can use the IF logic, but it would be simpler if we could turn off the masking behaviour with some type of configuration setting similar to HTMLENCODE.

David