Solved

tricky issue with query

Posted on 2012-04-13
4
207 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 7

Expert Comment

by:karunamoorthy
ID: 37843817
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 32

Expert Comment

by:awking00
ID: 37844299
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
ID: 37844909
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
ID: 37903921
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

739 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