complicated select

through no fault of my own, there exists a table with varchar(50)
column, the values of which are comma separated integers. something
like so:
create table foo (
fookey int primary key not null,
foointegerlist1 varchar(50) null,
foointegerlist2 varchar(50) null,
fooprice money null,
foosize int null
)
insert into foo values (1, ',2,3', '1', 10, 100)
insert into foo values (2, '3', '4', null, 100)
insert into foo values (3, ',1,11', ',1,5,6', 10, 100)
insert into foo values (4, ',3,5', ',11', 10, null)
(yes, the leading comma will randomly appear)
unpleasant? yes. scheduled to be redesigned? yes. but for now, this is
what i have to contend with.
the problem i'm having is that there is another table with a very
similar column, like so:
create table foomatch (
foomatchkey int primary key not null,
foomatchintegerlist1 varchar(50) null
foomatchintegerlist2 varchar(50) null,
foomatchprice money null,
foomatchsize int null
)
insert into foomatch values (1, ',2', ',4,5,6', 10, 100)
insert into foomatch values (2, ',1,3,6', ',1', null, null)
the goal of the select statement is to capture all of the rows in the
table foo that "match" the values of a given single row in the foomatch
table. the rules for matching are as follows:
foomatch.foomatchprice = foo.fooprice AND
foomatch.foomatchsize = foo.foosize AND
(this is where it gets dicey for me)
at least ONE of the integer values in foomatch.foomatchintegerlist1
must occur in the foo.foointegerlist1 column AND
at least ONE of the integer values in foomatch.foomatchintegerlist2
must occur in the foo.foointegerlist1 column
where foomatch.foomatchkey = 1 (for example)
i have a user defined function that can turn a comma separated string
value into a single column table. i mention this in case building
tables of the values will make this easier / possible (perhaps with
relational math?)
thanks in advance for any help, and just let me know if you need
clarification,
jasonhi jason,
its quite a long story
use string manipulations to meet the desired solution.
we have several string functions that you can use
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"jason" wrote:

> through no fault of my own, there exists a table with varchar(50)
> column, the values of which are comma separated integers. something
> like so:
> create table foo (
> fookey int primary key not null,
> foointegerlist1 varchar(50) null,
> foointegerlist2 varchar(50) null,
> fooprice money null,
> foosize int null
> )
> insert into foo values (1, ',2,3', '1', 10, 100)
> insert into foo values (2, '3', '4', null, 100)
> insert into foo values (3, ',1,11', ',1,5,6', 10, 100)
> insert into foo values (4, ',3,5', ',11', 10, null)
> (yes, the leading comma will randomly appear)
> unpleasant? yes. scheduled to be redesigned? yes. but for now, this is
> what i have to contend with.
> the problem i'm having is that there is another table with a very
> similar column, like so:
> create table foomatch (
> foomatchkey int primary key not null,
> foomatchintegerlist1 varchar(50) null
> foomatchintegerlist2 varchar(50) null,
> foomatchprice money null,
> foomatchsize int null
> )
> insert into foomatch values (1, ',2', ',4,5,6', 10, 100)
> insert into foomatch values (2, ',1,3,6', ',1', null, null)
> the goal of the select statement is to capture all of the rows in the
> table foo that "match" the values of a given single row in the foomatch
> table. the rules for matching are as follows:
> foomatch.foomatchprice = foo.fooprice AND
> foomatch.foomatchsize = foo.foosize AND
> (this is where it gets dicey for me)
> at least ONE of the integer values in foomatch.foomatchintegerlist1
> must occur in the foo.foointegerlist1 column AND
> at least ONE of the integer values in foomatch.foomatchintegerlist2
> must occur in the foo.foointegerlist1 column
> where foomatch.foomatchkey = 1 (for example)
> i have a user defined function that can turn a comma separated string
> value into a single column table. i mention this in case building
> tables of the values will make this easier / possible (perhaps with
> relational math?)
> thanks in advance for any help, and just let me know if you need
> clarification,
> jason
>|||thanks for the reply Jose.
i spent some time looking at string manipulation approaches, i'm not
sure string manipulation will do the trick here. even if we boiled it
down to a single comparison, how would you use string manipulation to
answer the question are any of the comma separated elements in ',1,2,3'
also in the comma separated list ',3,4,5'? i've spent some time going
down this path, and it didn't yield anything close to sane results.
though if you can post something a little more specific, i'd be happy
to entertain the notion.
and yes, my explanations are rarely brief. this isn't even close to one
of my longer ones :)|||If you use tables or table valued user defined functions for the integer
lists it is reasonably straightforward:
Assuming there are tables/udfs foolist1 (fookey, foointeger1) ,
foomatchlist1 (foomatchkey, foomatchinteger1) and foomatchlist2
(foomatchkey, foomatchinteger2)
SELECT f.<column list>
FROM foo f
INNER JOIN foomatch fm
ON fm.foomatchprice = f.fooprice
AND fm.foomatchsize = f.foosize
WHERE EXISTS
(
SELECT NULL FROM foolist1 fl1
INNER JOIN foomatchlist1 fml1
ON fl1.foointeger1 = fml1.foomatchinteger1
WHERE fl1.fookey = f.fookey AND fml1.foomatchkey = fm.foomatchkey
)
AND EXISTS
(
SELECT NULL FROM foolist1 fl1
INNER JOIN foomatchlist2 fml2
ON fl1.foointeger1 = fml2.foomatchinteger2
WHERE fl1.fookey = f.fookey AND fml2.foomatchkey = fm.foomatchkey
)
Jacco Schalkwijk
SQL Server MVP
"jason" <iaesun@.yahoo.com> wrote in message
news:1128432811.427135.37460@.g44g2000cwa.googlegroups.com...
> through no fault of my own, there exists a table with varchar(50)
> column, the values of which are comma separated integers. something
> like so:
> create table foo (
> fookey int primary key not null,
> foointegerlist1 varchar(50) null,
> foointegerlist2 varchar(50) null,
> fooprice money null,
> foosize int null
> )
> insert into foo values (1, ',2,3', '1', 10, 100)
> insert into foo values (2, '3', '4', null, 100)
> insert into foo values (3, ',1,11', ',1,5,6', 10, 100)
> insert into foo values (4, ',3,5', ',11', 10, null)
> (yes, the leading comma will randomly appear)
> unpleasant? yes. scheduled to be redesigned? yes. but for now, this is
> what i have to contend with.
> the problem i'm having is that there is another table with a very
> similar column, like so:
> create table foomatch (
> foomatchkey int primary key not null,
> foomatchintegerlist1 varchar(50) null
> foomatchintegerlist2 varchar(50) null,
> foomatchprice money null,
> foomatchsize int null
> )
> insert into foomatch values (1, ',2', ',4,5,6', 10, 100)
> insert into foomatch values (2, ',1,3,6', ',1', null, null)
> the goal of the select statement is to capture all of the rows in the
> table foo that "match" the values of a given single row in the foomatch
> table. the rules for matching are as follows:
> foomatch.foomatchprice = foo.fooprice AND
> foomatch.foomatchsize = foo.foosize AND
> (this is where it gets dicey for me)
> at least ONE of the integer values in foomatch.foomatchintegerlist1
> must occur in the foo.foointegerlist1 column AND
> at least ONE of the integer values in foomatch.foomatchintegerlist2
> must occur in the foo.foointegerlist1 column
> where foomatch.foomatchkey = 1 (for example)
> i have a user defined function that can turn a comma separated string
> value into a single column table. i mention this in case building
> tables of the values will make this easier / possible (perhaps with
> relational math?)
> thanks in advance for any help, and just let me know if you need
> clarification,
> jason
>|||ahh! i think i see the solution here. yes, i think i can modify my
udf's slightly to make this work. i will give that a shot.
thanks very much
jason

Comparing varchar value in int type column

Hi,

I have a varchar(255) field on the control_value table which contains 10,159,711. These values are organization_ids (type int) separated by a common.

I am trying to exclude these organization IDs with the following statement:

DECLARE @.exclude_clients varchar(255)

SELECT @.exclude_clients = value

FROM control_value

WHERE parameter = 'client_excluded'

select * from organization org

where org.organization_id not in (@.exclude_clients)

I get the following error:

Server: Msg 245, Level 16, State 1, Line 7

Syntax error converting the varchar value '10,159,711' to a column of data type int.

Is there anyway around this?

You couldn't use variables as part of in clause.

But you could use dynamic SQL for with task:

Code Snippet

DECLARE @.exclude_clients varchar(255)

SELECT @.exclude_clients = value

FROM control_value

WHERE parameter = 'client_excluded'

declare @.query varchar(1000)

set @.query ='

select * from organization org

where org.organization_id not in ('+@.exclude_clients+')'

EXECUTE(@.query)

Another approach - split @.exclude_clients into table, then use join clause. You could use ideas from this link http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

|||

You can't use 'NOT IN' in this way - i.e. use it to identify multiple values from a comma-separated list provided as a single parameter.

The NOT IN condition as written will cause the entire @.exclude_clients string to be compared with org.organsation_id. However due to datatype precedence, SQL Server is attempting to convert the string to an integer (i.e. the same datatype as org.organsation_id) before the comparison - which is why you're experiencing the error. If the value of @.exclude_clients was '45' then the query wouldn't cause an error.

One way of performing the task would be to build up your SELECT statement dynamically, see below.

Chris

Code Snippet

DECLARE @.exclude_clients VARCHAR(255)

SELECT @.exclude_clients = value

FROM control_value

WHERE parameter = 'client_excluded'

/*

SELECT *

FROM organization org

WHERE org.organization_id NOT IN (@.exclude_clients)

*/

DECLARE @.sql VARCHAR(4000)

SET @.sql = 'SELECT * FROM organisation org WHERE org.organisation_id NOT IN ('

+ @.exclude_clients + ')'

EXEC (@.sql)

|||

Code Snippet

DECLARE @.T1 table(exclude_id int)

insert into @.t1
select 10 union
select 159 union
select 711

or if control_value is a table then

insert into @.t1
select value
from control_value
where parameter= 'client_excluded'

Then you can just use @.t1 as a table in your other query(ies)

select * from organization org
where org.organization_id not in (select exclude_id from @.t1)

|||Thanks Konstantin Kosinsky and Chris Howarth. The dynamic query works fine and it really helpful.