Solved

tricky issue with query

Posted on 2012-04-13
4
205 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
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to free up undo space? 3 50
error in my cursor 5 41
Oracle create type table from existing table%rowtype ? 6 33
date show only hh:mm 2 25
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

808 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