whats wrong with this? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

whats wrong with this?

Author

Topic

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-12 : 08:52:57

[code]
With tmp
as (
Select db = 'ABC001', fieldvalue from ABC001..fundcustomfieldvalues where fieldid = '1'
Union all
Select db = 'ABC002', fieldvalue from ABC002..fundcustomfieldvalues where fieldid = '1'
Union all
Select db = 'ABC003', fieldvalue from ABC003..fundcustomfieldvalues where fieldid = '1'
Union all
)
Select fieldvalue, dbname
From tmp
Where fieldvalue in
(
Select fieldvalue from tmp
Group by fieldvalue
Having count(*) > 1
)
Order by fieldvalue, dbname
[/code]

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'With'.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near ')'.

its the whole with temp ( ... ) part

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-12 : 08:55:00

quote:
Originally posted by QuietRiot

With tmp
as (
Select db = 'ABC001', fieldvalue from ABC001..fundcustomfieldvalues where fieldid = '1'
Union all
Select db = 'ABC002', fieldvalue from ABC002..fundcustomfieldvalues where fieldid = '1'
Union all
Select db = 'ABC003', fieldvalue from ABC003..fundcustomfieldvalues where fieldid = '1'
~~Union all~~
)
Select fieldvalue, dbname
From tmp
Where fieldvalue in
(
Select fieldvalue from tmp
Group by fieldvalue
Having count(*) > 1
)
Order by fieldvalue, dbname

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'With'.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near ')'.

its the whole with temp ( ... ) part

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-12 : 08:59:49

and add a ; before with just in case.
;With ...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-12 : 08:59:53

ok,

i solved one but I still get this one error
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'With'.

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-12 : 09:01:21

Line 1: Incorrect syntax near ';'.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-12 : 09:03:34

well post your "fixed" query

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-12 : 09:05:30

well you are doing too many wrong things. There is no column with name dbname. Secondly are you quite sure, you are running this query in SQL 2005?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-12 : 09:10:34

no its 2000.. just checked

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-12 : 09:14:49

also these are my 2 original if someone can help tweak the above

use ABC003 select co# =(003), fieldvalue from fundcustomfieldvalues where fieldid = '1'
use ABC004 select co# =(004), fieldvalue from fundcustomfieldvalues where fieldid = '1'

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-12 : 09:20:35

you can't use WITH in sql server 2000

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-12 : 09:36:15

Ok, thanks guys I just created a new thread in the 2000 section

sorry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources