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
 Can anyone help

Author  Topic 

NeilG
Aged Yak Warrior

530 Posts
Posted - 2008-11-14 : 09:52:15
I need to see if a fresh pair of eyes can help one this one, their is an error appearing and I just cannot clear

BEGIN
SET ROWCOUNT 0

SELECT TOP 200
CI.CONTENTITEMID AS CONTENTID ,
CI.CONTENTCONTAINERID AS CONTAINERID ,
CI.JOURNALTITLE AS JOURNALTITLE ,
CI.JOURNALVOLUME AS VOLUMENUMBER ,
CI.JOURNALISSUE AS ISSUENUMBER ,
CI.SPECIALISSUESHORTTITLE AS SPECIALISSUESHORTTITLE,
CI.JOURNALYEAR AS ARTICLEYEAR ,
CI.CONTENTITEMNAME AS ARTICLETITLE ,
CI.ARTICLETYPE AS ARTICLETYPE ,
CIA.ABSTRACTTEXT AS ABSTRACTTEXT ,
CI.CONTENTITEMTYPENAME AS CONTENTITEMTYPE,
CI.AUTHOR AS AUTHORNAMES ,
CI.APPROVEDFLAG AS APPROVEDFLAG ,
CI.PUBLISHEDFLAG AS PUBLISHEDFLAG ,
CIF1.FILEURN HTMLLINK ,
CIF2.FILEURN PDFLINK ,
CIF2.FILESIZE AS FILESIZE ,
SRCHQRY.RANK ,
CITYPE.DEFAULTAUTHENTICATIONRULECODE AS AUTHRULE ,
ISNULL(CTREE.NODE, -1) AS SUBSCRIBED ,
CI.copyrightIndicator as COPYRIGHTINDICATOR ,
J.subscriptionAcronym AS SUBSCRIPTIONACRONYM
FROM
CONTENTITEM CI
INNER JOIN
CONTENTITEMTYPE CITYPE
ON
CI.CONTENTITEMTYPENAME = CITYPE.CONTENTITEMTYPENAME
LEFT JOIN
(
SELECT
CICC.CONTENTCONTAINERID NODE ,
CICC.CONTENTITEMID
FROM
CONTENTITEM_CONTRACTCONTAINERS_ARTICLE CICC WITH (NOLOCK),
USERSUBSCRIPTIONS USR WITH (NOLOCK)
WHERE
CICC.CONTENTCONTAINERID = USR.CONTENTCONTAINERID
AND USR.SESSIONID = 'null'
AND CICC.ORDERKEY BETWEEN USR.STARTKEY
AND USR.ENDKEY
AND CICC.CONTENTITEMTYPENAME IN ('Check Full Text Article','Check Abstract Only Article','Management Article')
)
UNION ALL
SELECT
NODE ,
CONTENTITEMID
FROM
PROMOTEDCONTENT WITH (NOLOCK)
UNION ALL
SELECT
1 AS NODE,
CONTENTITEMID
FROM
USERSUBSCRIPTIONS WITH (NOLOCK)
WHERE
SESSIONID = 'null'
AND CONTENTITEMID > 0
UNION ALL
SELECT
NULL AS NODE ,
CONTENTITEMID
FROM
CONTENTITEM WITH (NOLOCK)
WHERE
PUBLISHEDFLAG = 'Y'
AND APPROVEDFLAG NOT IN ('I', 'R')
AND CONTENTITEMTYPENAME IN (
SELECT
CONTENTITEMTYPENAME
FROM
CONTENTITEMTYPE WITH (NOLOCK)
WHERE
DEFAULTAUTHENTICATIONRULECODE > 1
AND CONTENTITEMTYPENAME IN ('Check Full Text Article','Check Abstract Only Article','Management Article')
)
AND CONTENTITEMTYPENAME IN ('Check Full Text Article','Check Abstract Only Article','Management Article')
) CTREE
ON
CTREE.CONTENTITEMID = CI.CONTENTITEMID
LEFT JOIN
CONTENTITEMABSTRACTS CIA ON CI.CONTENTITEMID = CIA.CONTENTITEMID
LEFT JOIN
CONTENTITEMFILE CIF1 ON CI.CONTENTITEMID = CIF1.CONTENTITEMID AND CIF1.FILETYPECODE=1
LEFT JOIN
CONTENTITEMFILE CIF2 ON CI.CONTENTITEMID = CIF2.CONTENTITEMID AND CIF2.FILETYPECODE=2
LEFT JOIN
JOURNAL J
ON
CI.JOURNAL = J.ID
INNER JOIN
(
SELECT
Q.CONTENTITEMID ,
Q.RANK AS RANK
FROM
(
(
SELECT
CIXML.CONTENTITEMID ,
KEYTABLE.RANK AS RANK
FROM
CONTENTXMLS CIXML
INNER JOIN
CONTAINSTABLE(CONTENTXMLS , FILEIMAGE, '"relationship" AND "marketing"', 300 )AS KEYTABLE
ON
KEYTABLE.[KEY] = CIXML.FILEID
)
) Q
Group By
Q.CONTENTITEMID
) QR
ON
QR.CONTENTITEMID = CI.CONTENTITEMID
WHERE
CI.PUBLISHEDFLAG = 'Y' AND CI.CONTENTLOADDATE > '01/06/2008'
) SRCHQRY
ON
SRCHQRY.CONTENTITEMID = CI.CONTENTITEMID
AND ( CITYPE.DEFAULTAUTHENTICATIONRULECODE != 1 OR CTREE.NODE IS NOT NULL) -- error here. Incorrect syntax near ')'.
ORDER BY
SRCHQRY.RANK DESC ,
CI.ORDERKEY DESC
END



**************************
Check Check and double check it's the safest way

NeilG
Aged Yak Warrior

530 Posts
Posted - 2008-11-14 : 10:03:33
it is showing error

Msg 156, Level 15, State 1, Line 48
Incorrect syntax near the keyword 'UNION'.
Msg 170, Level 15, State 1, Line 82
Line 82: Incorrect syntax near ')'.
Msg 170, Level 15, State 1, Line 112
Line 112: Incorrect syntax near ')'.

sorry guys
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-14 : 10:07:16
For the UNION error, you have forgot to alias the derived table "AS CI" just before that line.

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

NeilG
Aged Yak Warrior

530 Posts
Posted - 2008-11-14 : 10:12:21
Have i not forgot the on statement as well or can i do that
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-14 : 10:16:47
See the missing part in red!
AND CICC.CONTENTITEMTYPENAME IN ('Check Full Text Article','Check Abstract Only Article','Management Article')
) AS ci
UNION ALL 
SELECT


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

NeilG
Aged Yak Warrior

530 Posts
Posted - 2008-11-14 : 10:18:52
When I add that in then is still come up with

Msg 156, Level 15, State 1, Line 45
Incorrect syntax near the keyword 'UNION'
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-14 : 10:25:32
A paranthesis too much
SELECT TOP 200	CI.CONTENTITEMID AS CONTENTID , 
		CI.CONTENTCONTAINERID AS CONTAINERID , 
		CI.JOURNALTITLE AS JOURNALTITLE , 
		CI.JOURNALVOLUME AS VOLUMENUMBER , 
		CI.JOURNALISSUE AS ISSUENUMBER , 
		CI.SPECIALISSUESHORTTITLE AS SPECIALISSUESHORTTITLE, 
		CI.JOURNALYEAR AS ARTICLEYEAR , 
		CI.CONTENTITEMNAME AS ARTICLETITLE , 
		CI.ARTICLETYPE AS ARTICLETYPE , 
		CIA.ABSTRACTTEXT AS ABSTRACTTEXT , 
		CI.CONTENTITEMTYPENAME AS CONTENTITEMTYPE, 
		CI.AUTHOR AS AUTHORNAMES , 
		CI.APPROVEDFLAG AS APPROVEDFLAG ,
		CI.PUBLISHEDFLAG AS PUBLISHEDFLAG , 
		CIF1.FILEURN HTMLLINK , 
		CIF2.FILEURN PDFLINK , 
		CIF2.FILESIZE AS FILESIZE , 
		SRCHQRY.RANK , 
		CITYPE.DEFAULTAUTHENTICATIONRULECODE AS AUTHRULE , 
		ISNULL(CTREE.NODE, -1) AS SUBSCRIBED , 
		CI.copyrightIndicator as COPYRIGHTINDICATOR , 
		J.subscriptionAcronym AS SUBSCRIPTIONACRONYM
