Derived table used in nested query gives error

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)

Derived table used in nested query gives error

Author

Topic

sweta_singh
Starting Member

10 Posts

Posted - 2009-06-03 : 08:56:29

Hi,
I have written a stored procedure to implement paging. It selects the top (records per page) from(a derived table) where (some column name) NOT IN (select top ((current page-1)*records per page) from (the same derived table as above). Current page, records per page are parameters passed to the SP. Each of the queries run successfully individually but when I nest them as above I get the error:

Msg 170, Level 15, State 1, Line 52
Line 52: Incorrect syntax near ')'

CAn anyone please help me on this. Let me know if more info is needed

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 09:05:19

crystal ball:
There is no alias name given for derived table?

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-03 : 09:06:26

The full query is needed to provide any help...but be sure to give any derived tables names like this:

SELECT * FROM (SELECT Name FROM mytable) AS a

- Lumbago

sweta_singh
Starting Member

10 Posts

Posted - 2009-06-05 : 03:26:51

Thanks, but I am still stuck up. I am posting the query for your reference, but it is pretty huge with too many tables. M not giving the tables and their structures. If just the query can give someone the idea where it goes wrong with the syntax please let me know.

Select @QueryString = 'SELECT TOP '+CONVERT(nvarchar,@RecordsPerPage)+'
uol.ProposalID,
IsNull(p.ProposalName, ''Unassigned'') ProposalName,
IsNull(p.ProposalDescription, ''Unassigned'') ProposalDescription,
p.CreatedDate,
p.OpportunityID,
p.SASReviewer,
uol.QuoteID,
q.QuoteName,
q.QuoteDescription,
q.DateAmended,
CASE
WHEN q.DEIRequired = 1
THEN IsNull(d.UActStatus, ''Not Submitted'')
ELSE ''Not Required''
END DEIStatus,
COUNT(IsNull(soq.ObjectID, sop.ObjectID)) SharedCount,
COUNT(f.QuoteID) UCNCount,

pas.Status ProposalStatus,
pas.SASStatus SASProposalStatus,
Case
When pas.Status = ''Processed'' Then
MAX(ob.DateEnteredOM)
Else
pas.Date
End ApprovalStatusDate,
CASE q.UserLogin
WHEN '''+Convert(varchar,@UserLogin)+'''
THEN 0
ELSE 1
END IsQuoteOwned,
CASE p.SalesPerson
WHEN '''+Convert(varchar,@UserLogin)+'''
THEN 0
ELSE 1
END IsProposalOwned,
uol.QuoteSortOrder
FROM (
SELECT q.ProposalID ProposalID, ObjectID as QuoteID, QuoteSortOrder
FROM SharedObjects sq
JOIN Quotes q
ON sq.ObjectID = q.QuoteID
WHERE sq.UserLogin = '''+Convert(varchar,@UserLogin)+'''
AND sq.ObjectType = ''Q''
UNION
SELECT ObjectID ProposalID, q.QuoteID as QuoteID, QuoteSortOrder
FROM SharedObjects sp
INNER LOOP JOIN Quotes q
ON sp.ObjectID = q.ProposalID
WHERE sp.UserLogin = '''+Convert(varchar,@UserLogin)+'''
AND sp.ObjectType = ''P''
UNION
SELECT ProposalID, QuoteID, QuoteSortOrder
FROM Quotes q
WHERE q.UserLogin = '''+Convert(varchar,@UserLogin)+'''
UNION
SELECT q.ProposalID, q.QuoteID, QuoteSortOrder
FROM Quotes q
JOIN Proposals p
ON q.ProposalID = p.ProposalID
WHERE q.UserLogin = '''+Convert(varchar,@UserLogin)+'''
UNION
SELECT p.ProposalID, q.QuoteID, QuoteSortOrder
FROM Proposals p
INNER LOOP JOIN Quotes q
ON p.ProposalID = q.ProposalID
WHERE p.SalesPerson = '''+Convert(varchar,@UserLogin)+'''
) as uol
LEFT JOIN Quotes q
ON uol.QuoteID = q.QuoteID
LEFT JOIN Proposals p
ON uol.ProposalID = p.ProposalID
LEFT JOIN OrderBuckets ob
ON uol.ProposalID = ob.ProposalID
LEFT JOIN QuoteDEIStatus d
ON uol.QuoteID = d.QuoteID
LEFT JOIN Files f
ON uol.QuoteID = f.QuoteID
LEFT JOIN ProposalApprovalStatus pas
ON uol.ProposalID = pas.ProposalID
LEFT JOIN SharedObjects soq
ON uol.QuoteID = soq.ObjectID
AND soq.ObjectType = ''Q''
LEFT JOIN SharedObjects sop
ON uol.ProposalID = sop.ObjectID
AND sop.ObjectType = ''P''
WHERE
((p.ProposalID IS NOT NULL AND ISNULL(p.MovedToFlag,0) = '+Convert(varchar,@ReturnArchived)+')
or (p.ProposalID IS NULL AND ISNULL(q.Archived,0) = '+Convert(varchar,@ReturnArchived)+'))
and (
uol.QuoteID NOT IN(
select TOP 4 uol.QuoteID
FROM (
SELECT q.ProposalID ProposalID, ObjectID as QuoteID, QuoteSortOrder
FROM SharedObjects sq
JOIN Quotes q
ON sq.ObjectID = q.QuoteID
WHERE sq.UserLogin = '''+Convert(varchar,@UserLogin)+'''
AND sq.ObjectType = ''Q''
UNION
SELECT ObjectID ProposalID, q.QuoteID as QuoteID, QuoteSortOrder
FROM SharedObjects sp
INNER LOOP JOIN Quotes q
ON sp.ObjectID = q.ProposalID
WHERE sp.UserLogin = '''+Convert(varchar,@UserLogin)+'''
AND sp.ObjectType = ''P''
UNION
SELECT ProposalID, QuoteID, QuoteSortOrder
FROM Quotes q
WHERE q.UserLogin = '''+Convert(varchar,@UserLogin)+'''
UNION
SELECT q.ProposalID, q.QuoteID, QuoteSortOrder
FROM Quotes q
JOIN Proposals p
ON q.ProposalID = p.ProposalID
WHERE q.UserLogin = '''+Convert(varchar,@UserLogin)+'''
UNION
SELECT p.ProposalID, q.QuoteID, QuoteSortOrder
FROM Proposals p
INNER LOOP JOIN Quotes q
ON p.ProposalID = q.ProposalID
WHERE p.SalesPerson = '''+Convert(varchar,@UserLogin)+'''
) uol
LEFT JOIN Quotes q
ON uol.QuoteID = q.QuoteID
LEFT JOIN Proposals p
ON uol.ProposalID = p.ProposalID
LEFT JOIN OrderBuckets ob
ON uol.ProposalID = ob.ProposalID
LEFT JOIN QuoteDEIStatus d
ON uol.QuoteID = d.QuoteID
LEFT JOIN Files f
ON uol.QuoteID = f.QuoteID
LEFT JOIN ProposalApprovalStatus pas
ON uol.ProposalID = pas.ProposalID
LEFT JOIN SharedObjects soq
ON uol.QuoteID = soq.ObjectID
AND soq.ObjectType = ''Q''
LEFT JOIN SharedObjects sop
ON uol.ProposalID = sop.ObjectID
AND sop.ObjectType = ''P''
WHERE
((p.ProposalID IS NOT NULL AND ISNULL(p.MovedToFlag,0) = '+Convert(varchar,@ReturnArchived)+')
or (p.ProposalID IS NULL AND ISNULL(q.Archived,0) = '+Convert(varchar,@ReturnArchived)+'))
ORDER BY uol.ProposalID DESC,uol.QuoteSortOrder ASC, uol.QuoteID DESC
)
)
GROUP BY
p.SalesPerson,
q.UserLogin,
uol.ProposalID,
p.ProposalName,
p.ProposalDescription,
p.CreatedDate,
p.OpportunityID,
p.SASReviewer,
uol.QuoteID,
q.QuoteName,
q.QuoteDescription,
q.DateAmended,
d.UActStatus,
q.DEIRequired,
f.QuoteID,
pas.Status,
pas.SASStatus,
pas.Date,
-- obd.DateEnteredOM,
uol.QuoteSortOrder
ORDER BY uol.ProposalID DESC,uol.QuoteSortOrder ASC, uol.QuoteID DESC'

EXEC sp_executesql @QueryString

The query nested inside the where clause(last 'and' part- uol.QuoteID NOT IN(....)) of the outside query is the same as the outside query with the diffrence of select 'TOP 4 uol.QuoteID' instead of SELECT TOP '+CONVERT(nvarchar,@RecordsPerPage)+'
uol.ProposalID,
IsNull(p.ProposalName, ''Unassigned'') ProposalName,
IsNull(p.ProposalDescription, ''Unassigned'') ProposalDescription,
p.CreatedDate,
p.OpportunityID,
p.SASReviewer,
uol.QuoteID,
q.QuoteName,
q.QuoteDescription,
q.DateAmended,
CASE
WHEN q.DEIRequired = 1
THEN IsNull(d.UActStatus, ''Not Submitted'')
ELSE ''Not Required''
END DEIStatus,
COUNT(IsNull(soq.ObjectID, sop.ObjectID)) SharedCount,
COUNT(f.QuoteID) UCNCount,

pas.Status ProposalStatus,
pas.SASStatus SASProposalStatus,
Case
When pas.Status = ''Processed'' Then
MAX(ob.DateEnteredOM)
Else
pas.Date
End ApprovalStatusDate,
CASE q.UserLogin
WHEN '''+Convert(varchar,@UserLogin)+'''
THEN 0
ELSE 1
END IsQuoteOwned,
CASE p.SalesPerson
WHEN '''+Convert(varchar,@UserLogin)+'''
THEN 0
ELSE 1
END IsProposalOwned,
uol.QuoteSortOrder.

Thanks in advance.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-05 : 03:33:39

Can you please add [ code ] [/ code ] tags around your originally formatted query? Would make it a lot easier to read...

- Lumbago

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-05 : 03:36:22

And also post the output of PRINT @QueryString...

- Lumbago

sweta_singh
Starting Member

10 Posts

Posted - 2009-06-05 : 03:42:18

[ code ] Select @QueryString = 'SELECT TOP '+CONVERT(nvarchar,@RecordsPerPage)+'
uol.ProposalID,
IsNull(p.ProposalName, ''Unassigned'') ProposalName,
IsNull(p.ProposalDescription, ''Unassigned'') ProposalDescription,
p.CreatedDate,
p.OpportunityID,
p.SASReviewer,
uol.QuoteID,
q.QuoteName,
q.QuoteDescription,
q.DateAmended,
CASE
WHEN q.DEIRequired = 1
THEN IsNull(d.UActStatus, ''Not Submitted'')
ELSE ''Not Required''
END DEIStatus,
COUNT(IsNull(soq.ObjectID, sop.ObjectID)) SharedCount,
COUNT(f.QuoteID) UCNCount,

pas.Status ProposalStatus,
pas.SASStatus SASProposalStatus,
Case
When pas.Status = ''Processed'' Then
MAX(ob.DateEnteredOM)
Else
pas.Date
End ApprovalStatusDate,
CASE q.UserLogin
WHEN '''+Convert(varchar,@UserLogin)+'''
THEN 0
ELSE 1
END IsQuoteOwned,
CASE p.SalesPerson
WHEN '''+Convert(varchar,@UserLogin)+'''
THEN 0
ELSE 1
END IsProposalOwned,
uol.QuoteSortOrder
FROM (
SELECT q.ProposalID ProposalID, ObjectID as QuoteID, QuoteSortOrder
FROM SharedObjects sq
JOIN Quotes q
ON sq.ObjectID = q.QuoteID
WHERE sq.UserLogin = '''+Convert(varchar,@UserLogin)+'''
AND sq.ObjectType = ''Q''
UNION
SELECT ObjectID ProposalID, q.QuoteID as QuoteID, QuoteSortOrder
FROM SharedObjects sp
INNER LOOP JOIN Quotes q
ON sp.ObjectID = q.ProposalID
WHERE sp.UserLogin = '''+Convert(varchar,@UserLogin)+'''
AND sp.ObjectType = ''P''
UNION
SELECT ProposalID, QuoteID, QuoteSortOrder
FROM Quotes q
WHERE q.UserLogin = '''+Convert(varchar,@UserLogin)+'''
UNION
SELECT q.ProposalID, q.QuoteID, QuoteSortOrder
FROM Quotes q
JOIN Proposals p
ON q.ProposalID = p.ProposalID
WHERE q.UserLogin = '''+Convert(varchar,@UserLogin)+'''
UNION
SELECT p.ProposalID, q.QuoteID, QuoteSortOrder
FROM Proposals p
INNER LOOP JOIN Quotes q
ON p.ProposalID = q.ProposalID
WHERE p.SalesPerson = '''+Convert(varchar,@UserLogin)+'''
) as uol
LEFT JOIN Quotes q
ON uol.QuoteID = q.QuoteID
LEFT JOIN Proposals p
ON uol.ProposalID = p.ProposalID
LEFT JOIN OrderBuckets ob
ON uol.ProposalID = ob.ProposalID
LEFT JOIN QuoteDEIStatus d
ON uol.QuoteID = d.QuoteID
LEFT JOIN Files f
ON uol.QuoteID = f.QuoteID
LEFT JOIN ProposalApprovalStatus pas
ON uol.ProposalID = pas.ProposalID
LEFT JOIN SharedObjects soq
ON uol.QuoteID = soq.ObjectID
AND soq.ObjectType = ''Q''
LEFT JOIN SharedObjects sop
ON uol.ProposalID = sop.ObjectID
AND sop.ObjectType = ''P''
WHERE
((p.ProposalID IS NOT NULL AND ISNULL(p.MovedToFlag,0) = '+Convert(varchar,@ReturnArchived)+')
or (p.ProposalID IS NULL AND ISNULL(q.Archived,0) = '+Convert(varchar,@ReturnArchived)+'))
and (
uol.QuoteID NOT IN(
select TOP 4 uol.QuoteID
FROM (
SELECT q.ProposalID ProposalID, ObjectID as QuoteID, QuoteSortOrder
FROM SharedObjects sq
JOIN Quotes q
ON sq.ObjectID = q.QuoteID
WHERE sq.UserLogin = '''+Convert(varchar,@UserLogin)+'''
AND sq.ObjectType = ''Q''
UNION
SELECT ObjectID ProposalID, q.QuoteID as QuoteID, QuoteSortOrder
FROM SharedObjects sp
INNER LOOP JOIN Quotes q
ON sp.ObjectID = q.ProposalID
WHERE sp.UserLogin = '''+Convert(varchar,@UserLogin)+'''
AND sp.ObjectType = ''P''
UNION
SELECT ProposalID, QuoteID, QuoteSortOrder
FROM Quotes q
WHERE q.UserLogin = '''+Convert(varchar,@UserLogin)+'''
UNION
SELECT q.ProposalID, q.QuoteID, QuoteSortOrder
FROM Quotes q
JOIN Proposals p
ON q.ProposalID = p.ProposalID
WHERE q.UserLogin = '''+Convert(varchar,@UserLogin)+'''
UNION
SELECT p.ProposalID, q.QuoteID, QuoteSortOrder
FROM Proposals p
INNER LOOP JOIN Quotes q
ON p.ProposalID = q.ProposalID
WHERE p.SalesPerson = '''+Convert(varchar,@UserLogin)+'''
) uol
LEFT JOIN Quotes q
ON uol.QuoteID = q.QuoteID
LEFT JOIN Proposals p
ON uol.ProposalID = p.ProposalID
LEFT JOIN OrderBuckets ob
ON uol.ProposalID = ob.ProposalID
LEFT JOIN QuoteDEIStatus d
ON uol.QuoteID = d.QuoteID
LEFT JOIN Files f
ON uol.QuoteID = f.QuoteID
LEFT JOIN ProposalApprovalStatus pas
ON uol.ProposalID = pas.ProposalID
LEFT JOIN SharedObjects soq
ON uol.QuoteID = soq.ObjectID
AND soq.ObjectType = ''Q''
LEFT JOIN SharedObjects sop
ON uol.ProposalID = sop.ObjectID
AND sop.ObjectType = ''P''
WHERE
((p.ProposalID IS NOT NULL AND ISNULL(p.MovedToFlag,0) = '+Convert(varchar,@ReturnArchived)+')
or (p.ProposalID IS NULL AND ISNULL(q.Archived,0) = '+Convert(varchar,@ReturnArchived)+'))
ORDER BY uol.ProposalID DESC,uol.QuoteSortOrder ASC, uol.QuoteID DESC
)
)
GROUP BY
p.SalesPerson,
q.UserLogin,
uol.ProposalID,
p.ProposalName,
p.ProposalDescription,
p.CreatedDate,
p.OpportunityID,
p.SASReviewer,
uol.QuoteID,
q.QuoteName,
q.QuoteDescription,
q.DateAmended,
d.UActStatus,
q.DEIRequired,
f.QuoteID,
pas.Status,
pas.SASStatus,
pas.Date,
-- obd.DateEnteredOM,
uol.QuoteSortOrder
ORDER BY uol.ProposalID DESC,uol.QuoteSortOrder ASC, uol.QuoteID DESC'

EXEC sp_executesql @QueryString
[/ code ]

The output of ;
Print @QueryString is

SELECT TOP 2
uol.ProposalID,
IsNull(p.ProposalName, 'Unassigned') ProposalName,
IsNull(p.ProposalDescription, 'Unassigned') ProposalDescription,
p.CreatedDate,
p.OpportunityID,
p.SASReviewer,
uol.QuoteID,
q.QuoteName,
q.QuoteDescription,
q.DateAmended,
CASE
WHEN q.DEIRequired = 1
THEN IsNull(d.UActStatus, 'Not Submitted')
ELSE 'Not Required'
END DEIStatus,
COUNT(IsNull(soq.ObjectID, sop.ObjectID)) SharedCount,
COUNT(f.QuoteID) UCNCount,

pas.Status ProposalStatus,
pas.SASStatus SASProposalStatus, -- SR 74833
Case -- SR 48440
When pas.Status = 'Processed' Then
MAX(ob.DateEnteredOM)
Else
pas.Date
End ApprovalStatusDate,
CASE q.UserLogin
WHEN 'Sweta'
THEN 0
ELSE 1
END IsQuoteOwned,
CASE p.SalesPerson
WHEN 'Sweta'
THEN 0
ELSE 1
END IsProposalOwned,
uol.QuoteSortOrder
FROM (
SELECT q.ProposalID ProposalID, ObjectID as QuoteID, QuoteSortOrder
FROM SharedObjects sq
JOIN Quotes q
ON sq.ObjectID = q.QuoteID
WHERE sq.UserLogin = 'Sweta'
AND sq.ObjectType = 'Q'
UNION
SELECT ObjectID ProposalID, q.QuoteID as QuoteID, QuoteSortOrder
FROM SharedObjects sp
INNER LOOP JOIN Quotes q
ON sp.ObjectID = q.ProposalID
WHERE sp.UserLogin = 'Sweta'
AND sp.ObjectType = 'P'
UNION
SELECT ProposalID, QuoteID, QuoteSortOrder
FROM Quotes q
WHERE q.UserLogin = 'Sweta'
UNION
SELECT q.ProposalID, q.QuoteID, QuoteSortOrder
FROM Quotes q
JOIN Proposals p
ON q.ProposalID = p.ProposalID
WHERE q.UserLogin = 'Sweta'
UNION
SELECT p.ProposalID, q.QuoteID, QuoteSortOrder
FROM Proposals p
INNER LOOP JOIN Quotes q
ON p.ProposalID = q.ProposalID
WHERE p.SalesPerson = 'Sweta'
) as uol
LEFT JOIN Quotes q
ON uol.QuoteID = q.QuoteID
LEFT JOIN Proposals p
ON uol.ProposalID = p.ProposalID
LEFT JOIN OrderBuckets ob
ON uol.ProposalID = ob.ProposalID
LEFT JOIN QuoteDEIStatus d
ON uol.QuoteID = d.QuoteID
LEFT JOIN Files f
ON uol.QuoteID = f.QuoteID
LEFT JOIN ProposalApprovalStatus pas
ON uol.ProposalID = pas.ProposalID
LEFT JOIN SharedObjects soq
ON uol.QuoteID = soq.ObjectID
AND soq.ObjectType = 'Q'
LEFT JOIN SharedObjects sop
ON uol.ProposalID = sop.ObjectID
AND sop.ObjectType = 'P'
WHERE
((p.ProposalID IS NOT NULL AND ISNULL(p.MovedToFlag,0) = 0)
or (p.ProposalID IS NULL AND ISNULL(q.Archived,0) = 0))
and (
uol.QuoteID NOT IN(
select TOP 4 uol.QuoteID
FROM (
SELECT q.ProposalID ProposalID, ObjectID as QuoteID, QuoteSortOrder
FROM SharedObjects sq
JOIN Quotes q
ON sq.ObjectID = q.QuoteID
WHERE sq.UserLogin = 'Sweta'
AND sq.ObjectType = 'Q'
UNION
SELECT ObjectID ProposalID, q.QuoteID as QuoteID, QuoteSortOrder
FROM SharedObjects sp
INNER LOOP JOIN Quotes q
ON sp.ObjectID = q.ProposalID
WHERE sp.UserLogin = 'Sweta'
AND sp.ObjectType = 'P'
UNION
SELECT ProposalID, QuoteID, QuoteSortOrder
FROM Quotes q
WHERE q.UserLogin = 'Sweta'
UNION
SELECT q.ProposalID, q.QuoteID, QuoteSortOrder
FROM Quotes q
JOIN Proposals p
ON q.ProposalID = p.ProposalID
WHERE q.UserLogin = 'Sweta'
UNION
SELECT p.ProposalID, q.QuoteID, QuoteSortOrder
FROM Proposals p
INNER LOOP JOIN Quotes q
ON p.ProposalID = q.ProposalID
WHERE p.SalesPerson = 'Sweta'
) uol
LEFT JOIN Quotes q
ON uol.QuoteID = q.QuoteID
LEFT JOIN Proposals p
ON uol.ProposalID = p.ProposalID
LEFT JOIN OrderBuckets ob
ON uol.ProposalID = ob.ProposalID
LEFT JOIN QuoteDEIStatus d
ON uol.QuoteID = d.QuoteID

and the error that I am now getting is :

Msg 170, Level 15, State 1, Line 132
Line 132: Incorrect syntax near 'QuoteID'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-05 : 04:10:35

Here is your query in a formatted fashion. Now it's VERY obvious your dynamically built string is truncated before the end.
My conclusion is that your @QueryString variable needs to be declare NVARCHAR(MAX) !
I bet $10 that you today have declared your @QueryString variable as NVARCHAR(4000). Am I right or wrong?

SELECT TOP 2	uol.ProposalID,
		IsNull(p.ProposalName, 'Unassigned') ProposalName,
		IsNull(p.ProposalDescription, 'Unassigned') ProposalDescription,
		p.CreatedDate,
		p.OpportunityID,
		p.SASReviewer,
		uol.QuoteID,
		q.QuoteName,
		q.QuoteDescription,
		q.DateAmended,
		CASE 
			WHEN q.DEIRequired = 1 THEN IsNull(d.UActStatus, 'Not Submitted')
			ELSE 'Not Required'
		END DEIStatus,
		COUNT(IsNull(soq.ObjectID, sop.ObjectID)) SharedCount, 
		COUNT(f.QuoteID) UCNCount,
		pas.Status ProposalStatus,
		pas.SASStatus SASProposalStatus, -- SR 74833 
		Case -- SR 48440
			When pas.Status = 'Processed' Then MAX(ob.DateEnteredOM)
			Else pas.Date
		End ApprovalStatusDate,
		CASE q.UserLogin 
			WHEN 'Sweta' THEN 0 
			ELSE 1 
		END IsQuoteOwned,
		CASE p.SalesPerson 
			WHEN 'Sweta' THEN 0 
			ELSE 1 
		END IsProposalOwned,
		uol.QuoteSortOrder
FROM		(
			SELECT		q.ProposalID ProposalID,
					ObjectID as QuoteID, QuoteSortOrder
			FROM		SharedObjects sq
			JOIN		Quotes q ON sq.ObjectID = q.QuoteID
			WHERE		sq.UserLogin = 'Sweta'
					AND sq.ObjectType = 'Q'

			UNION 

			SELECT		ObjectID ProposalID,
					q.QuoteID as QuoteID,
					QuoteSortOrder
			FROM		SharedObjects sp
			INNER LOOP JOIN	Quotes q ON sp.ObjectID = q.ProposalID
			WHERE		sp.UserLogin = 'Sweta'
					AND sp.ObjectType = 'P'

			UNION 

			SELECT		ProposalID,
					QuoteID,
					QuoteSortOrder
			FROM		Quotes q
			WHERE		q.UserLogin = 'Sweta'

			UNION 

			SELECT		q.ProposalID, 
					q.QuoteID, 
					QuoteSortOrder
			FROM		Quotes q
			JOIN		Proposals p ON q.ProposalID = p.ProposalID
			WHERE		q.UserLogin = 'Sweta'

			UNION 

			SELECT		p.ProposalID,
					q.QuoteID,
					QuoteSortOrder
			FROM		Proposals p
			INNER LOOP JOIN	Quotes q ON p.ProposalID = q.ProposalID
			WHERE		p.SalesPerson = 'Sweta'
		) as uol
LEFT JOIN	Quotes q ON uol.QuoteID = q.QuoteID
LEFT JOIN	Proposals p ON uol.ProposalID = p.ProposalID 
LEFT JOIN	OrderBuckets ob ON uol.ProposalID = ob.ProposalID
LEFT JOIN	QuoteDEIStatus d ON uol.QuoteID = d.QuoteID
LEFT JOIN	Files f ON uol.QuoteID = f.QuoteID
LEFT JOIN	ProposalApprovalStatus pas ON uol.ProposalID = pas.ProposalID
LEFT JOIN	SharedObjects soq ON uol.QuoteID = soq.ObjectID
			AND soq.ObjectType = 'Q'
LEFT JOIN	SharedObjects sop ON uol.ProposalID = sop.ObjectID
			AND sop.ObjectType = 'P'
WHERE		(
			(p.ProposalID IS NOT NULL AND ISNULL(p.MovedToFlag,0) = 0)
			or
			(p.ProposalID IS NULL AND ISNULL(q.Archived,0) = 0)
		)
		and ( uol.QuoteID NOT IN(	select TOP 4	uol.QuoteID
						FROM		(
									SELECT		q.ProposalID ProposalID,
											ObjectID as QuoteID,
											QuoteSortOrder
									FROM		SharedObjects sq
									JOIN		Quotes q ON sq.ObjectID = q.QuoteID
									WHERE		sq.UserLogin = 'Sweta'
											AND sq.ObjectType = 'Q'

									UNION 
									
									SELECT		ObjectID ProposalID,
											q.QuoteID as QuoteID, 
											QuoteSortOrder
									FROM		SharedObjects sp
									INNER LOOP JOIN	Quotes q ON sp.ObjectID = q.ProposalID
									WHERE		sp.UserLogin = 'Sweta'
											AND sp.ObjectType = 'P'

									UNION 

									SELECT		ProposalID, 
											QuoteID, 
											QuoteSortOrder
									FROM		Quotes q
									WHERE		q.UserLogin = 'Sweta'

									UNION 

									SELECT		q.ProposalID,
											q.QuoteID,
											QuoteSortOrder
									FROM		Quotes q
									JOIN		Proposals p ON q.ProposalID = p.ProposalID
									WHERE		q.UserLogin = 'Sweta'

									UNION 

									SELECT		p.ProposalID,
											q.QuoteID, 
											QuoteSortOrder
									FROM		Proposals p
									INNER LOOP JOIN	Quotes q ON p.ProposalID = q.ProposalID
									WHERE		p.SalesPerson = 'Sweta'
								) uol
						LEFT JOIN	Quotes q ON uol.QuoteID = q.QuoteID
						LEFT JOIN	Proposals p ON uol.ProposalID = p.ProposalID 
						LEFT JOIN	OrderBuckets ob ON uol.ProposalID = ob.ProposalID
						LEFT JOIN	QuoteDEIStatus d ON uol.QuoteID = d.QuoteID

Where is the rest of code!?
There is at least two ) missing at the end!

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-05 : 04:21:43

Your @QueryString variable is not long enough to hold the remaining code below (about 1000 characters)

						LEFT JOIN	Files f ON uol.QuoteID = f.QuoteID
						LEFT JOIN	ProposalApprovalStatus pas ON uol.ProposalID = pas.ProposalID
						LEFT JOIN	SharedObjects soq ON uol.QuoteID = soq.ObjectID
									AND soq.ObjectType = 'Q'
						LEFT JOIN	SharedObjects sop ON uol.ProposalID = sop.ObjectID
									AND sop.ObjectType = 'P'
						WHERE		(
									(p.ProposalID IS NOT NULL AND ISNULL(p.MovedToFlag,0) = 0)
									or
									(p.ProposalID IS NULL AND ISNULL(q.Archived,0) = 0)
								)
						ORDER BY	uol.ProposalID DESC,
								uol.QuoteSortOrder ASC,
								uol.QuoteID DESC
					)
		)
GROUP BY	p.SalesPerson,
		q.UserLogin,
		uol.ProposalID,
		p.ProposalName,
		p.ProposalDescription,
		p.CreatedDate,
		p.OpportunityID,
		p.SASReviewer,
		uol.QuoteID,
		q.QuoteName,
		q.QuoteDescription,
		q.DateAmended,
		d.UActStatus,
		q.DEIRequired,
		f.QuoteID,
		pas.Status,
		pas.SASStatus, 
		pas.Date,
		-- obd.DateEnteredOM,
		uol.QuoteSortOrder
ORDER BY	uol.ProposalID DESC,
		uol.QuoteSortOrder ASC,
		uol.QuoteID DESC

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

sweta_singh
Starting Member

10 Posts

Posted - 2009-06-05 : 06:58:48

Heyyy, this indeed was the problem. I have now removed all the spaces in @QueryString used for indentation and it works. Thankyou so much Peso and the others who tried to help me out.(N by the way, u win the $10 bet :), I have declared it as nvarchar(4000), nvarchar(max) again gives an error)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-06 : 13:06:45

NVARCHAR(MAX) is only available for SQL Server 2005 and later.
And only if you also have your compatibility level set to 90 or higher.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 06:25:17

you can check it by using below query


SELECT @@VERSION
GO
EXEC sp_dbcmptlevel 'your db name here '
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources