problem running where table2.column1 in (table1.column2)

I have 2 tables
table1 contains data on a product, and has a column2 with a list of
values separated by a comma (ie. 1577,256,436). column2 is of type
varchar
table2 contains a column1 which has a list of ids (which are the ids
referenced in table1.column2) and another column with descriptions for
those ids. column1 is of type int
if i run a sql statement with a where clause
WHERE table2.column1 in (table1.column2)
i get an error saying its unable to convert varchar to int
the clause with the data in it should look like
WHERE table2.column1 in (1577,256,436)
is it not possible to pass a list of values from another table and
check against it, as if it was a haldfull of int values separated by a
comma?
ive also tried casting both columns to type varchar, and
table1.column2 adding single quotes using a replace() command so that
the values are varchar values, but end up with 0 results.
at the same time if i just run
WHERE table2.column1 in (1577,256,436)
meaning the data is not being passed im just typing it in, than the
query is ran just fine and produces the results im looking for.
any help on this is appreciated
You didn't provide table definition scripts nor sample data, but I am
guessing that you will need dynamic sql here.
create table #tmp1 (id int identity, a char(1))
insert #tmp1 values ('a')
insert #tmp1 values ('2')
insert #tmp1 values ('d')
create table #tmp2 (b varchar(10))
insert #tmp2 values ('1,2')
declare @.sql varchar(100)
select @.sql = 'select * from #tmp1 where id in (' + b + ')'
from #tmp2
print @.sql
exec (@.sql)
drop table #tmp1
drop table #tmp2
TheSQLGuru
President
Indicium Resources, Inc.
<igornik@.gmail.com> wrote in message
news:1187194472.590943.165600@.d55g2000hsg.googlegr oups.com...
>I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated
>
|||/*
Value lists are very bad design and do not belong in Relational
Databases.
I suspect that you currently have something that looks something like
this:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_ids varchar(16))
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_ids) values (1, '1,3,4')
insert into #t2 (t2_id, t1_ids) values (2, '1,2,3')
select * from #t1
select * from #t2
drop table #t2
drop table #t1
/*
I suspect you would try something like:
select
t1_id,
t2_id
from
t1,t2
where
t1_id in (t1_ids)
But that won't work, this value list should be stored in the proper
case below:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_id int)
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_id) values (1, 1)
insert into #t2 (t2_id, t1_id) values (1, 3)
insert into #t2 (t2_id, t1_id) values (1, 4)
insert into #t2 (t2_id, t1_ids) values (2, 1)
insert into #t2 (t2_id, t1_ids) values (2, 2)
insert into #t2 (t2_id, t1_ids) values (2, 3)
select * from #t1
select * from #t2
select
t1_id,
t2_id
from
#t1 t1
inner join #t2 t2 on t1.t1_id = t2.t1_id
drop table #t2
drop table #t1
On Aug 15, 12:14 pm, igor...@.gmail.com wrote:
> I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated
|||thanks for all of your help, this got me going into the right
direction again.
sql

problem running where table2.column1 in (table1.column2)

I have 2 tables
table1 contains data on a product, and has a column2 with a list of
values separated by a comma (ie. 1577,256,436). column2 is of type
varchar
table2 contains a column1 which has a list of ids (which are the ids
referenced in table1.column2) and another column with descriptions for
those ids. column1 is of type int
if i run a sql statement with a where clause
WHERE table2.column1 in (table1.column2)
i get an error saying its unable to convert varchar to int
the clause with the data in it should look like
WHERE table2.column1 in (1577,256,436)
is it not possible to pass a list of values from another table and
check against it, as if it was a haldfull of int values separated by a
comma?
ive also tried casting both columns to type varchar, and
table1.column2 adding single quotes using a replace() command so that
the values are varchar values, but end up with 0 results.
at the same time if i just run
WHERE table2.column1 in (1577,256,436)
meaning the data is not being passed im just typing it in, than the
query is ran just fine and produces the results im looking for.
any help on this is appreciatedYou didn't provide table definition scripts nor sample data, but I am
guessing that you will need dynamic sql here.
create table #tmp1 (id int identity, a char(1))
insert #tmp1 values ('a')
insert #tmp1 values ('2')
insert #tmp1 values ('d')
create table #tmp2 (b varchar(10))
insert #tmp2 values ('1,2')
declare @.sql varchar(100)
select @.sql = 'select * from #tmp1 where id in (' + b + ')'
from #tmp2
print @.sql
exec (@.sql)
drop table #tmp1
drop table #tmp2
TheSQLGuru
President
Indicium Resources, Inc.
<igornik@.gmail.com> wrote in message
news:1187194472.590943.165600@.d55g2000hsg.googlegroups.com...
>I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated
>|||/*
Value lists are very bad design and do not belong in Relational
Databases.
I suspect that you currently have something that looks something like
this:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_ids varchar(16))
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_ids) values (1, '1,3,4')
insert into #t2 (t2_id, t1_ids) values (2, '1,2,3')
select * from #t1
select * from #t2
drop table #t2
drop table #t1
/*
I suspect you would try something like:
select
t1_id,
t2_id
from
t1,t2
where
t1_id in (t1_ids)
But that won't work, this value list should be stored in the proper
case below:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_id int)
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_id) values (1, 1)
insert into #t2 (t2_id, t1_id) values (1, 3)
insert into #t2 (t2_id, t1_id) values (1, 4)
insert into #t2 (t2_id, t1_ids) values (2, 1)
insert into #t2 (t2_id, t1_ids) values (2, 2)
insert into #t2 (t2_id, t1_ids) values (2, 3)
select * from #t1
select * from #t2
select
t1_id,
t2_id
from
#t1 t1
inner join #t2 t2 on t1.t1_id = t2.t1_id
drop table #t2
drop table #t1
On Aug 15, 12:14 pm, igor...@.gmail.com wrote:
> I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated|||thanks for all of your help, this got me going into the right
direction again.

problem running where table2.column1 in (table1.column2)

I have 2 tables
table1 contains data on a product, and has a column2 with a list of
values separated by a comma (ie. 1577,256,436). column2 is of type
varchar
table2 contains a column1 which has a list of ids (which are the ids
referenced in table1.column2) and another column with descriptions for
those ids. column1 is of type int
if i run a sql statement with a where clause
WHERE table2.column1 in (table1.column2)
i get an error saying its unable to convert varchar to int
the clause with the data in it should look like
WHERE table2.column1 in (1577,256,436)
is it not possible to pass a list of values from another table and
check against it, as if it was a haldfull of int values separated by a
comma?
ive also tried casting both columns to type varchar, and
table1.column2 adding single quotes using a replace() command so that
the values are varchar values, but end up with 0 results.
at the same time if i just run
WHERE table2.column1 in (1577,256,436)
meaning the data is not being passed im just typing it in, than the
query is ran just fine and produces the results im looking for.
any help on this is appreciatedYou didn't provide table definition scripts nor sample data, but I am
guessing that you will need dynamic sql here.
create table #tmp1 (id int identity, a char(1))
insert #tmp1 values ('a')
insert #tmp1 values ('2')
insert #tmp1 values ('d')
create table #tmp2 (b varchar(10))
insert #tmp2 values ('1,2')
declare @.sql varchar(100)
select @.sql = 'select * from #tmp1 where id in (' + b + ')'
from #tmp2
print @.sql
exec (@.sql)
drop table #tmp1
drop table #tmp2
TheSQLGuru
President
Indicium Resources, Inc.
<igornik@.gmail.com> wrote in message
news:1187194472.590943.165600@.d55g2000hsg.googlegroups.com...
>I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated
>|||/*
Value lists are very bad design and do not belong in Relational
Databases.
I suspect that you currently have something that looks something like
this:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_ids varchar(16))
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_ids) values (1, '1,3,4')
insert into #t2 (t2_id, t1_ids) values (2, '1,2,3')
select * from #t1
select * from #t2
drop table #t2
drop table #t1
/*
I suspect you would try something like:
select
t1_id,
t2_id
from
t1,t2
where
t1_id in (t1_ids)
But that won't work, this value list should be stored in the proper
case below:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_id int)
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_id) values (1, 1)
insert into #t2 (t2_id, t1_id) values (1, 3)
insert into #t2 (t2_id, t1_id) values (1, 4)
insert into #t2 (t2_id, t1_ids) values (2, 1)
insert into #t2 (t2_id, t1_ids) values (2, 2)
insert into #t2 (t2_id, t1_ids) values (2, 3)
select * from #t1
select * from #t2
select
t1_id,
t2_id
from
#t1 t1
inner join #t2 t2 on t1.t1_id = t2.t1_id
drop table #t2
drop table #t1
On Aug 15, 12:14 pm, igor...@.gmail.com wrote:
> I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated|||thanks for all of your help, this got me going into the right
direction again.

Problem migrating NonEmptyCrossJoin query to AS2005

Hi,

In AS2000 we were using following query as a basis for product report:

select {[Measures].[Stock], [Measures].[Sales Qty YTD], [Measures].[Sales Qty LW]} on columns,
NonEmptyCrossJoin([Hierarchy].[Sub Category].Members, [SKU].[SKU Name].Members, [Supplier].[Short Name].Members) on rows
from eSalesActStock
where [DOp Calendar].[OpYear].[2006].[08.February].[Week 32]

It was used to show all products either on stock, or sold this year or both.
It was getting approx 9k rows as result
The cube used was a virtual one, merging stock and sales cubes.
The where clause is only to put calculated members from sales cube in context.
Dimensions are crossjoined only because we need them for grouping and on report layout. Every SKU has a supplier and is attached to assortment hierarchy.

In AS2005 we tried to recreate the dataset, and this is what we ended up with:

SELECT NON EMPTY { [Measures].[Sales Qty YTD], [Measures].[Stock],
[Measures].[Sales Qty LW]} ON COLUMNS,
NonEmpty ([DHierarchy].[DHierarchy].[Division].ALLMEMBERS * [DSKU].[DSKU Name].[DSKU Name].ALLMEMBERS *
[DSupplier].[DSupplier].[Supplier].ALLMEMBERS ) ON ROWS
FROM [SalesStock]
WHERE ( [DCalendar].[DOp Calendar].[OpYear].&[2006].&[08.February].&[Week 32] )

Unfortunately this query only results in 1.1k rows result, with the same data and parameters.
We have played a lot around with it, even trying to use nonemptycrossjoin but the results remained the same.
We did find out, that after removing where clause, the query retrieves 9k rows, which seems to be correct amount, but the data are useless, as calculated members do not have date context.

The only thing that differs from the AS2000 is that in AS2000 we had stock figures artificially tied to a date, to see the stock figures if you chose any date dimension filters. In AS2005 this is no longer necessary, so we just left date dimension and stock measure group unrelated. Can this be the cause of problem ? It seems unlikely, as crossjoin is made on different dimensions...

Are we missing something ? It used to work as the result of crossjoin was not related to where clause on non crossjoined dimension, now it seems that date dimension actually influences the results of the cross join...

Maybe there is a way to solve or walk around this problem ?

Any feedback appreciated :)

Thanks,

Marcin

Hi Marcin,

The role of the date wrt. stock data isn't clear to me; but since the NonEmpty() returns the right results without the where clause, try adding the default date as the filter:

>>

SELECT NON EMPTY { [Measures].[Sales Qty YTD], [Measures].[Stock],
[Measures].[Sales Qty LW]} ON COLUMNS,
NonEmpty ([DHierarchy].[DHierarchy].[Division].ALLMEMBERS * [DSKU].[DSKU Name].[DSKU Name].ALLMEMBERS *
[DSupplier].[DSupplier].[Supplier].ALLMEMBERS,

{[DCalendar].[DOp Calendar].DefaultMember} ) ON ROWS
FROM [SalesStock]
WHERE ( [DCalendar].[DOp Calendar].[OpYear].&[2006].&[08.February].&[Week 32] )
>>

|||Thanks a lot for your answer, it did solve the problem :)