468,525 Members | 2,234 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,525 developers. It's quick & easy.

Incorrect syntax near the keyword 'EXISTS'.

syedshaffee
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.

3 3702
debasisdas
8,127 Expert 4TB
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
12,513 Expert Mod 8TB
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
Thanx rabbit you rock
Oct 17 '12 #4

Post your reply

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

Similar topics

1 post views Thread by Jeff Magouirk | last post: by
1 post views Thread by Sandesh | last post: by
2 posts views Thread by ielmrani via SQLMonster.com | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.