Receiving an syntax error
-
I am assuming that the problems is the way that i have the WHERE clause at the bottom.
Is it not possiable to concatenate as i have below? (At the very bottom)
Thanks,
ERIK...
ALTER PROCEDURE
GetBlindMenuInfoCT16
@IDCompany
int
AS
SELECT DISTINCT
Company.CompanyID, Company.CompanyName, Company.CompanyLogo, Product.ProductID, Categories.ProductCategoryID, Categories.ProductCategoryName
FROM
Company INNER JOIN
Product ON Company.CompanyID = Product.CompanyID
INNER JOIN
Categories
ON Categories.ProductCategoryID = Product.ProductCategoryID
WHERE
Company.CompanyID = @IDCompany
AND
WHERE
Categories.ProductCategoryID = 16, 32, 33, 34, 45, 48, 64, 80, 96
Dam again!
-
Is this what you mean?
Where Categories.ProductCategoryID in (16, 32, 33, 34, 45, 48, 64, 80, 96)
Because if you want ProductCategoryID = 16 and ProductCategoryID = 32... then it's just not possible.
What are you trying to do exactly?
-
In case I'm gone when you read this...
This may help you :
IF Object_id('fnSplit_Set') > 0
DROP FUNCTION fnSplit_Set
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, PkNumber + len(@vcDelimiter)) - PkNumber - len(@vcDelimiter)) as EachID
FROM dbo.Numbers
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
GO
IF Object_id('Numbers') IS NULL
BEGIN
--I use this table for many other string operations as well
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
Declare @i as int
set @i = 0
while @i = @ItemsCount
)
ORDER BYName
SET NOCOUNT OFF
GO
--find all items
Exec dbo.SearchColumns 'name,id', 2-- 4 row(s) affected
Exec dbo.SearchColumns 'name,id,indid', 3-- 1 row(s) affected
Exec dbo.SearchColumns 'id', 1-- 12 row(s) affected
--find at least 2
Exec dbo.SearchColumns 'name,id,indid', 2-- 5 row(s) affected (instead of 1)
--find at least 1
Exec dbo.SearchColumns 'name', 1-- 10 row(s) affected
Exec dbo.SearchColumns 'name,indid', 1-- 11 row(s) affected
GO
DROP PROCEDURE SearchColumns
--DROP FUNCTION fnSplit_Set
GO
that too :
The Curse and Blessings of Dynamic SQL
-
Holly Molly!
I see i have some studing to do. Thank you very much for taking the time to get this to me.
Let me get to studing.
Thank You,
Dam again!
-
I am pulling only the describe categories from the category table
Where Categories.ProductCategoryID in (16, 32, 33, 34, 45, 48, 64, 80, 96)
some times i have to refresh the database or things act a little funny with procedures
Dam again!
-
Why not add a type of categorie and hardcode that type only?? You know you'll have to change this list everytime a new category fits it. This is not well designed.
-
You are the teacher and i am the newbie.
I am 7 months young to the vb.net sql2000 world. It is just in the last month or so that all of this is not overwhelming. That is why i am so concerned about the pricing of the blinds.
I am doing well with vb.net and sql 2000; however, when it comes to some things i get stuck for days. Like the simple (TO YOU
) Query with the array looking design. The deal with this one is that the categories table and the company table do not have a direct connection ,or FK_, Therefore i am into new water again, i have been working with this one all day, tring to figure out the best way to implement it.
When you say Type table, can you be a little more explanatory.
Thanks,
Erik
Dam again!
-
14 months old here
.Don't know your system enough, but you seem to have categories, but you also seem to have types of categories (from the list of in values you passed). I'd create another lookup tables for the types of categories and populate that information (or have the users do it). Then the in clause can look something like this :
where id in (Select categoryid from dbo.Categories where CategoryTypeid = 5).
Then you can add as many categories to this type and the proc still works.
-
This one took a minute to soak in but i see whats going on with it.
VERY NICE!
I see whats going on now let me see i can make that rabbit hop.
i'll Send you a message if i get stuck.
Erik
Dam again!
-
Alright... what part took a minute to soak in??
-
where id in
Dam again!
-
I am receiving an syntax error.
Syntax Error near 'Where'
ALTER PROCEDURE
GetBlindMenuInfoCTaLL
@IDCompany
int
AS
SELECT DISTINCT
Company.CompanyID, Company.CompanyName, Company.CompanyLogo, Product.ProductID, Categories.ProductCategoryID, Categories.ProductCategoryName
FROM
Company INNER JOIN
Product ON Company.CompanyID = Product.CompanyID
INNER JOIN
Categories
ON Categories.ProductCategoryID = Product.ProductCategoryID
WHERE
Company.CompanyID = @IDCompany
AND
WHERE
id in (Select ProductCategoryID from dbo.Categories where CategoryTypeid = 20)
I have tried it with and with out the id by where
Dam again!
-
This is a little better.
ALTER PROCEDURE
GetBlindMenuInfoCTaLL
@IDCompany int
AS
SELECT DISTINCT Company.CompanyID, Company.CompanyName, Company.CompanyLogo, Product.ProductID, Categories.ProductCategoryID, Categories.ProductCategoryName
FROM
Company INNER JOIN
Product ON Company.CompanyID = Product.CompanyID
INNER JOIN
Categories
ON Categories.ProductCategoryID = Product.ProductCategoryID
WHERE
Company.CompanyID = @IDCompany
AND
WHERE IN
(Select Categories.ProductCategoryID from Categories where Categories.CategoryTypeid = 20)
Dam again!
-
I got it. Man i am always tring to make it hard on myself.
===============================+++++++++++++
ALTER PROCEDURE
GetBlindMenuInfoCTaLL
@IDCompany
int
AS
SELECT DISTINCT
Company.CompanyID, Company.CompanyName, Company.CompanyLogo, Product.ProductID, Categories.ProductCategoryID, Categories.ProductCategoryName
FROM
Company INNER JOIN
Product ON Company.CompanyID = Product.CompanyID
INNER JOIN
Categories
ON Categories.ProductCategoryID = Product.ProductCategoryID
WHERE
Company.CompanyID = @IDCompany
AND
Categories.CategoryTypeid = 20
Dam again!
-
Yup... still only one where keyword is allowed per select
.
Viewing 15 posts - 1 through 15 (of 66 total)
You must be logged in to reply to this topic. Login to reply