Parsing error in view - 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)

Parsing error in view

Author

Topic

alex_808
Starting Member

16 Posts

Posted - 2009-05-28 : 12:12:47

SELECT dbo.tbl_vvp_user.id_vvp_user, dbo.tbl_vvp_user.firstname, dbo.tbl_vvp_user.lastname, dbo.tbl_vvp_user.email, dbo.tbl_vvp_user.cookie_key,
MAX(dbo.tbl_vvp_weblog.date) AS last_date, MIN(dbo.tbl_vvp_weblog.date) AS first_date, MAX(dbo.tbl_vvp_weblog.IP) AS IP,
COUNT(DISTINCT CASE WHEN in_flashplayer = 1 THEN id_vvp_weblog ELSE 0 END) AS flashlog,
COUNT(DISTINCT CASE WHEN in_flashplayer = 0 THEN id_vvp_weblog ELSE 0 END) AS weblog, COUNT(DISTINCT dbo.tbl_vvp_weblog.IP)
AS IP_count, COUNT(DISTINCT dbo.tbl_vvp_question.id_vvp_question) AS question_count, COUNT(DISTINCT dbo.tbl_vvp_mail_sent.id_vvp_mail_sent)
AS mail_count, mail_sent.vvp AS mail_sent
FROM dbo.tbl_vvp_user INNER JOIN
dbo.tbl_vvp_mail_sent ON dbo.tbl_vvp_user.cookie_key = dbo.tbl_vvp_mail_sent.cookie_key LEFT OUTER JOIN
dbo.tbl_vvp_question ON dbo.tbl_vvp_user.id_vvp_user = dbo.tbl_vvp_question.id_vvp_user LEFT OUTER JOIN
dbo.tbl_vvp_weblog ON dbo.tbl_vvp_user.id_vvp_user = dbo.tbl_vvp_weblog.id_vvp_user CROSS APPLY
(SELECT id_vvp_conference + '; ' AS [text()]
FROM tbl_vvp_mail_sent ms INNER JOIN
tbl_vvp_mail m ON ms.id_vvp_mail = m.id_vvp_mail
WHERE ms.cookie_key = tbl_vvp_user.cookie_key FOR XML PATH('')) mail_sent(vvp)
GROUP BY dbo.tbl_vvp_user.id_vvp_user, dbo.tbl_vvp_user.firstname, dbo.tbl_vvp_user.lastname, dbo.tbl_vvp_user.email, dbo.tbl_vvp_user.cookie_key,
mail_sent.vvp

Error in WHERE clause near '('.
Error in GROUP BY clause.
Unable to parse query text.

:( i don't see the problem feel free to help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 12:18:41

[code]SELECT dbo.tbl_vvp_user.id_vvp_user,
dbo.tbl_vvp_user.firstname,
dbo.tbl_vvp_user.lastname,
dbo.tbl_vvp_user.email,
dbo.tbl_vvp_user.cookie_key,
MAX(dbo.tbl_vvp_weblog.date) AS last_date, MIN(dbo.tbl_vvp_weblog.date) AS first_date, MAX(dbo.tbl_vvp_weblog.IP) AS IP,
COUNT(DISTINCT CASE WHEN in_flashplayer = 1 THEN id_vvp_weblog ELSE 0 END) AS flashlog,
COUNT(DISTINCT CASE WHEN in_flashplayer = 0 THEN id_vvp_weblog ELSE 0 END) AS weblog,
COUNT(DISTINCT dbo.tbl_vvp_weblog.IP) AS IP_count,
COUNT(DISTINCT dbo.tbl_vvp_question.id_vvp_question) AS question_count,
COUNT(DISTINCT dbo.tbl_vvp_mail_sent.id_vvp_mail_sent) AS mail_count, mail_sent.vvp AS mail_sent
FROM dbo.tbl_vvp_user
INNER JOIN dbo.tbl_vvp_mail_sent
ON dbo.tbl_vvp_user.cookie_key = dbo.tbl_vvp_mail_sent.cookie_key LEFT OUTER JOIN dbo.tbl_vvp_question
ON dbo.tbl_vvp_user.id_vvp_user = dbo.tbl_vvp_question.id_vvp_user LEFT OUTER JOIN dbo.tbl_vvp_weblog
ON dbo.tbl_vvp_user.id_vvp_user = dbo.tbl_vvp_weblog.id_vvp_user CROSS APPLY
(SELECT id_vvp_conference + '; '
FROM tbl_vvp_mail_sent ms
INNER JOIN tbl_vvp_mail m
ON ms.id_vvp_mail = m.id_vvp_mail
WHERE ms.cookie_key = tbl_vvp_user.cookie_key
FOR XML PATH('')) mail_sent(vvp)
GROUP BY dbo.tbl_vvp_user.id_vvp_user, dbo.tbl_vvp_user.firstname, dbo.tbl_vvp_user.lastname, dbo.tbl_vvp_user.email, dbo.tbl_vvp_user.cookie_key,mail_sent.vvp
[/code]

alex_808
Starting Member

16 Posts

Posted - 2009-05-28 : 13:35:18

Thx for your quick reply visakh16! Sadly i still get the error :( I will keep looking!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 13:48:18

are you using sql 2005? whats the compatibility level of db? run belwo and post the result


SELECT @@VERSION
EXEC sp_dbcmptlevel 'Yourdatabase'

alex_808
Starting Member

16 Posts

Posted - 2009-05-28 : 13:55:19

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 14:01:20

whats the result of this?

EXEC sp_dbcmptlevel 'Yourdatabase'

alex_808
Starting Member

16 Posts

Posted - 2009-05-28 : 14:05:22

The current compatibility level is 90.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 14:13:00

And you are using the VIEW editor?
Not the query window?

E 12°55'05.63"
N 56°04'39.26"

alex_808
Starting Member

16 Posts

Posted - 2009-05-28 : 14:15:17

VIEW editor.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 14:17:58

The query still works fine and will be saved accordingly and proper.
It's just the graphical representation that cannot be made by the VIEW editor.

E 12°55'05.63"
N 56°04'39.26"

alex_808
Starting Member

16 Posts

Posted - 2009-05-28 : 14:23:43

Okay that awnsers my question a million thanks to You and visakh16 for helping much appreciated!

(Since am here for general knowledge what in the query would make it not graphically representable?)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 14:28:36

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources