By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,472 Members | 1,204 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,472 IT Pros & Developers. It's quick & easy.

Incorrect syntax near the keyword 'EXISTS'.

syedshaffee
P: 91
Hey people,
i am getting this error 'Incorrect syntax near the keyword 'EXISTS'.'
while executing this query
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Contract_detail.Contract_no
  3. FROM   Contract_detail INNER JOIN ContractRewriteHistory ON Contract_detail.Contract_no = ContractRewriteHistory.ContractNo
  4. WHERE Contract_detail.Contract_no  NOT EXISTS
  5. (
  6. SELECT ContractNo FROM ContractRewriteHistory WHERE 
  7. ContractNo<>'' AND
  8. ContractRewriteHistory.ContractNo = Contract_detail.Contract_no
  9. );
  10.  
  11.  
Please tell me what is wrong in this
Oct 16 '12 #1

✓ answered by Rabbit

The EXISTS and NOT EXISTS isn't a comparison against a column. You use IN or NOT IN to compare against a column.

However, you should use neither. The current query you are attempting to use will return 0 rows. If your goal is to find contracts in detail that are not in rewrite history, that is not the correct way to go about it. You should use an outer join query and filter for the nulls.

Share this Question
Share on Google+
3 Replies


debasisdas
Expert 5K+
P: 8,127
try this

Expand|Select|Wrap|Line Numbers
  1. SELECT Contract_detail.Contract_no
  2. FROM   Contract_detail INNER JOIN ContractRewriteHistory ON Contract_detail.Contract_no = ContractRewriteHistory.ContractNo
  3. WHERE NOT EXISTS
  4. (
  5. SELECT ContractNo FROM ContractRewriteHistory WHERE 
  6. ContractNo<>'' AND
  7. ContractRewriteHistory.ContractNo = Contract_detail.Contract_no
  8. );
Oct 16 '12 #2

Rabbit
Expert Mod 10K+
P: 12,430
The EXISTS and NOT EXISTS isn't a comparison against a column. You use IN or NOT IN to compare against a column.

However, you should use neither. The current query you are attempting to use will return 0 rows. If your goal is to find contracts in detail that are not in rewrite history, that is not the correct way to go about it. You should use an outer join query and filter for the nulls.
Oct 16 '12 #3

syedshaffee
P: 91
Thanx rabbit you rock
Oct 17 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.