PCCUtech
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
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
Thanks for your help!
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
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 haveAny 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
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!
If you want to find where they are equivalent -
where atrp.greatest(acctnbr,pers nbr) = strp.greatest(acctnbr,pers nbr)
and atrp.least(acctnbr,persnbr ) = strp.least(acctnbr,persnbr )
If not equivalent -
where atrp.greatest(acctnbr,pers nbr) != strp.greatest(acctnbr,pers nbr)
or atrp.least(acctnbr,persnbr ) != strp.least(acctnbr,persnbr )
where atrp.greatest(acctnbr,pers
and atrp.least(acctnbr,persnbr
If not equivalent -
where atrp.greatest(acctnbr,pers
or atrp.least(acctnbr,persnbr
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Holy Smokes I don't know what I was on there.
Thanks.
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"