Link to home
Start Free TrialLog in
Avatar of PCCUtech
PCCUtechFlag for Canada

asked on

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!
Avatar of karunamoorthy
karunamoorthy
Flag of India image

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"
Avatar of awking00
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)
ASKER CERTIFIED SOLUTION
Avatar of flow01
flow01
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PCCUtech

ASKER

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.