Solved

tricky issue with query

Posted on 2012-04-13
4
202 Views
Last Modified: 2012-04-27
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!
0
Comment
Question by:PCCUtech
4 Comments
 
LVL 7

Expert Comment

by:karunamoorthy
Comment Utility
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
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:PCCUtech
Comment Utility
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now