Link to home
Start Free TrialLog in
Avatar of PCCUtech
PCCUtechFlag for Canada

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
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

Open in new window


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

Open in new window


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	
)

Open in new window

Avatar of Pratima
Pratima
Flag of India image

what is result of this

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
)
ASKER CERTIFIED SOLUTION
Avatar of Pratima
Pratima
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PCCUtech

ASKER

This was perfect.  Worked like a charm and as expected I was over thinking the problem.

Minus... heh.

Thanks so much.