[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

WHERE NOT EXISTS missing right parenthesis problem.

Posted on 2006-03-23
9
Medium Priority
?
529 Views
Last Modified: 2011-10-03
I am having trouble getting this query to work.  I can run both queries seperately just fine, but when I try to run them together in a WHERE NOT EXISTS clause, I keep getting the dreaded missing right parenthesis error.

Here is the query:

SELECT *
FROM Nine99 n, bookingoptselect bos
WHERE NOT EXISTS
(
  SELECT *
  FROM Nine99, BookingOptSelect bos
  WHERE nine99txt != n.nine99Txt
  AND bos.id = bookingOptSelectId
  AND bookingid = 19633
  AND bookingRevNum = 0
  ORDER BY bosUdcName
)
AND bos.id = bookingOptSelectId
AND bookingid = 19633
AND bookingRevNum = 1
ORDER BY bosUdcName

Can you not do order by inside the WHERE NOT EXISTS?
0
Comment
Question by:gdlp2004
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16270255
where would be the interest in having an order by in a WHERE NOT EXISTS () ?

remove the order by clause from there.
0
 

Author Comment

by:gdlp2004
ID: 16271008
angelIII,

the reason I have that order by clause is to check for each particular number not necessarily to see if that description was not in the entire table.  

For example.

BEFORE:             AFTER:
1. apple       --    1. apple
2. grapes     --    2. grapes
3. orange     --    3. grapefruit
4. cherry      --    4. cherry
5. tomato     --    5. bananna

I want the results of the query to be.  
1. grapefruit
5. bananna

I was worried that it might compare

apple to grapes and return grapes.  On down.
0
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 16271211
try "NOT IN" clause with joining 2 fields
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Expert Comment

by:calyciflorae
ID: 16272502
it must work as this...

instead of WHERE NOT EXISTS, give NOT IN
followed by WHERE AND GIVE YOUR SPECIFICATIONS..

I have tried similar problems.
goodluck
0
 

Expert Comment

by:calyciflorae
ID: 16272527
I mean give NOT IN in followed byt (SELECT *.......)
WHERE conditions.

hope it helps
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16272861
Not Exists will be TRUE is the Select statement that you are testing returns 0 rows, and False it it returns ANY number of rows.  It has nothing at all to do with what fields the select statement returns - just that A record, ANY record is or  is not  returned.

Not IN will allow you to test a specific value  ( the syntax is Where SourceTable.Field NOT IN (Select TestTable.Field from TestTable where TestTable.Condition)

Notice here that the inner select returns only 1 Field (but possibly many rows) - Here it is VERY important what field the inner SQL returns, since it MUST be the same KIND of thing as the field you are testing with (SourceTable.Field and TestTable.Field MUST be the same kind of values).

AW
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 16272872
As angelIII has pointed out you cannot have an ORDER BY clause in a EXISTS; it simply does not make any sense.  If you do you will get the following error message:
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Do it this way:
SELECT *
FROM Nine99 n, bookingoptselect bos
WHERE NOT EXISTS
(
  SELECT 1
  FROM Nine99, BookingOptSelect bos
  WHERE nine99txt != n.nine99Txt
  AND bos.id = bookingOptSelectId
  AND bookingid = 19633
  AND bookingRevNum = 0)
AND bos.id = bookingOptSelectId
AND bookingid = 19633
AND bookingRevNum = 1
ORDER BY bosUdcName
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question