# Regarding FIND NUMBER??? **URL:** \ **Category:** Forum\ **Tags:** Adabas-Natural, Natural, Natural-on-Mainframes\ **Created:** [April 17, 2006, 11:14am UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824 "2006-04-17T11:14:11Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![rahul\_mf](https://avatars.discourse-cdn.com/v4/letter/r/f08c70/32.png) [@rahul\_mf](https://techcommunity.softwareag.com/u/rahul_mf)\ **Post date:** [April 17, 2006, 11:14am UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/1 "2006-04-17T11:14:11Z")
Hi all, What is the difference in using a “FIND NUMBER” statement and using a “HISTOGRAM with \*NUMBER” ??? Waiting for reply. Rahul ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [April 17, 2006, 6:52pm UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/2 "2006-04-17T18:52:45Z")
FIND NUMBER will return just the overall number of records a FIND with the same search criteria would return, it doesn’t open a processing loop. HISTOGRAM will open a processing loop and present a descriptor value along with the number of records containing that value on every iteration. ---
**Author:** ![rahul\_mf](https://avatars.discourse-cdn.com/v4/letter/r/f08c70/32.png) [@rahul\_mf](https://techcommunity.softwareag.com/u/rahul_mf)\ **Post date:** [April 18, 2006, 10:42am UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/3 "2006-04-18T10:42:12Z")
Thanks Wolfgang. 😃 ---
**Author:** ![Wilfried\_B](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/wilfried_b/32/83_2.png) [@Wilfried\_B](https://techcommunity.softwareag.com/u/Wilfried_B)\ **Post date:** [April 18, 2006, 1:12pm UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/4 "2006-04-18T13:12:31Z")
If you want the number of records for a specific key value FIND NUMBER is faster than HISTOGRAM(1). If you want the number of records for each occurrence in a specific key range a HISTOGRAM is the solution. If you only want the number of records within the key range you use FIND NUMBER. If it is a complex criterion, you use FIND NUMBER, too. Another application of FIND NUMBER is the provision of the record list for later processing: FIND NUMBER … RETAIN AS. You can also use FIND NUMBER … RETAIN AS for union, intersection and difference of retained sets.: ```auto FIND NUMBER view WITH set1 OR set2 RETAIN AS set-union FIND NUMBER view WITH set1 AND set2 RETAIN AS set-intersection FIND NUMBER view WITH (set1 OR set2) AND NOT (set1 AND set2) RETAIN AS set-difference ``` ---
**Author:** ![Steve\_Robinson](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/steve_robinson/32/107_2.png) [@Steve\_Robinson](https://techcommunity.softwareag.com/u/Steve_Robinson)\ **Post date:** [April 18, 2006, 6:38pm UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/5 "2006-04-18T18:38:30Z")
In Report Mode, FIND NUMBER permits a WHERE clause involving other fields from a record. There is no such capability for HISTOGRAM. steve ---
**Author:** ![rahul\_mf](https://avatars.discourse-cdn.com/v4/letter/r/f08c70/32.png) [@rahul\_mf](https://techcommunity.softwareag.com/u/rahul_mf)\ **Post date:** [April 19, 2006, 10:28am UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/6 "2006-04-19T10:28:06Z")
But Steve, in HISTOGRAM also we have a WHERE clause ( as per Natural programming guide , P-142). But I guess there is no use of that WHERE clause because it is mentioned there → “The field specified in the WHERE clause must be the same as in the main clause of the HISTOGRAM statement” Could you please clarify this??? ---
**Author:** ![Wilfried\_B](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/wilfried_b/32/83_2.png) [@Wilfried\_B](https://techcommunity.softwareag.com/u/Wilfried_B)\ **Post date:** [April 19, 2006, 1:12pm UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/7 "2006-04-19T13:12:23Z")
In a HISTOGRAM statement you always have access only to the descriptor field the histogram uses, i.e. your view definition may only contain this descriptor and no other field from the ddm. You can of course redefine that field as you wish and write a where clause on any of this redefinition fields. I think, your problem is understanding the WHERE clause itself. It has nothing to do with the WITH clause in a FIND statement. The WITH is evaluated by ADABAS, the WHERE is evaluated by NATURAL, i.e. after the record has been passed from ADABAS to NATURAL but before the record is available to the body of the processing loop. The WHERE clause effects \*COUNTER but not \*NUMBER (in a FIND), as \*NUMBER is an ADABAS provided value, \*COUNTER counts how often the body of the processing loop has been entered. Similar statements are ACCEPT/REJECT except that these statements are coded within the body of the processing loop and thus don’t effect \*COUNTER (the body of the processing loop has been entered!) ---
**Author:** ![Matthias1](https://avatars.discourse-cdn.com/v4/letter/m/9fc348/32.png) [@Matthias1](https://techcommunity.softwareag.com/u/Matthias1)\ **Post date:** [April 19, 2006, 2:04pm UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/8 "2006-04-19T14:04:24Z")
> [@rahul\_mf](#): > > “The field specified in the WHERE clause must be the same as in the main clause of the HISTOGRAM statement” I think there’s at least one more possiblity than using View-Fields. Example: ```auto define data local * 01 VIEW-NAME view of DDM-NAME 02 AE * end-define * histogram VIEW-NAME for AE where *NUMBER > 10 * display *NUMBER VIEW-NAME * end-histogram * end ``` A Read or Find for that purpose would be slower! ---
**Author:** ![Wilfried\_B](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/wilfried_b/32/83_2.png) [@Wilfried\_B](https://techcommunity.softwareag.com/u/Wilfried_B)\ **Post date:** [April 19, 2006, 10:41pm UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/9 "2006-04-19T22:41:28Z")
To become a quibbler: \*number ist not a field, it is a system variable. Of course you can use any variable in the where clause. But if you want to compare to anything you have read (i.e. “field”), this must be defined in the view. And in a histogram you can only use a view that contains no other “field” than the descriptor. Now I stop quibbling. ---
**Author:** ![rahul\_mf](https://avatars.discourse-cdn.com/v4/letter/r/f08c70/32.png) [@rahul\_mf](https://techcommunity.softwareag.com/u/rahul_mf)\ **Post date:** [April 20, 2006, 9:40am UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/10 "2006-04-20T09:40:06Z")
Wilfried, does it mean that the above line of code " histogram VIEW-NAME for AE where \*NUMBER \> 10 " is not valid??? ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [April 20, 2006, 3:16pm UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/11 "2006-04-20T15:16:13Z")
> [@rahul\_mf](#): > > Wilfried, does it mean that the above line of code " histogram VIEW-NAME for AE where \*NUMBER \> 10 " is not valid??? It is valid and it does work 😉 ---
**Author:** ![Curtis\_Pew](https://avatars.discourse-cdn.com/v4/letter/c/5f9b8f/32.png) [@Curtis\_Pew](https://techcommunity.softwareag.com/u/Curtis_Pew)\ **Post date:** [April 24, 2006, 10:56pm UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/12 "2006-04-24T22:56:46Z")
I think some of the confusion here arises because Natural does such a good job of abstracting what Adabas is doing, but to understand the difference between FIND NUMBER and HISTOGRAM you need to understand the Adabas back end. First, remember that Adabas stores records in its DATA container files, and also builds an index for each descriptor in the ASSO container files. A descriptor may be a field in the record (a normal descriptor), a part of a field (a subdescriptor), a concatenation of parts or all of two or more fields (a superdescriptor), or a value calculated from one or more fields (a hyperdescriptor.) A descriptor index logically consists of a sorted list of values with each value having an associated list of the ISNs of the records matching that value. To satisfy any FIND (Sx) command, Adabas creates a list of the ISNs of all records that meet the find criteria. In the simplest case this is just those records with a particular value of a single descriptor, but in other cases it may involve a range of values, multiple descriptors, and even fields that have not been indexed as descriptors. Normally your program then retrieves each record in the constructed ISN list in turn (using L1/L4 commands), but in the case of FIND NUMBER Adabas merely returns the size of the ISN list, which Natural places in the \*NUMBER system variable. For the HISTOGRAM (L9) command, Adabas reads through a single descriptor index and returns each value with the count of its associated ISN list, which NATURAL again places in the \*NUMBER system variable. (The Adabas command manual calls the L9 command “Read Descriptor Values”, which is probably clearer than “HISTOGRAM”.) If you understand what Adabas is doing “behind the scenes”, the difference between FIND NUMBER and HISTOGRAm is quite clear. ---
**Author:** ![rahul\_mf](https://avatars.discourse-cdn.com/v4/letter/r/f08c70/32.png) [@rahul\_mf](https://techcommunity.softwareag.com/u/rahul_mf)\ **Post date:** [April 25, 2006, 11:55am UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/13 "2006-04-25T11:55:45Z")
Thanks Curtis 😃 ---
**Author:** ![John\_Smith](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/john_smith/32/39012_2.png) [@John\_Smith](https://techcommunity.softwareag.com/u/John_Smith)\ **Post date:** [January 9, 2008, 6:28am UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/14 "2008-01-09T06:28:16Z")
Hi folks, I would like to think on performance, but according to Curtis said, which conclusion we can take? In this issue, we are not going to consider Histogram needs a specific View, Find number can be used with AND or OR criteria. Let consider only performance. 1 ) S1 command is the same, doesn’t matter if you are using FIND, FIND NUMBER or FIND (1), Natural handles it for you, so, only ISQ is retuned on System Variable (\*number); 2 ) S1 command is not the same, Adabas handles it according to the information that you pass by Control Block. Find Number doesn’t have format buffer, and there is no loop to generate RC command; 3 ) L9 is cheaper than S1, because L9 uses only inverted list; so, no AC and DS are used. S1 uses inverted list, depend on the amount of the records, Work part 2 can be used, AC and DS are used - the first record is returned on S1 command; 4 ) Find number is S1 command, even if Natural handles the command. So, no format buffer (FB), loop (hence, no RC command is issued), and ISQ is placed on \*number (system variable); 5 ) To check only if the record exist - descriptor with 1 or no record: a. Find number should be used instead of Histogram (1); 6 ) To check only if the record exist - descriptor with large records: a. Histogram (1) should be used instead of Find Number; 7 ) If you use Find Number, only S1 is generated, since there is no loop (no RC) 8 ) If you use Histogram (1), L9 and RC are generated; 9 ) Find number is always the best choose, only if the amount of ISN is enough on NSISN parameter; After all, which statement is better to use? Find Number or Histogram (1)? Thanks. ---
**Author:** ![John\_Smith](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/john_smith/32/39012_2.png) [@John\_Smith](https://techcommunity.softwareag.com/u/John_Smith)\ **Post date:** [January 12, 2008, 3:08am UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/15 "2008-01-12T03:08:59Z")
Hi folks, Does anyone have any suggestion, comments or any explanation about the issue above? Thanks. ---
**Author:** ![Ralph\_Zbrog](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/ralph_zbrog/32/78_2.png) [@Ralph\_Zbrog](https://techcommunity.softwareag.com/u/Ralph_Zbrog)\ **Post date:** [January 12, 2008, 5:31am UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/16 "2008-01-12T05:31:39Z")
There are several reasons for using FIND NUMBER versus HISTOGRAM, but, in my experience, there are no files whose contents apply solely to one of those criteria. There is always some subset of key values that would be processed better by the “other” statement. But you know only after the fact, during execution, that the other statement would have been better. To simplify the decision, I base it on the answer to one question: Is your descriptor unique? If so, then avoid HISTOGRAM’s RC call and code ```auto FIND NUMBER EMPLOYEES WITH NAME = 'ZBROG' ``` otherwise use ```auto HISTOGRAM EMPLOYEES FOR NAME FROM 'ZBROG' TO 'ZBROG' END-HISTOGRAM ``` If you’re not yet at Natural 4, change the HISTOGRAM to use a THRU clause. ```auto HISTOGRAM EMPLOYEES FOR NAME FROM 'ZBROG' THRU 'ZBROG' END-HISTOGRAM ``` And if it’s not easy to provide the THRU value, use ```auto HISTOGRAM EMPLOYEES FOR NAME FROM 'ZBROG' ESCAPE BOTTOM END-HISTOGRAM ``` ---
**Author:** ![John\_Smith](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/john_smith/32/39012_2.png) [@John\_Smith](https://techcommunity.softwareag.com/u/John_Smith)\ **Post date:** [January 12, 2008, 5:00pm UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/17 "2008-01-12T17:00:02Z")
Hi Ralph, I understand that you agreed with me about the item 5. Thanks, does anyone want to add comments, suggestion about the other itens? ---
**Author:** ![Douglas\_Kelly](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/douglas_kelly/32/102_2.png) [@Douglas\_Kelly](https://techcommunity.softwareag.com/u/Douglas_Kelly)\ **Post date:** [January 13, 2008, 12:42am UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/18 "2008-01-13T00:42:40Z")
---
**Author:** ![Douglas\_Kelly](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/douglas_kelly/32/102_2.png) [@Douglas\_Kelly](https://techcommunity.softwareag.com/u/Douglas_Kelly)\ **Post date:** [January 13, 2008, 12:44am UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/19 "2008-01-13T00:44:20Z")
point 6 is irrelevant to the discussion: the size of the record will not impact the performance of either command. If there was a blanket answer “use command x rather than command y” for all cases, Adabas wouldn’t need to support “command y” or Natural would be modified to generate “command x” in all cases where it now uses “command y”. As Ralph alludes to - even if you run a test against a set of data and programs, the performance would vary for the selection criteria or data. A test against today’s scenario may yield a different performance result when run against tomorrow’s scenario. For scenarios other than the simple existence check (counting, complex criteria, multiple-millions of records, etc, etc) you would have to define what you are trying to achieve more specifically. My general answer would be “it depends” - it depends on what your environment - data, processing, application, etc - and what the “question” is. For a simple existence check, I generally use the FIND NUMBER as a first choice: - it is easier to code (as a developer, a significant consideration) - it allows me to add complex criteria (AND, OR, etc) readily - the intention of it is clear to the person reading the program I will use a HISTOGRAM in an existence check to - check a range of values such as might be found with a reverse date index (find most recent record for this key starting from datevalue) - count a range of values - check values involving PE’s (that is descriptors on a PE element or superdescriptors with a PE element component) - if the number of values to be counted is likely to be large that it would impinge on NSISN. This is seldom an issue for a simple existence check. Performance for both commands is nearly always “good enough”. I will almost always get more bang for my performance and tuning efforts looking elsewhere. ---
**Author:** ![Matthias1](https://avatars.discourse-cdn.com/v4/letter/m/9fc348/32.png) [@Matthias1](https://techcommunity.softwareag.com/u/Matthias1)\ **Post date:** [January 14, 2008, 6:31pm UTC](https://techcommunity.softwareag.com/t/regarding-find-number/60824/20 "2008-01-14T18:31:30Z")
> [@John Smith](#): > > 6 ) To check only if the record exist - descriptor with large records: > a. Histogram (1) should be used instead of Find Number; > [@Douglas Kelly](#): > > point 6 is irrelevant to the discussion: the size of the record will not impact the performance of either command. I think point 6 is relevant. John Smith wrote about “large records”. I would say: “large number of records”. For example imaging the question: Is there any descriptor-value starting with “AB”? I would use HISTOGRAM here… ```auto define data local 01 #search (A15) init <'AB'> 01 #low (A15) init full length 01 #high (A15) init full length * 01 hist-id view of example-file 02 id-field (A15) * end-define * COMPRESS #search #low into #low LEAVING NO COMPRESS #search #high into #high LEAVING NO HIST-1. HISTOGRAM (1) hist-id for id-field FROM #low THRU #high END-HISTOGRAM IF *COUNTER(HIST-1.) = 0 write "there is no value starting with" #search else write "there is a value starting with" #search END-IF ``` [Next page](https://techcommunity.softwareag.com/t/regarding-find-number/60824.md?page=2)