FROM		CONTENTITEM CI 
INNER JOIN	CONTENTITEMTYPE CITYPE ON CI.CONTENTITEMTYPENAME = CITYPE.CONTENTITEMTYPENAME 
LEFT JOIN	( 
			SELECT	CICC.CONTENTCONTAINERID NODE , 
				CICC.CONTENTITEMID 
			FROM	CONTENTITEM_CONTRACTCONTAINERS_ARTICLE CICC WITH (NOLOCK),
				USERSUBSCRIPTIONS USR WITH (NOLOCK) 
			WHERE	CICC.CONTENTCONTAINERID = USR.CONTENTCONTAINERID 
				AND USR.SESSIONID = 'null' 
				AND CICC.ORDERKEY BETWEEN USR.STARTKEY AND USR.ENDKEY 
				AND CICC.CONTENTITEMTYPENAME IN ('Check Full Text Article','Check Abstract Only Article','Management Article')

			UNION ALL 

			SELECT	NODE , 
				CONTENTITEMID 
			FROM	PROMOTEDCONTENT WITH (NOLOCK) 

			UNION ALL 

			SELECT	1 AS NODE, 
				CONTENTITEMID 
			FROM	USERSUBSCRIPTIONS WITH (NOLOCK) 
			WHERE	SESSIONID = 'null' 
				AND CONTENTITEMID > 0 

			UNION ALL 

			SELECT	NULL AS NODE , 
				CONTENTITEMID 
			FROM	CONTENTITEM WITH (NOLOCK) 
			WHERE	PUBLISHEDFLAG = 'Y' 
				AND APPROVEDFLAG NOT IN ('I', 'R') 
				AND CONTENTITEMTYPENAME IN ( SELECT 
						CONTENTITEMTYPENAME 
						FROM	CONTENTITEMTYPE WITH (NOLOCK) 
						WHERE	DEFAULTAUTHENTICATIONRULECODE > 1 
							AND CONTENTITEMTYPENAME IN ('Check Full Text Article','Check Abstract Only Article','Management Article') 
								) 
				AND CONTENTITEMTYPENAME IN ('Check Full Text Article','Check Abstract Only Article','Management Article') 
		) CTREE ON CTREE.CONTENTITEMID = CI.CONTENTITEMID 
LEFT JOIN	CONTENTITEMABSTRACTS CIA ON CI.CONTENTITEMID = CIA.CONTENTITEMID 
LEFT JOIN	CONTENTITEMFILE CIF1 ON CI.CONTENTITEMID = CIF1.CONTENTITEMID AND CIF1.FILETYPECODE=1
LEFT JOIN	CONTENTITEMFILE CIF2 ON CI.CONTENTITEMID = CIF2.CONTENTITEMID AND CIF2.FILETYPECODE=2
LEFT JOIN	JOURNAL J ON CI.JOURNAL = J.ID
INNER JOIN	(
			SELECT	Q.CONTENTITEMID , 
				Q.RANK AS RANK 
			FROM	( 
					SELECT		CIXML.CONTENTITEMID , 
							KEYTABLE.RANK AS RANK 
					FROM		CONTENTXMLS CIXML 
					INNER JOIN	CONTAINSTABLE(CONTENTXMLS , FILEIMAGE, '"relationship" AND "marketing"', 300 )AS KEYTABLE ON KEYTABLE.[KEY] = CIXML.FILEID 
				) Q 
			Group By Q.CONTENTITEMID 
		) QR ON QR.CONTENTITEMID = CI.CONTENTITEMID 
WHERE		CI.PUBLISHEDFLAG = 'Y' AND CI.CONTENTLOADDATE > '01/06/2008'
/*
) SRCHQRY 
ON SRCHQRY.CONTENTITEMID = CI.CONTENTITEMID
	AND (CITYPE.DEFAULTAUTHENTICATIONRULECODE != 1 OR CTREE.NODE IS NOT NULL) 
*/
ORDER BY	SRCHQRY.RANK DESC , 
		CI.ORDERKEY DESC


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

NeilG
Aged Yak Warrior

530 Posts
Posted - 2008-11-14 : 10:53:13
But how do i ad that section in do i need to add another paranthesis further u the code somewhere

Cheers for all the help by the way
Go to Top of Page

NeilG
Aged Yak Warrior

530 Posts
Posted - 2008-11-14 : 11:06:27
I do need that section in but nothing is working onthis any extra help will be grateful
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-14 : 11:08:47
You also have to fix the

Q.RANK AS RANK

part...


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site