Help With Query

[Damroo]

I have a table as below
---------------------------
name linkcode level
---------------------------
brando 1,3,8 1
damroo 1,5 2
rogers 2,7 1
shane 1,7 1
Now I want a query where I pass a level and linkcode (one of the codes
in Comma Seperated values in the record) and it returns me a recordset
with matching criteria like below

I have a variable called lcode (int) and a variable called ulevel (int)
if I pass lcode = 7 and level = 1 it should return rogers, shane in the
record set
My sql is as below

"Select name from tblnames where level =" & ulevel & " and " & lcode &
" in (linkcode)"

I am unable to run this query as it returns an error that
Syntax error converting the varchar value '1,3,8' to a column of data
type int

I even tried by updating the query with a UDF Split function, which
works fine for a specific string CSV as params but does not take the
CSV as a Fieldvalue
"Select name from tblnames where level =" & ulevel & " and " & lcode &
" in (select value from dbo.split(linkcode))"

returns
linkcode is not a recognized OPTIMIZER LOCK HINTS option

can anyone help with this.. or suggest an alternative SQL for my
problem..
I am using MS Sql Server 2000

thanks

[DA Morgan]

Damroo wrote:

I have a table as below
---------------------------
name linkcode level
---------------------------
brando 1,3,8 1
damroo 1,5 2
rogers 2,7 1
shane 1,7 1
Now I want a query where I pass a level and linkcode (one of the codes
in Comma Seperated values in the record) and it returns me a recordset
with matching criteria like below

I have a variable called lcode (int) and a variable called ulevel (int)
if I pass lcode = 7 and level = 1 it should return rogers, shane in the
record set
My sql is as below

"Select name from tblnames where level =" & ulevel & " and " & lcode &
" in (linkcode)"

I am unable to run this query as it returns an error that
Syntax error converting the varchar value '1,3,8' to a column of data
type int

I even tried by updating the query with a UDF Split function, which
works fine for a specific string CSV as params but does not take the
CSV as a Fieldvalue
"Select name from tblnames where level =" & ulevel & " and " & lcode &
" in (select value from dbo.split(linkcode))"

returns
linkcode is not a recognized OPTIMIZER LOCK HINTS option

can anyone help with this.. or suggest an alternative SQL for my
problem..
I am using MS Sql Server 2000

thanks

If your linkcode is truly holding multiple separate values what you have
here, by definition, is a bad design: Fix the design.

Trying to find a work-around is the same as trying trying to sing "la la
la" really loud to drown out the sound of someone telling you your house
is on fire.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[Simon Hayes]

This might work (another version of your first query):

"Select name from tblnames where level = " & ulevel & " and linkcode
like '%" & lcode & "%'"

However, in general storing a list in a single column indicates a
design problem, so you might want to look again at your implementation.
It's hard to be more specific without more details, though, and in
particular what "linkcode" represents - an ordered list, an unordered
list, a path in a hierarchy etc. If you can give some more information,
along with CREATE TABLE and INSERTs to show a sample case, someone may
be able to suggest an alternative approach.

This article has more information about handling delimited lists:

http://www.sommarskog.se/arrays-in-sql.html

Specifically, these comments seem to apply to your current situation:

http://www.sommarskog.se/arrays-in-s...#unpack-tblcol

Simon

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. By definition, this is not a table!! If you were
actually writing SQL and using Normal Forms would it look like this?

CREATE TABLE Foobar
(user_name CHAR(15) NOT NULL,
link_code INTEGER NOT NULL,
PRIMARY KEY (user_name,link_code),
foo_level INTEGER NOT NULL);

INSERT INTO Foobar VALUES ('brando', 1, 1);
INSERT INTO Foobar VALUES ('brando', 3, 1);
INSERT INTO Foobar VALUES ('brando', 8, 1);
INSERT INTO Foobar VALUES ('damroo', 1, 2);
INSERT INTO Foobar VALUES ('damroo', 5, 2);
INSERT INTO Foobar VALUES ('rogers', 2, 1);
INSERT INTO Foobar VALUES ('rogers', 7, 1);
INSERT INTO Foobar VALUES ('shane', 1, 1);
INSERT INTO Foobar VALUES ('shane', 7, 1);

a query where I pass a level and linkcode (one of the codes in Comma Seperated values in the record [sic]) and it returns me a recordset with matching criteria like below <<

Rows are not anything like records. Columns are supposed to be
scalars! No wonder you do not understand Normal Forms!!

SELECT user_name
FROM Foobar
WHERE foo_level = @my_lvl
AND link_code = @my_link;

See how easy it is when you follow the basics. Please read a book on
RDBMS before you code or post anything again.