Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

To find non over lap values in the file comparing witt table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
Divyananda

New User


Joined: 11 Dec 2008
Posts: 9
Location: Chennai

PostPosted: Fri Aug 14, 2009 9:39 am    Post subject: To find non over lap values in the file comparing witt table
Reply with quote

Hi All,

I have a requirement to code a new program to update a table(eg: Account_num_Range) for account ranges of different Banks.

The scenario is -

The Account-num-range table contains Account_number ranges of different bank. Now a NEW BANK account rangeS recieved through a sequential file needs to be updated into Account-num-range table through the new program after validation of some fields by calling validation modules(standard).

Unfortunately the Account ranges of NEW BANK are overlapped with existing account ranges in the table.

Now the new program should find the non overlapping account ranges by comparing the NEW BANK account ranges with that of existing ranges of different banks in the table and insert them into table..

The Account-num-range table will be like

Low range(char 20), High_range(char 20), Bank_Id(char 2), ....
and the primary Key is Low_range and High Range columns

Overlapping has happened in all possibilities like- same range exist in both file and table, and NEW BANK range exist within a Accunt-range in the table and one NEW BANK account ranges exist (spilt) in two existing account ranges in the table, (see the attachment )

The Reason to update the table through program is - every week the - The file containing the existing Account ranges and with new ranges if any will be delivered by the NEW BANK and we have to update it into the table with non overlap ranges. and the program logic is to Delete all the existing Account ranges for the new bank and then open the flat file read and validate some mandatory fileds(standard) and then identify the non-overlapping rows and update them to the table.

Kindly any one guide how to find the non overlapping rows LOGIC in the program.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Aug 14, 2009 7:17 pm    Post subject:
Reply with quote

Hello,

Suggest you post a bit of the .xls data using the code tag. Many of our contributers are not permitted to open attachments due to their site security rules.

If a new bank allows a range that duplicates something already in the system, are the new overlapping account numbers rejected? Seems to me that if there were enough banks in the system there might be a lot of overlap.

Has a change to the range_table that uses the bank id as well as the hi-lo range been considered?

If you post a bit of data that demonstrates the requirement, someone may have a more useful reply.
Back to top
View user's profile Send private message
Divyananda

New User


Joined: 11 Dec 2008
Posts: 9
Location: Chennai

PostPosted: Mon Aug 17, 2009 4:32 am    Post subject:
Reply with quote

Hi Dick,

Thanks for your reply... icon_smile.gif

Actually as of now SYSTEM has 673 banks data in it and the file which we recieve from NEW bank has around 1630 account ranges.


The Attachement contains the table structure and the file format

Here I try to explain-

The table structure will be -
Code:


LOW_RANGE   HIGH_RANGE   BANK_ID   COL-1   COL--2   COL-3
0              10           AB         
15             30           BC         
31             45           CD         
50             60           AB         
80            100           BC


The other columns will be like CREATE_TS, START_DATE, EXP_DATE, ACTIVE_STATUS ...etc...

And the file which will be Recieved will be of structure below.

Code:
for eg-
01 NEW-BNK-AC-RANGE-FILE
    04 AC-FILE-HDR.
         06 HEADER-TAG    PIC X(4).
         06 CREATION-TS   PIC 9(14).
         06 HEADER-FILLER PIC X(140).
         06 END-CHAR      PIC XX.
       04 NEW-BNK-AC-RANGE-DTL REDEFINES NEW-BNK-AC-RANGE-FILE.
         06 BNK-ID         PIC X(11).
         06 BNK-NAME    PIC X(60).
         06 AC-RANGE-LENGTH    PIC 99.
         06 AC-RANGE    PIC X(12).
         06 DB-CR-AC    PIC X.
         06 DTL-FILLER   PIC X(30).
         06 END-CHAR    PIC XX.
      04 AC-FILE-TRL REDEFINES AC-FILE-HDR.
         06 REC-CNT       PIC 9(10).
         06 TRAILER-FILLERPIC X(144).
         06 END-CHAR     PIC XX.
Code'd

the program calculte the account-range by field AC-RANGE.

i.e program reads the file and suppose the 321 is the account range in file then program calculate the range as 3210000000000000000 and high range as 3219999999999999999

now once the program calculates the ac range and other validation is done then program loads the variables (HOST variables) with values ready to insert into ACoount-num-range table if the ACoount_range is not overlapping with other ranges in the table.

the problem is here on how to find whether the account range (calculated from File) is overlapping with existing rows of the table.

for Eg if the calculated range is-
Code:


LOW_RANGE   HIGH_RANGE   BANK_ID   COL-1   COL--2   COL-3
0              25           NE         
51             79           NE         
80            125           NE   


Once the program updates the table with new banks non overlapping Account number then it should look like-


Code:


LOW_RANGE   HIGH_RANGE   BANK_ID   COL-1   COL--2   COL-3
0              10           AB
11             14           NE
15             30           BC
31             45           CD
46             49           NE
50             60           AB
80            100           BC
101           125           NE           


Hope I have provided necessary info....
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Aug 19, 2009 6:55 am    Post subject:
Reply with quote

Hello,

How many entries exist in the "range table"?

What happens when a new bank presents a range that is completely unavailable?

Is there a maximum number of range splits for an individual bank? For example, the "new" bank has 1630 account ranges. Might these become 4000 due to values already being taken?

After the update this entry is shown:
Code:

46             49           NE
How was this determined? It does not follow what i thought were the "rules". Possibly i don't yet understand the rules. . .
Back to top
View user's profile Send private message
Divyananda

New User


Joined: 11 Dec 2008
Posts: 9
Location: Chennai

PostPosted: Wed Aug 19, 2009 9:34 am    Post subject:
Reply with quote

Hello Dick,

Again thanks for your reply,

The Account_num_range table has all the account ranges of the system and in realtime when ever a transaction is made the realtime programs identify whether the transaction belongs to our banks(system) or not from this table by finding whether the transaction account exist in the system(table)

Now I extremely apologize for giving wrong data, it should be
Code:
 

61      79     NE.



What i supposed is to just find the non overlap rows out of 1600 account ranges and insert it into the table for New bank.

suppose out of 1600 rows only 180 (after calculated) are found to be non-overlap then only those rows are inserted into the table for NEW bank.

After so many R&D now I had found the account ranges from table where the NEW bank account ranges are overlapped using below queries---

At first I will read the Low-range and High-range from file and move to ws-low-range and ws-high-range respectively
and now I will execute below Queries

Code:

SELECT MAX(LOW_RANGE_NO)                                                   
  INTO :WS-MIN :IN-MIN                             
  FROM  ACCOUNT_NUM_RANGE                                                   
 WHERE                                                   
   ACTIVE_STATUS   = 'A'                   
   AND LOW_RANGE_NO       <= :WS-LOW-RANGE-NO     


AND

Code:

SELECT MAX(LOW_RANGE_NO)                         
  INTO :WS-MAX :IN-MAX                           
  FROM ACCOUNT_NUM_RANGE                                                 
 WHERE
   ACTIVE_IN_PERIOD    = 'A'                 
   AND LOW_RANGE_NO       <= :WS-HIGH-RANGE-NO   


The result gave me the account ranges from the table where NEW banks account range are overlapped if any.

Now I am concentrating on how to find the exact non overlap rows like what you asked

Code:

11  14   NE  (46  49)


which falls in the NEW bank range of 0 - 25 and falls in table entries 0-10 and 15 - 30 and out of which 11- 14 is non over lapped.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Aug 19, 2009 9:47 am    Post subject: Reply to: To find non over lap values in the file comparing
Reply with quote

Quote:
Now I extremely apologize for giving wrong data
Not to worry - as long as we get to the same understanding icon_smile.gif

I suspect zero is the lowest lo-range. What is the highest hi-range (nnnnn)?

Something that comes to mind is to define an array of one-byte entries (nnnnn) that would be an indicator of whether that number was "taken" or if it was "available". This array (with just a bit of work) could also be used to detect overlap. Each individual byte could accumulate from zero to 255 if used as a binary number rather than just an on/off indicator.

Just a thought. . .
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Converting Variable block file to Fix... bhavana yalavarthi DFSORT/ICETOOL 11 Tue May 16, 2017 2:20 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
No new posts Issue with NDM process to transmit ES... chetanambi All Other Mainframe Topics 6 Wed May 03, 2017 10:52 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us