PCCUtech
asked on
Query with Multiple criteria
Hello everyone.
I am using an interface to an Oracle 9i database that doesn't allow me to create temporary tables and so need to get everything into a single query.
Right now I have 2 account number lists that are working fine.
The first list is account numbers with a specific type of loan security
The second query lists accounts that have a specific type of document request
These both seem to pull the correct account information.
What I need now is a query that gives me account number of loan that have that collateral type (in query 1) but DO NOT have the corresponding document request (not in Query 2).
I tried this but it yielded no results when I know there are several dozen that should be listed. They do show up in query 1 and are missing in query 2 when I compare the output lists so I know there should be results.
This is what I tried. Obviously I have an error somewhere but I don't see it. Thus it's likely obvious ;-P
I am using an interface to an Oracle 9i database that doesn't allow me to create temporary tables and so need to get everything into a single query.
Right now I have 2 account number lists that are working fine.
The first list is account numbers with a specific type of loan security
SELECT DISTINCT ACCTNBR
FROM ACCTPROP
WHERE PROPNBR in
(
Select PROPNBR
FROM PROP
Where PROPTYPCD not in ('1','2','3','4','10','25')
)
and INACTIVEDATE IS NULL
The second query lists accounts that have a specific type of document request
SELECT DISTINCT REFACCTNBR
FROM DOCREQ
WHERE UPPER(DOCTYPCD) = 'PPSA'
and INACTIVEDATE IS NULL
These both seem to pull the correct account information.
What I need now is a query that gives me account number of loan that have that collateral type (in query 1) but DO NOT have the corresponding document request (not in Query 2).
I tried this but it yielded no results when I know there are several dozen that should be listed. They do show up in query 1 and are missing in query 2 when I compare the output lists so I know there should be results.
This is what I tried. Obviously I have an error somewhere but I don't see it. Thus it's likely obvious ;-P
Select DISTINCT WH_ACCTCOMMON.ACCTNBR
FROM WH_ACCTCOMMON
WHERE
WH_ACCTCOMMON.ACCTNBR NOT IN
(
SELECT DISTINCT REFACCTNBR
FROM DOCREQ
WHERE UPPER(DOCTYPCD) = 'PPSA'
and INACTIVEDATE IS NULL
)
and WH_ACCTCOMMON.ACCTNBR IN
(
SELECT DISTINCT ACCTPROP.ACCTNBR
FROM ACCTPROP
WHERE PROPNBR in
(
Select PROP.PROPNBR
FROM PROP
Where PROPTYPCD not in ('1','2','3','4','10','25')
)
and INACTIVEDATE IS NULL
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This was perfect. Worked like a charm and as expected I was over thinking the problem.
Minus... heh.
Thanks so much.
Minus... heh.
Thanks so much.
SELECT DISTINCT ACCTNBR
FROM ACCTPROP
WHERE PROPNBR in
(
Select PROPNBR
FROM PROP
Where PROPTYPCD not in ('1','2','3','4','10','25'
)
and INACTIVEDATE IS NULL
and ACCTNBR not in
(
SELECT DISTINCT REFACCTNBR
FROM DOCREQ
WHERE UPPER(DOCTYPCD) = 'PPSA'
and INACTIVEDATE IS NULL
)