Question Re: To get address numbers based on building code in Spend Management Q&A

To get address numbers based on building code

Former Member — Wed, 09 Dec 2009 11:17:15 GMT

Hi All,

Here is my requirement.

I need to select all address numbers from ADRC table based on building code.

For instance, say I want all address numbers where building code = ABC.

I can write a simple inner join query between ADRC & ADCP.

But the problem is, the ADRC table can have multiple address numbers. Say, for a specific Partner number & Person number, the ADRC table has following 3 entries. Lets say 3rd one is valid.

Addr 1 = XYZ'.

Addr2 = 'ABC'.

Addr3 = 'MNO'.

Now if I use that select query, it will fetch the second one, though it is the older one which is not valid anymore.

Is there any fields in ADRC with which we can decide which is the recent valid address number?

Kindly provide your valuable help for this scenario.

Re: To get address numbers based on building code

Former Member — Wed, 09 Dec 2009 13:33:08 GMT

Is there any fields in ADRC with which we can decide which is the recent valid address number?

Would DATE_FROM and DATE_TO fields help you with validity of the address?

Re: To get address numbers based on building code

Former Member — Thu, 10 Dec 2009 03:46:40 GMT

Nopes.

For all the multiple entries, DATE_TO and DATE_FROM are the same.

Re: To get address numbers based on building code

Former Member — Thu, 10 Dec 2009 15:18:48 GMT

How do you define "valid"?

Re: To get address numbers based on building code

Former Member — Tue, 09 Feb 2010 10:14:27 GMT

Have found out the way to get this.