I am using Oracle 11g. I have these two tables:
acctnbr || Name || Mailing address 000001 || Slater || 000002 || Baker || Alt 000003 || Parke ||
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, b.address 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.