Home » SQL & PL/SQL » SQL & PL/SQL » Help in NOT EXISTS (Oracle 8i, 8.1.7, Windows Server 2003)
Help in NOT EXISTS [message #316137] Wed, 23 April 2008 22:25 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Dear All,

I have a sqlplus like this

select DISTINCT
a.CUST_NAME,a.L_No,a.R_DATE
from m_r_hdr a
where a.STS != 'X'
and exists (select null from m_r_jobs b where b.r_id=a.id
and B.J_CODE IN ('T0106') AND B.STS != 'X')
AND NOT EXISTS (SELECT NULL FROM MSS_RO_JOBS F WHERE F.ROHD_ID=A.ID
AND F.JOB_CODE IN ('T0107'))

Actually I want to list the customer details who have the j_code T0106 and don't have j_code T0107 but I am getting the details of customers who have T0107 off course the customers who have T0107 will also have T0106 but if the customers have T0107 I don't want there details to appear.

Can anyone help me in this regard?

Re: Help in NOT EXISTS [message #316140 is a reply to message #316137] Wed, 23 April 2008 22:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Can anyone help me in this regard?
have you tried using "MINUS"
Re: Help in NOT EXISTS [message #316143 is a reply to message #316140] Wed, 23 April 2008 22:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Your query looks OK. There may be some error that I cannot see though.

Create a test case with CREATE TABLE commands and INSERT commands to populate the data. Post it here and someone will rerun it locally and find the problem.

Ross Leishman
Re: Help in NOT EXISTS [message #316154 is a reply to message #316140] Thu, 24 April 2008 00:23 Go to previous message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi,

How to use minus can you help me?
Previous Topic: Index a MINUS set
Next Topic: Average Function for Date Columns
Goto Forum:
  


Current Time: Sun Dec 11 08:28:57 CST 2016

Total time taken to generate the page: 0.04512 seconds