|
View previous topic :: View next topic
|
| Author |
Message |
Divyananda
New User
Joined: 11 Dec 2008
Posts: 9
Location: Chennai
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Divyananda
New User
Joined: 11 Dec 2008
Posts: 9
Location: Chennai
|
|
|
|
Hi Dick,
Thanks for your reply... 
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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:
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 |
|
|
Divyananda
New User
Joined: 11 Dec 2008
Posts: 9
Location: Chennai
|
|
|
|
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
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
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
| Quote: |
| Now I extremely apologize for giving wrong data |
Not to worry - as long as we get to the same understanding
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 |
|
|
|
|
