NOT EXISTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NOT EXISTS

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-06-14 : 11:04:34

Hi,

Thank you in advance for your assistance. I am trying to identify distinct GLDOCs which are in the first query but not in the second?

I get the error message:
Incorrect syntax near the keyword 'where'.

select DISTINCT GLDOC from View_F_GLTrans_BU_V2 F
inner join F0101 A on F.GLAN8 = A.ABAN8
where F.GLAN8 = '02050294'
and TreeYear = 2011

where not exists
(
select DISTINCT GLDOC from view_f_gltrans_bu_v2 where GLSBL = '02050294'
AND TreeYear = 2011
)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-14 : 11:10:50

replacing "where not exists" with "AND GLDOC NOT IN" will get rid of the error message, but you will always get zero rows from that query. Can you describe what you are trying to get?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-14 : 11:36:06

Careful, careful

quote:
Originally posted by sunitabeck

replacing "where not exists" with "AND GLDOC NOT IN" will get rid of the error message, but you will always get zero rows from that query. Can you describe what you are trying to get?

select DISTINCT GLDOC from View_F_GLTrans_BU_V2 F
inner join F0101 A on F.GLAN8 = A.ABAN8
where F.GLAN8 = '02050294'
and TreeYear = 2011
and F.GLDOC not in
(
select DISTINCT GLDOC from view_f_gltrans_bu_v2 where GLSBL = '02050294'
AND TreeYear = 2011
)

Corey

I Has Returned!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-14 : 11:41:44

It's not very clear how your data relates, since we don't have access to your database. Here is a link that will help you craft your question so we can help you better:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-14 : 11:42:47

Thanks Corey! I looked at the first two characters and decided that they must be the same!!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-14 : 12:51:58

Happens to the best of us

Corey

I Has Returned!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-14 : 13:58:03

quote:
Originally posted by tariq2

Hi,

Thank you in advance for your assistance. I am trying to identify distinct GLDOCs which are in the first query but not in the second?

I get the error message:
Incorrect syntax near the keyword 'where'.

select DISTINCT GLDOC from View_F_GLTrans_BU_V2 F
inner join F0101 A on F.GLAN8 = A.ABAN8
where F.GLAN8 = '02050294'
and TreeYear = 2011

~~where~~and not exists
(
select DISTINCT GLDOC from view_f_gltrans_bu_v2 V where V.GLSBL = ~~'02050294'~~F.GLAN8
AND TreeYear = 2011
)

No, you're never too old to Yak'n'Roll if you're too young to die.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-14 : 17:25:43

Please stop putting “view” on view names; it violates ISO-11179 and just looks silly. The rest of the data elements were also in violation and impossible to understand. But the short answer is to use the EXCEPT operator.

(SELECT gldoc
FROM F_Gltrans_BU_V2 AS F,
F0101 AS A
WHERE F.glan8 = A.aban8
AND F.glan8 = '02050294'
AND tree_year = 2011)
EXCEPT
(SELECT gldoc
FROM F_Gltrans_BU_V2
WHERE glsbl = '02050294'
AND tree_year = 2011)

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources