Query on histogram



Software AG's platform-independent programming language with full support for open-source and Internet applications

Query on histogram

Postby diptisaini » Sat Oct 01, 2011 3:55 pm

What is the value of *isn in histogram if a descriptor is a MU or PE field? Please explain me with a example?

Also In [color=#FF0000]FIND NUMBER or HISTOGRAM which one is better[/color] ?(if we have to check that the particulary record is exist or not).

Thanks.
diptisaini
 
Posts: 90
Joined: Sun Mar 14, 2010 5:12 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Query on histogram

Postby RGZbrog » Tue Oct 04, 2011 11:41 am

Rarely will you find PE fields within a descriptor definition, because programmers find them too difficult to use. Such descriptors cannot be used in a READ LOGICAL, so FIND must be used. And when HISTOGRAMed, the results are not what you might expect.

If your applications don't have PE-descriptors, you can play with one in Software AG's Employees file, as in the examples, below.
Everyone should have access to Employees and Vehicles. If you don't, talk to your Natural Administrator, DBA, or Manager. Tell them that I said so.
Ralph Zbrog
Instructor

Let's start with a typical HISTOGRAM example. NAME is an elementary descriptor. In the report, we see unique descriptor values and the number of records which contain those values. For example, we see that ADKINSON appears in 8 records in the Employees file.
DEFINE DATA LOCAL
1 EMP    VIEW EMPLOYEES
  2 NAME
END-DEFINE
HISTOGRAM (10) EMP FOR NAME
  DISPLAY *COUNTER
          NAME
          *NUMBER
END-HISTOGRAM
END

Page     1                                                   10/03/11  14:19:24
 
    CNT             NAME            NMBR
----------- -------------------- -----------
 
          1 ABELLAN                        1
          2 ACHIESON                       1
          3 ADAM                           1
          4 ADKINSON                       8
          5 AECKERLE                       1
          6 AFANASSIEV                     2
          7 AHL                            1
          8 AKROYD                         1
          9 ALEMAN                         1
         10 ALESTIA                        1

In the following example, we populate the CURR-CODE and SALARY fields of the periodic group INCOME to generate values for the CURRENCY-SALARY descriptor. The sole purpose of #PE-DESCRIPTOR is to create a pretty header for the report. Because CURRENCY-SALARY is a combination of an alpha and a packed value, #CURR and #SAL are defined to list human-readable values.
DEFINE DATA LOCAL
1 EMP    VIEW EMPLOYEES
  2 PERSONNEL-ID
  2 NAME
  2 C*INCOME
  2 CURR-CODE (4)
  2 SALARY (4)
1 HST    VIEW EMPLOYEES
  2 CURRENCY-SALARY          2 REDEFINE CURRENCY-SALARY
    3 #PE-DESCRIPTOR
      4 #CURR (A3)
      4 #SAL (P17) (EM=ZZZ,ZZ9)
END-DEFINE
EMP.PERSONNEL-ID  := 'Ralph'
EMP.NAME          := 'ZBROG'
EMP.CURR-CODE (1) := 'Z01'
EMP.SALARY    (1) := 100001
EMP.CURR-CODE (2) := 'Z02'
EMP.SALARY    (2) := 100002
EMP.CURR-CODE (3) := 'Z01'
EMP.SALARY    (3) := 100001
STORE EMP
RESET EMP.CURR-CODE (*)
      EMP.SALARY (*)
EMP.PERSONNEL-ID  := 'Zbrog'
EMP.CURR-CODE (1) := 'Z01'
EMP.SALARY    (1) := 100001
EMP.CURR-CODE (2) := 'Z02'
EMP.SALARY    (2) := 100002
EMP.CURR-CODE (3) := 'Z03'
EMP.SALARY    (3) := 100003
EMP.CURR-CODE (4) := 'Z01'
EMP.SALARY    (4) := 100001
STORE EMP
*
H.
HISTOGRAM HST FOR CURRENCY-SALARY FROM 'Z'
                                        TO 'Z03' - H'FF'
  DISPLAY #PE-DESCRIPTOR
          *NUMBER
END-HISTOGRAM
*
BACKOUT TRANSACTION
END

We expect the report to be similar to the first one - unique descriptor values and corresponding counts.
Page     1                                                   10/03/11  15:20:25
 
#PE-DESCRIPTOR    NMBR
 
#CURR  #SAL
----- -------  -----------
 
Z01   100,001            2
Z01   100,001            1
Z01   100,001            1
Z02   100,002            2
Z03   100,003            1

Why do the first three lines report the same descriptor value? We stored 4 such values; 2 in the first record and 2 in the second record. To get the "correct" count, we need to test for equal descriptor values and sum *NUMBER. Why?

Let's add *ISN (H.) to the field list of the DISPLAY statement.
Page     1                                                   10/03/11  15:26:10
 
#PE-DESCRIPTOR     ISN        NMBR
 
#CURR  #SAL
----- -------  ----------- -----------
 
Z01   100,001            1           2
Z01   100,001            3           1
Z01   100,001            4           1
Z02   100,002            2           2
Z03   100,003            3           1

What this demonstrates is that the value in *ISN is appended to the descriptor to create a unique value. The value in *ISN is the descriptor value's occurrence in the PE. The report tells us that the value "Z01 100,001" appears in occurrences 1, 3, and 4, but we are not told whether the 4 values are distributed over 1, 2, 3, or 4 records.

See what happens if we store equal values in a single record.
DEFINE DATA LOCAL
1 EMP    VIEW EMPLOYEES
  2 PERSONNEL-ID
  2 NAME
  2 C*INCOME
  2 CURR-CODE (4)
  2 SALARY (4)
1 HST    VIEW EMPLOYEES
  2 CURRENCY-SALARY          2 REDEFINE CURRENCY-SALARY
    3 #PE-DESCRIPTOR
      4 #CURR (A3)
      4 #SAL (P17) (EM=ZZZ,ZZ9)
END-DEFINE
EMP.PERSONNEL-ID  := 'Ralph'
EMP.NAME          := 'ZBROG'
EMP.CURR-CODE (1) := 'Z01'
EMP.SALARY    (1) := 100001
EMP.CURR-CODE (2) := 'Z01'
EMP.SALARY    (2) := 100001
EMP.CURR-CODE (3) := 'Z01'
EMP.SALARY    (3) := 100001
EMP.CURR-CODE (4) := 'Z01'
EMP.SALARY    (4) := 100001
STORE EMP
*
H.
HISTOGRAM HST FOR CURRENCY-SALARY FROM 'Z'
                                        TO 'Z03' - H'FF'
  DISPLAY #PE-DESCRIPTOR
          *ISN (H.)
          *NUMBER
END-HISTOGRAM
*
BACKOUT TRANSACTION
END

Page     1                                                   10/03/11  15:36:49
 
#PE-DESCRIPTOR     ISN        NMBR
 
#CURR  #SAL
----- -------  ----------- -----------
 
Z01   100,001            1           1
Z01   100,001            2           1
Z01   100,001            3           1
Z01   100,001            4           1

What is the value of *isn in histogram if a descriptor is a MU or PE field?
In other database access statements, *ISN contains the Internal Sequence Number of the associated data record. Since HISTOGRAM does not access a file's data, but only the descriptor values, there is no associated ISN. Positionality is significant in PEs, so a system variable is needed to tell us in which occurrence the descriptor component was found. Because Internal Sequence Number makes no sense within a HISTOGRAM, the *ISN system variable is used instead as a pointer into the Periodic Group. (I would have preferred that Software AG had created a separate variable for this purpose, to avoid all the confusion.)

As for MUs, the documentation for *ISN contains the following statement:
*ISN = 0 if the descriptor is not contained within a periodic group.


Diptisaini, i suggest that you re-post your HISTOGRAM vs FIND NUMBER query as a separate thread.
User avatar
RGZbrog
 
Posts: 101
Joined: Mon Nov 23, 2009 1:34 pm
Location: California, USA
Has thanked: 0 time
Been thanked: 0 time


Return to Natural

 


  • Related topics
    Replies
    Views
    Last post