tricky issue with query

Hi there,

Here is the issue I am having, basically I need a query that looks at an accounts table and looks up the owners of said accounts (there can be 1 to any number of owners listed).  

It then needs to compare that accounts owner to the owners of it's secondary (statement) account.  What I am trying to detect is when they aren't exactly the same.

I have the following query written and thought that simply including
STRP.PERSNBR <> ATRP.PERSNBR

in the where clause would flesh out where the person in the statement account was the same as the actual account (ATRP)

It certainly fleshed out a lot of accounts dropping the mismatches from over 30,000 to about 250.  However several some of them are false hits.

For example the first account listed as a non match looks something like
Account   Person   Person
1111111   236236   789789
1111111   789789   236236

Open in new window

Obviously they do match they just are in a slightly different order, I had hoped using order by would have corrected for that but it doesn't appear to have

Any ideas how I would change the query below to actually stop reporting these false positives?

The Table used are

ACCTLOAN -> this is the general loan only accounts table
ACCT -> This is the table that holds all the status's of all accounts
ACCTACCTROLEPERS -> This table lists all owners for all accounts
SELECT 
    AL.ACCTNBR
    ,ATRP.PERSNBR AS "Acct Non-Tax Own"
    ,STRP.PERSNBR AS "Stmt Non-Tax Own"

FROM
    ACCTLOAN AL
    ,ACCT A
    ,(SELECT AL.ACCTNBR ,ARP.PERSNBR
      FROM ACCTLOAN AL, ACCTACCTROLEPERS ARP, ACCT A
      WHERE A.curracctstatcd in ('ACT','IACT','DORM')
            AND (UPPER(ARP.ACCTROLECD) = 'OWN' AND ARP.INACTIVEDATE IS NULL)
            AND A.STMTACCTNBR = ARP.ACCTNBR
            AND AL.ACCTNBR = A.ACCTNBR
      ORDER BY 1,2
    )STRP  --LISTS ALL NON-TAX OWNERS OF THE STATEMENT ACCOUNTS

    ,(SELECT AL.ACCTNBR, ARP.PERSNBR
      FROM ACCTLOAN AL, ACCTACCTROLEPERS ARP, ACCT A
      WHERE A.curracctstatcd in ('ACT','IACT','DORM')
            AND (UPPER(ARP.ACCTROLECD) = 'OWN' AND ARP.INACTIVEDATE IS NULL)
            AND A.STMTACCTNBR = ARP.ACCTNBR
            AND AL.ACCTNBR = A.ACCTNBR
      ORDER BY 1,2
    )ATRP  --LISTS ALL NON-TAX OWNERS OF THE LOAN ACCOUNTS    

WHERE    
    A.curracctstatcd in ('ACT','IACT','DORM') 
AND AL.ACCTNBR = STRP.ACCTNBR
AND AL.ACCTNBR = ATRP.ACCTNBR
AND AL.ACCTNBR = A.ACCTNBR

Open in new window

I should also mention that the tool I am forced to use to report against the Oracle database will only allow me to use Select statements.

Thanks for your help!
PCCUtechAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

karunamoorthyCommented:
You can try
First treat ,ATRP.PERSNBR AS "Acct Non-Tax Own" and STRP.PERSNBR AS "Stmt Non-Tax Own" as one column instead of two columns
then  use concatenate  ACCTNBR+PERSNBR AS "key to check"
0
awking00Information Technology SpecialistCommented:
If you want to find where they are equivalent -
where atrp.greatest(acctnbr,persnbr) = strp.greatest(acctnbr,persnbr)
    and atrp.least(acctnbr,persnbr) = strp.least(acctnbr,persnbr)
If not equivalent -
where atrp.greatest(acctnbr,persnbr) != strp.greatest(acctnbr,persnbr)
       or atrp.least(acctnbr,persnbr) != strp.least(acctnbr,persnbr)
0
flow01Commented:
For what I see  the ATRP list and the STRP list are equal  :
if there are more owners you will get the square number of rows per account
for your sample account you got originally 4 rows
1111111   236236   236236
1111111   236236   789789
1111111   789789   236236
1111111   789789   789789

you lost already the equals and wil eventualy lose all records because ATRP and STRP are alike

i also think you are doing some extra work:
try


SELECT
    AL.ACCTNBR
    ,ATRP.PERSNBR AS "Acct Non-Tax Own"
    ,STRP.PERSNBR AS "Stmt Non-Tax Own"
FROM
    ACCTLOAN AL
    ,ACCT A
    , ACCTACCTROLEPERS STRP
    , ACCTACCTROLEPERS ATRP
WHERE    
    A.curracctstatcd in ('ACT','IACT','DORM')
AND AL.ACCTNBR = A.ACCTNBR
AND AL.ACCTNBR = STRP.ACCTNBR
AND (UPPER(STRP.ACCTROLECD) = 'OWN' AND STRP.INACTIVEDATE IS NULL)
AND AL.ACCTNBR = ATRP.ACCTNBR
AND UPPER(ATRP.ACCTROLECD) <> 'OWN' AND ATRP.INACTIVEDATE IS NULL)  -- adjust condition for getting list2 = ATRP  
AND ATRP.PERSNBR <> STRP.PERSNBR
ORDER BY 1,2,3
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PCCUtechAuthor Commented:
had to tweak this a tiny bit but this was it.  I can't believe I didn't catch the two being the same.

Holy Smokes I don't know what I was on there.

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.