Invalid column name error in sotred procedure

[swfmaster]

Hi,

I have been writing a stored procedure to retrieve a list of restaurants, but when using a function with the "AS" keyword to save the result into the fullmatch column, the WHERE statement does not recognize the fullmatch column, please help.

Here is the code:

ALTER PROCEDURE dbo.GetRestaurants
	(
	@kws NVARCHAR (50)
	)
	
AS	
	SELECT DISTINCT r.name, a.name , c.name , c.phone_key, dbo.findnumberofmatches(r.name,@kws) [B]AS fullmatch[/B]
	FROM  area AS a INNER JOIN
          restaurants AS r ON a.id = r.area INNER JOIN
          city AS c ON c.id = a.city
    WHERE [B]fullmatch > 0[/B]
	ORDER BY fullmatch DESC
RETURN

Thank you in advance

[Jim Doherty]

Hi,

I have been writing a stored procedure to retrieve a list of restaurants, but when using a function with the "AS" keyword to save the result into the fullmatch column, the WHERE statement does not recognize the fullmatch column, please help.

Here is the code:

ALTER PROCEDURE dbo.GetRestaurants
	(
	@kws NVARCHAR (50)
	)
	
AS	
	SELECT DISTINCT r.name, a.name , c.name , c.phone_key, dbo.findnumberofmatches(r.name,@kws) [B]AS fullmatch[/B]
	FROM  area AS a INNER JOIN
          restaurants AS r ON a.id = r.area INNER JOIN
          city AS c ON c.id = a.city
    WHERE [B]fullmatch > 0[/B]
	ORDER BY fullmatch DESC
RETURN

Thank you in advance

Amend to this to make it work (WHERE and SORT clauses on functions not optimum but if your db is small then ok)

SELECT DISTINCT r.name, a.name , c.name , c.phone_key, dbo.findnumberofmatches(r.name,@kws) [B]AS fullmatch[/B]
	FROM  area AS a INNER JOIN
          restaurants AS r ON a.id = r.area INNER JOIN
          city AS c ON c.id = a.city
    WHERE [B]dbo.findnumberofmatches(r.name,@kws) > 0[/B]
	ORDER BY [B]dbo.findnumberofmatches(r.name,@kws) [/B] DESC

Regards

Jim

[azimmer]

Amend to this to make it work (WHERE and SORT clauses on functions not optimum but if your db is small then ok)

SELECT DISTINCT r.name, a.name , c.name , c.phone_key, dbo.findnumberofmatches(r.name,@kws) [B]AS fullmatch[/B]
	FROM  area AS a INNER JOIN
          restaurants AS r ON a.id = r.area INNER JOIN
          city AS c ON c.id = a.city
    WHERE [B]dbo.findnumberofmatches(r.name,@kws) > 0[/B]
	ORDER BY [B]dbo.findnumberofmatches(r.name,@kws) [/B] DESC

Regards

Jim

If you do have too many rows, you can try this:

SELECT rname, aname , cname , phone_key, fullmatch
FROM (
SELECT DISTINCT r.name as rname, a.name as aname, c.name as cname, c.phone_key, dbo.findnumberofmatches(r.name,@kws) AS fullmatch
	FROM  area AS a INNER JOIN
          restaurants AS r ON a.id = r.area INNER JOIN
          city AS c ON c.id = a.city
) as jointable
WHERE fullmatch > 0
ORDER BY fullmatch DESC

[contributor]

Either u can still try this also and if too much pf rows will be selected, try this easuy pagination

Create Procedure <procedurenam e>
@kws varchar(<size>) ,
@pageNumber int = 0,
@pageSize int = 30

With jointable(rname ,aname,cname,ph one_key,fullmat ch)
As
(
SELECT DISTINCT r.name as rname, a.name as aname, c.name as cname, c.phone_key, dbo.findnumbero fmatches(r.name ,@kws) AS fullmatch,
ROW_NUMBER() over (order by a.name desc) as RowNum
FROM area AS a INNER JOIN
restaurants AS r ON a.id = r.area INNER JOIN
city AS c ON c.id = a.city
)

select * from jointable
where RowNum between ((@pageNumber * @pageSize) + 1)) and ((@pageNumber + 1) * @pageSize)


Best of luck

[swfmaster]

If you do have too many rows, you can try this:

SELECT rname, aname , cname , phone_key, fullmatch
FROM (
SELECT DISTINCT r.name as rname, a.name as aname, c.name as cname, c.phone_key, dbo.findnumberofmatches(r.name,@kws) AS fullmatch
	FROM  area AS a INNER JOIN
          restaurants AS r ON a.id = r.area INNER JOIN
          city AS c ON c.id = a.city
) as jointable
WHERE fullmatch > 0
ORDER BY fullmatch DESC

Thank you very much, it worked just fine.

[swfmaster]

Amend to this to make it work (WHERE and SORT clauses on functions not optimum but if your db is small then ok)

SELECT DISTINCT r.name, a.name , c.name , c.phone_key, dbo.findnumberofmatches(r.name,@kws) [B]AS fullmatch[/B]
	FROM  area AS a INNER JOIN
          restaurants AS r ON a.id = r.area INNER JOIN
          city AS c ON c.id = a.city
    WHERE [B]dbo.findnumberofmatches(r.name,@kws) > 0[/B]
	ORDER BY [B]dbo.findnumberofmatches(r.name,@kws) [/B] DESC

Regards

Jim

Thank you a lot, but i have tried that, and my DB is subject to expand repidly. although i think it's not the best programming practice to evaluate the function twice in a single query, but the jointable solution azimmer provided is great for this case, thank you again.

[swfmaster]

Either u can still try this also and if too much pf rows will be selected, try this easuy pagination

Create Procedure <procedurenam e>
@kws varchar(<size>) ,
@pageNumber int = 0,
@pageSize int = 30

With jointable(rname ,aname,cname,ph one_key,fullmat ch)
As
(
SELECT DISTINCT r.name as rname, a.name as aname, c.name as cname, c.phone_key, dbo.findnumbero fmatches(r.name ,@kws) AS fullmatch,
ROW_NUMBER() over (order by a.name desc) as RowNum
FROM area AS a INNER JOIN
restaurants AS r ON a.id = r.area INNER JOIN
city AS c ON c.id = a.city
)

select * from jointable
where RowNum between ((@pageNumber * @pageSize) + 1)) and ((@pageNumber + 1) * @pageSize)


Best of luck

Thank you for this one, i will use it in the future for sure :D