Focal Point
[SOLVED] WHERE * NOT_IN

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/2481075681

September 19, 2006, 08:30 AM
mark66
[SOLVED] WHERE * NOT_IN
Hi all,

I am having some difficulty trying to select data source values that are not in a list.

Example:

TABLE FILE GGSALES
PRINT *
WHERE ST NOT_IN ('CT','NY');
END
-RUN

Returns the error:
(FOC263) EXTERNAL FUNCTION OR LOAD MODULE NOT FOUND: NOT_IN

I have checked the Help example and all looks ok:
"WHERE bank_name NOT_IN ('ASSOCIATED BANK', CITIBANK)"

I have tried diffenent combinations of NOT_IN / NOT IN etc. Any ideads?

Using version 535.

Thanks

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


WebFocus 765. iSeries v5r4
September 19, 2006, 08:48 AM
funsutton
Webfocus puts the NOT early in the clause:

Try this:

 TABLE FILE GGSALES
PRINT *
WHERE NOT ST IN ('CT','NY');
END
-RUN 



-Brian

Webfocus v7.6.1 on Windows XP
September 19, 2006, 09:54 AM
mark66
Thanks Brian, that made it work fine.

Had you come across this before? Seems like a strange order to me!


WebFocus 765. iSeries v5r4
September 19, 2006, 10:21 AM
funsutton
It's just the webfocus language...how they do things. I came from a SQL background, so I did run across this myself.


-Brian

Webfocus v7.6.1 on Windows XP
November 25, 2013, 06:06 PM
StevenTCramer
So if the example in the documentation does not work... Why doesn't IB update the documentation?


7.7.03, Windows
HTML, Excel, PDF,
November 26, 2013, 07:25 AM
Twanette
Hi Steven,

I checked the "Creating Reports With WebFOCUS Language" Version 8.0.0 and Version 7 Release 7 documentation, and it specifies the following:

Syntax:
WHERE [NOT] fieldname IN FILE file
where:
fieldname
Is the name of the selection field. It can be any real or temporary field in the data source.
file
Is the name of the file.
This is the ddname assigned by a DYNAM or TSO ALLOCATE command for z/OS.


So it would appear that the documentation is up to date.


WebFOCUS 8.2.06 mostly Windows Server
November 26, 2013, 07:26 AM
Alan B
quote:
WHERE bank_name NOT_IN ('ASSOCIATED BANK', CITIBANK)

This is MAINTAIN syntax, not TABLE syntax, check the help examples and this is from the Maintain Language Reference manual.


Alan.
WF 7.705/8.007
November 26, 2013, 11:10 AM
njsden
quote:
Had you come across this before? Seems like a strange order to me!


If you look at it from the standpoint of "negating an expression" it doesn't look that strange anymore. Using additional brackets makes this easier to understand:

WHERE NOT (ST IN ('CT','NY'))



quote:
It's just the webfocus language...how they do things


SQL operates quite similarly in this case although it allows for some more "flexibility" when it comes to syntax. The following construct is pretty much the same as the WebFOCUS counterpart:

select ... from ...
 where not st in ('CT','NY')


And is also equivalent to this:

select ... from ...
 where st not in ('CT','NY')


Some may actually argue that "not in" looks weird as it seems as if they placed 2 consecutive operators instead of 1. Roll Eyes



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.