troubleshooting Question

Query with Multiple criteria

Avatar of PCCUtech
PCCUtechFlag for Canada asked on
Oracle DatabaseSQL
3 Comments1 Solution239 ViewsLast Modified:
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
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	
)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros