Home » SQL & PL/SQL » SQL & PL/SQL » NOT EXISTS help
NOT EXISTS help [message #278237] Fri, 02 November 2007 08:21 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Hello all,

I'm havnig a problem with the below query. I'm trying to pull all names of people that have not entered data for at least one label per group.

They only have to enter data for one label in each group of four labels. What am I doing wrong? Names are appearing even though data is entered for a label for each of the two groups.

For example, I would want a name to show up if data is entered for label 46 in group1 but nothing is entered for a label in group2.


Any help would be greatly appreciated,
Stan

SELECT DISTINCT
   p1.last_name,
   p1.id,
   p1.dept
FROM
   p1
WHERE
   p1.DATE IS NULL AND
   p1.id IN ('trul','cse') AND
   p1.facility = 'UF' AND
   NOT EXISTS
      (SELECT 
          NULL
       FROM
          p2
      WHERE
         (p2.label = 46 OR p2.label = 47 OR p2.label = 856 OR p2.label = 857) AND -- group1
         (p2.label = 50 OR p2.label = 52 OR p2.label = 860 OR p2.label = 861) AND -- group2
         perform_date > DDT.FROMDATE(TRUNC(SYSDATE)) AND
         p1.id_seq = p2.id_seq)
ORDER BY
   p1.dept
Re: NOT EXISTS help [message #278242 is a reply to message #278237] Fri, 02 November 2007 08:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you run the Not Exists query by itself, I think you'll find it returns no data, because I don't think you've got any rows in table P2 that match the condition
(p2.label = 46 OR p2.label = 47 OR p2.label = 856 OR p2.label = 857) AND -- group1
(p2.label = 50 OR p2.label = 52 OR p2.label = 860 OR p2.label = 861)


I think you need to use two Not Exists queries, one for each group.
Re: NOT EXISTS help [message #278244 is a reply to message #278242] Fri, 02 November 2007 09:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In addition to JRowbottoms findings
Quote:

perform_date > DDT.FROMDATE(TRUNC(SYSDATE))

What does ddt.fromdate do? Not a conversion to string, I hope?
Re: NOT EXISTS help [message #278258 is a reply to message #278242] Fri, 02 November 2007 10:26 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
JRowbottom,

You are correct.....what am I missing in the logic that is causing no rows to be returned. If I remove one of the groups and run the query I get data.?.?

Thanks,
Stan
Re: NOT EXISTS help [message #278259 is a reply to message #278258] Fri, 02 November 2007 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

If I remove one of the groups and run the query I get data.?.?

Nobody knows here what are your data and what DDT.FROMDATE does.
So nobody can answer your question.

Regards
Michel
Re: NOT EXISTS help [message #278261 is a reply to message #278258] Fri, 02 November 2007 10:34 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

If I remove one of the groups and run the query I get data.?.?
You can test that yourself. Can't you?

DDT.FROMDATE??? Anything secret in that, which is stopping you to post here????

By
Vamsi
Re: NOT EXISTS help [message #278264 is a reply to message #278261] Fri, 02 November 2007 10:54 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
The
DDT.FROMDATE
function converts a date string into a proprietary ten digit number that I can then to calculations and date manipulations on.
Re: NOT EXISTS help [message #278269 is a reply to message #278264] Fri, 02 November 2007 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

The DDT.FROMDATE function converts a date string into a proprietary ten digit number that I can then to calculations and date manipulations on.

This is a silly thing.
Oracle has wonderful and efficient functions to manipulate dates. Much more efficient that you can ever program.
In addition, optimizer knows what is a date, it does not know that a custom numeric represents a date and so can generate suboptimal plan.

Regards
Michel
Re: NOT EXISTS help [message #278273 is a reply to message #278269] Fri, 02 November 2007 11:26 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Michel,

Who said I wrote it! Don't jump to conclusions! I have to use what's supplied to me OK!

If you have a problem with it, take it up with McKesson...they wrote it! I guess you know better then their DBA's
Re: NOT EXISTS help [message #278275 is a reply to message #278273] Fri, 02 November 2007 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Who said I wrote it! Don't jump to conclusions! I have to use what's supplied to me OK!

In this post, "you" is not a personal one but a generic one, it means "someone of your company or supplier". Don't be so sensitive.

Quote:

If you have a problem with it, take it up with McKesson...they wrote it! I guess you know better then their DBA's

If they wrote date functions, surely. And if "you" let DBAs write your code, "you" are a fool. Smile (it's a joke don't take it bad)

Regards
Michel


Re: NOT EXISTS help [message #278276 is a reply to message #278275] Fri, 02 November 2007 11:50 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I guess I've just experienced rudeness time and time again on this forum. I have no problem with it but believe it to be misdirected. DDT.FROMDATE has nothing to do with my dilemma. I personally could care less whether it's a joke or not. I have to use the functions and procedures supplied by our vendor.
Re: NOT EXISTS help [message #278401 is a reply to message #278276] Sat, 03 November 2007 15:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The reason I brought up the function is that you compare a column, named perform_date, which implies that its datatype is DATE, to the outcome of this function.
I wanted to warn you if the outcome of the function would be a string, that you should not compare dates to strings.
Re: NOT EXISTS help [message #278437 is a reply to message #278264] Sun, 04 November 2007 03:32 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
staann56 wrote on Fri, 02 November 2007 15:54

The DDT.FROMDATE function converts a date string into a proprietary ten digit number that I can then to calculations and date manipulations on.


But you aren't passing it a string, you're passing it a date.

Anyway, I'm guessing perform_date must also be one of these proprietary ten digit numbers dreamt up by some genius at McKesson, otherwise you wouldn't have to convert it and things would be simpler.

The main problem though, as JRowbottom pointed out earlier, is that you require each p2.label to be in both (46,47,856,857) and (50,52,860,861) at the same time, which obviously it cannot be.
Re: NOT EXISTS help [message #278697 is a reply to message #278437] Mon, 05 November 2007 07:20 Go to previous message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thanks JRowbottom & William Robertson

I used two NOT EXISTS queries and problem solved.

Much appreciated,
Stan

[Updated on: Mon, 05 November 2007 07:24]

Report message to a moderator

Previous Topic: query.....
Next Topic: Standization in Oracle
Goto Forum:
  


Current Time: Wed Dec 07 22:12:59 CST 2016

Total time taken to generate the page: 0.11191 seconds