Avatar of PCCUtech
PCCUtech
Flag 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

Oracle DatabaseSQL

Avatar of undefined
Last Comment
PCCUtech

8/22/2022 - Mon
Pratima

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
Pratima

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PCCUtech

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

Minus... heh.

Thanks so much.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck