Query help -- Incorrect Syntax 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 2000 Forums

SQL Server Development (2000)

Query help -- Incorrect Syntax Error

Author

Topic

zubairmasoodi
Starting Member

35 Posts

Posted - 2007-07-26 : 02:38:31

hi

I am getting this bad syntax error message

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.

SELECT A.*,B.* FROM as_tblmembers A
INNER JOIN dbo.as_fn_GetUserProfile(a.memberid) b on (a.memberID=b.memberid)

Can any one please spot where i am wrong
Intrestingly when i hard Code the value for the function argument i am able to execute the proc sucessfully

dbo.as_fn_GetUserProfile is a User defined Function that is returning a table

Please help

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-07-26 : 04:35:26

Hi,
You can not use column name as a parameter to UDF. It can only be a Variable Name or a Literal.

zubairmasoodi
Starting Member

35 Posts

Posted - 2007-07-26 : 04:43:29

quote:
Originally posted by shallu1_gupta

Hi,
You can not use column name as a parameter to UDF. It can only be a Variable Name or a Literal.

Thanks shallu

but i would like to know whether this limitation is specific to table-valued functions or all types of UDF's
because i have been doing it in the past where i pass the column to a Scalar functions and returns me the result

I can give a an Example if you want

With regards
Zubair Masoodi
(Every day's a school day)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-26 : 05:21:54

You can fix this with CROSS APPLY keyword, but then you have to move over to SQL Server 2005.

E 12°55'05.76"
N 56°04'39.42"

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-07-26 : 05:41:44

you are right you can use column names in Scalar functions but not in Table valued functions.

zubairmasoodi
Starting Member

35 Posts

Posted - 2007-07-26 : 06:23:02

quote:
Originally posted by Peso

You can fix this with CROSS APPLY keyword, but then you have to move over to SQL Server 2005.

E 12°55'05.76"
N 56°04'39.42"

Thanks peso,
how can i do it in SS2000, i am stuck, since i cannot make hafty changes in my proc..

With regards
Zubair Masoodi
(Every day's a school day)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-26 : 08:15:44

what does your function as_fn_GetUserProfile do ? Can you post it here ?

KH
[spoiler]Time is always against us[/spoiler]

zubairmasoodi
Starting Member

35 Posts

Posted - 2007-07-26 : 08:26:00

quote:
Originally posted by khtan

what does your function as_fn_GetUserProfile do ? Can you post it here ?

KH
[spoiler]Time is always against us[/spoiler]

CREATE FUNCTION dbo.as_fn_GetUserProfile ( @UserID int )
RETURNS @UserProfile TABLE
(
PPTCount int,
LastLogin nvarchar(50),
Contacts int,
UFirstname nvarchar(50),
ULastName nvarchar(50),
Ustate varchar(20),
MemberSince varchar(50),
UMemberID int
)
AS
BEGIN

DECLARE @PPTCount INT, @Contacts INT, @LastLogin VARCHAR(50)
SET @PPTCount = 0
SET @Contacts = 0
SELECT @PPTCount = COUNT(*) FROM aS_tblPresentations WHERE MemberID = @UserID AND PresentationStatus = 1
SELECT @LastLogin = CONVERT(VARCHAR, MIN(LoginDateTime), 100) FROM aS_tblUserLoginLogs WHERE MemberID = @UserID
SELECT @Contacts = COUNT(*) FROM aS_tblMemberContacts WHERE MemberID = @UserID

INSERT @UserProfile
SELECT @PPTCount,@LastLogin,@Contacts,FirstName, LastName, State,CONVERT(VARCHAR, CreatedDateTime, 106) AS MemberSince,MemberId
FROM aS_tblMembers
WHERE MemberID = @UserID
RETURN
END

With regards
Zubair Masoodi
(Every day's a school day)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-26 : 08:33:20

You can achieve what you want with a table function in SQL 2000.

Seems to me that what you need is just selecting the information from aS_tblMembers. Why not write it as a stored procedure and incorporate the what you do in as_fn_GetUserProfile into the stored procedure ?

KH
[spoiler]Time is always against us[/spoiler]

zubairmasoodi
Starting Member

35 Posts

Posted - 2007-07-26 : 08:50:29

quote:
Originally posted by khtan

You can achieve what you want with a table function in SQL 2000.

Seems to me that what you need is just selecting the information from aS_tblMembers. Why not write it as a stored procedure and incorporate the what you do in as_fn_GetUserProfile into the stored procedure ?

KH
[spoiler]Time is always against us[/spoiler]

Thanks khtan

The obvious solution is Like u have mentioned, but i am encountering problems while trying to do it that way, actually caused by the use of aggregate functions.. In my proc where i am using this functions , i have already used one aggregate function and whenever i make an attempt to include few more Count functions, it results me with some nasty joins.. the reason i fail to understand
the part of the Procedure where i intended to us this follows

SELECT P.PresentationID AS [ID], ISNULL(STUFF(PresentationTitle, 14, 200, '...'), PresentationTitle) AS [Title],
ISNULL(PresentationThumbNailPath,'No Path') AS ThumbnailPath, PresentationUniqueName AS UniqueName,
PresentationUniqueName + '.xml' AS XMLPath, TitleFormat as Title, PlayerType,PresentationTitle as [FullTitle],
dbo.aS_sp_GetDaysAgoString(DATEDIFF(Minute, P.CreatedDateTime, GETDATE())) AS DaysAgo, PresentationCategories,
LTRIM(RTRIM(PresentationCategories)) AS Category, dbo.as_fn_GetUserProfile(M.MemberID) as ProfileDetails,
ISNULL(LEFT(CAST(R.RatingNum AS FLOAT)/ CAST(R.CntRatings AS FLOAT), 3), 0) as RatingValue,
ISNULL(CntRatings,0) as RatingCount,
CASE -- Rating Image
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 1.0 AND 1.0 THEN '0-1'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 1.1 AND 1.5 THEN '1-5'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 1.6 AND 2.0 THEN '2-0'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 2.1 AND 2.5 THEN '2-5'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 2.6 AND 3.0 THEN '3-0'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 3.1 AND 3.5 THEN '3-5'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 3.6 AND 4.0 THEN '4-0'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 4.1 AND 4.5 THEN '4-5'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 4.6 AND 5.0 THEN '5-0'
ELSE '0-0'
END AS RatingImage ,
COUNT(C.PresentationID) as CommentsCount,
ISNULL(PresentationViews,0) AS ViewsCount,
DisplayName, M.MemberID as UserID,FirstName, LastName, State,CONVERT(VARCHAR, M.CreatedDateTime, 106) as MemberSince
FROM aS_tblPresentations P
JOIN aS_tblMembers M ON (M.MemberID = P.MemberID)
--LEFT OUTER JOIN dbo.as_fn_GetUserProfile(M.MemberID) Pr ON (Pr.UMemberID = M.MemberID)
LEFT OUTER JOIN aS_tblRatings R on(R.PresentationID=P.PresentationID)
LEFT OUTER JOIN aS_tblComments C on(C.PresentationID=P.PresentationID)

JOIN aS_tblCategories Ct ON (Ct.CategoryID = P.CategoryID AND Ct.PresentationCategories = @v_CategoryName)
WHERE P.PresentationStatus = 1 --AND P.CategoryID NOT IN (SELECT CategoryID FROM aS_tblCategories WHERE Order_ = 0 and )
AND P.CreatedDateTime BETWEEN @StartDate AND @LatestEndDate
GROUP BY P.PresentationID, PresentationTitle, PresentationDescription, PresentationThumbNailPath,
PresentationStoragePath, PresentationUniqueName, TitleFormat, PlayerType, PresentationViews, PresentationCategories, CntRatings, RatingNum, DisplayName, M.MemberID, P.CreatedDateTime, --,Pr.UFirstName
FirstName, LastName, State, CONVERT(VARCHAR, M.CreatedDateTime, 106)
ORDER By P.CreatedDateTime DESC

please see if u can guide me

With regards
Zubair Masoodi
(Every day's a school day)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-26 : 08:52:07

Or use subqueries?

SELECT a.*,
(SELECT COUNT(*) FROM aS_tblPresentations as p WHERE p.MemberID = a.memberID AND p.PresentationStatus = 1 ) AS PPTCount ,
(SELECT CONVERT(VARCHAR, MIN(LoginDateTime), 100) FROM aS_tblUserLoginLogs as l WHERE l.MemberID = a.memberID ) AS LastLogin,
(SELECT COUNT(*) FROM aS_tblMemberContacts as c WHERE c.MemberID = a.memberID ) AS Contacts
FROM as_tblmembers as A

E 12°55'05.76"
N 56°04'39.42"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-26 : 09:11:39

Your view can be re-write using INNER JOIN as

SELECT 	p.PPTCount, l.LastLogin, c.Contacts, a.FirstName, a.LastName, m.State, m.CreatedDateTime AS MemberSince, m.MemberId
FROM 	aS_tblMembers a
	INNER JOIN
	(
		SELECT	MemberID, PPTCount = COUNT(*)
		FROM	aS_tblPresentations
		GROUP BY MemberID
	) p	ON	a.MemberId	= p.MemberId
	INNER JOIN
	(
		SELECT	MemberID, LastLogin = MIN(loginDateTime)
		FROM	aS_tblUserLoginLogs
		GROUP BY MemberID
	) l	ON	a.MemberId	= l.MemberId
	INNER JOIN
	(
		SELECT	MemberID, Contacts = COUNT(*)
		FROM	aS_tblMemberContacts
		GROUP BY MemberID
	) c	ON	a.MemberID	= c.MemberId

Then you can use it to JOIN to other tables

KH
[spoiler]Time is always against us[/spoiler]

zubairmasoodi
Starting Member

35 Posts

Posted - 2007-07-27 : 06:32:15

Thanks Khtan

This is simply great solution....

Thanks a lot

With regards
Zubair Masoodi
(Every day's a school day)

zubairmasoodi
Starting Member

35 Posts

Posted - 2007-07-30 : 08:02:13

quote:

Originally posted by khtan

Your view can be re-write using INNER JOIN as

SELECT 	p.PPTCount, l.LastLogin, c.Contacts, a.FirstName, a.LastName, m.State, m.CreatedDateTime AS MemberSince, m.MemberId
FROM 	aS_tblMembers a
	INNER JOIN
	(
		SELECT	MemberID, PPTCount = COUNT(*)
		FROM	aS_tblPresentations
		GROUP BY MemberID
	) p	ON	a.MemberId	= p.MemberId
	INNER JOIN
	(
		SELECT	MemberID, LastLogin = MIN(loginDateTime)
		FROM	aS_tblUserLoginLogs
		GROUP BY MemberID
	) l	ON	a.MemberId	= l.MemberId
	INNER JOIN
	(
		SELECT	MemberID, Contacts = COUNT(*)
		FROM	aS_tblMemberContacts
		GROUP BY MemberID
	) c	ON	a.MemberID	= c.MemberId

Then you can use it to JOIN to other tables

KH
[spoiler]Time is always against us[/spoiler]

Khtan , a little more help is needed

With the resultset that i am getting from the above Query , i need one additional Field "Country Name" which is one of the Cloumns in as_TblCountries.

In as_tblMembers we have the Id stored for the Country Field

The Structure of as_TblCountries is

CountryId
CountryName

The Structure of as_Tbmembers

---Some fields
Country [In this field we store the Id CountryId of the as_TblCountries table ]

If u can Please Sort it Out , i will be Thankful

With regards
Zubair Masoodi
(Every day's a school day)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-30 : 13:00:49

[code]SELECT p.PPTCount, l.LastLogin, c.Contacts, a.FirstName, a.LastName, m.State, m.CreatedDateTime AS MemberSince, m.MemberId,
t.CountryName
FROM aS_tblMembers a
INNER JOIN
(
SELECT MemberID, PPTCount = COUNT(*)
FROM aS_tblPresentations
GROUP BY MemberID
) p ON a.MemberId = p.MemberId
INNER JOIN
(
SELECT MemberID, LastLogin = MIN(loginDateTime)
FROM aS_tblUserLoginLogs
GROUP BY MemberID
) l ON a.MemberId = l.MemberId
INNER JOIN
(
SELECT MemberID, Contacts = COUNT(*)
FROM aS_tblMemberContacts
GROUP BY MemberID
) c ON a.MemberID = c.MemberId
INNER JOIN as_TblCountries t
ON a.Country = t.CountryId[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources