Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using Oracle 11g. I have these two tables:

Table acct:

acctnbr || Name    || Mailing address   
000001  || Slater  ||   
000002  || Baker   || Alt  
000003  || Parke   ||   

Table addruse:

acctnbr || Address    || Type  
000001  || 1 aby rd   || Pri  
000001  || 2 ctr strt || Res  
000002  || 3 fbry rd  || Alt  
000003  || 4 jnry rd  || Res  

I am listing all the mailing addresses. So the query I need should compare the "mailing address" column from the acct table with the "type" column in the addruse table and list all those addresses. If the mailing address field is null, that means its using a primary address so it should grab the primary address from the addruse table but if there is no primary address then it should grab whatever there is in the addruse table. This is what I have so far:

 select distinct a.acctnbr, a.Name,
   DECODE(a.mailingaddress ,NULL,'PRI',a.mailingaddress) MAILCD,
from acct a, addruse b
where a.acctnbr = b.acctnbr
and   DECODE(a.mailingaddress,NULL,'PRI',a.mailingaddress) = b.type (+)

I dont know how to change this so that it should first check for a primary address and if there is no primary address, it should grab whatever there is like for acctnbr = 000003.

share|improve this question

You don't need to use a decode statement, just build it in to your join clause:

  acct a
  inner join addruse b on 
    (a.acctnbr = b.acctnbr 
     and NVL(a.address,'PRI') = b.address);

SQL Fiddle here. Also, I strongly recommend you start using ANSI-92 joins.

share|improve this answer
@ Mike Fal thanks for your response. This query does exactly what mine does which is to take all null values as primary. My problem is... if there is no primary address in the addruse table then it should show whichever address is there. – Rafey Aug 19 '13 at 20:15
What do you mean "whatever address is there?" There should be a specific rule here, because what if you had multiple addresses and none of them are the primary? How do you prioritize which one is selected? – Mike Fal Aug 19 '13 at 20:20
I know what you mean and you are right. There is another column in the addruse table called effective date. So if there is no primary address in the addruse table then it should select the most current address. What do you think? – Rafey Aug 19 '13 at 20:34
What does he think? He has no idea. What is the business requirement? – Phil Aug 19 '13 at 20:51
There's no easy answer to your business case and you could approach it in a couple of ways. As stated, your problem could be solved by a very complex SQL statement that won't be very efficient. It might be better to put some rules in the application, such as requiring/setting a primary address. To me, that would be better for your data hygiene anyway. – Mike Fal Aug 19 '13 at 20:55

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.