Microsoft SQL Server forum - Help With Query

Author

Help With Query

Damroo

2005-08-22, 9:23 am

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

2005-08-22, 11:23 am

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
damorgan@x.washington.edu
(replace x with u to respond)

Simon Hayes

2005-08-22, 11:23 am

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-...l#unpack-tblcol

Simon

--CELKO--

2005-08-22, 8:23 pm

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);
[color=darkred]

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.

Sponsored Links