on 11-06-2022 11:10 AM
Hi,
I am trying to work out what the correct email address is for our BP's both currently and historically.
Currently I am joining the following tables together;
BUT000
BUT021_FS - join to BUT000 on PARTNER; filter on ADR_KIND = 'XXDEFAULT'
BUT020 - join to BUT021_FS on ADDRNUMBER
ADRC - join to BUT020 on ADDRNUMBER
ADR6 - join to ADRC on ADDRNUMBER
ADRU - join to ADR6 on ADDRNUMBER, CONSNUMBER, VALID_FROM and VALID_TO; filter on COMM_TYPE = 'INT' and COMM_USAGE = 'AD_DEFAULT'
If I do this I kind of get what I am looking for but there seems to be a number of data anomalies.
For example ADR6 contains lots of rows where the VALID_FROM and VALID_TO dates are empty. Is this the expected behaviour for this table?
I can get dates from some of the other tables (BUT020 - ADDR_VALID_FROM and TO or BUT021_FS - VALID_FROM and TO), but if I use these dates I sometimes get instances of overlapping dates - again is this to be expected and how the data fits together?
Is the above method overly complicated and do I even need to use the BUT021_FS table?
I tried using just the BUT000, ADR6 and ADRU tables, but using this method there were a lot of rows without VALID_FROM and TO columns populated, so surely there need to be joined to another table to establish a valid from date?
Ultimately I want to know the PARTNER, SMTP_ADDR, VALID_FROM and VALID_TO information for every BP in the system.
Thanks in advance.
User | Count |
---|---|
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.