search LIST column type
Folks,
ID11.50 FC8, Redhat Linux
I tested the LIST column type, and confused about a simple search.
create table a ( c1 int, c2 list ( varchar(255) not null));
--insert into a values(10, List{'aa','bb','cc'});
--insert into a values(20, List{' hi',' hello','hey'});
select * from a;
c1 10
c2 LIST{'aa','bb','cc'}
c1 20
c2 LIST{' hi',' hello','hey'}
But, the following queries always get nothing!
select * from a
where c1=10
and list {'aa'} in (c2);
select * from a
where
list {'aa'} in (c2);
How can we check a LIST contains a element or sub-list?
Thanks,
Frank
--e89a8f23505b011dbb04b749e910
Frank:
You can use the "IN" keyword in the WHERE clause of
an SQL statement to determine whether a collection cotains
a specific element.
select *
from a
where 'aa' in c2
RETURNS
===========
c1 10
c2 LIST{'aa','bb','cc'}
For more information on this topic please
see the section titled "Select from a collection"
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
(Embedded image moved to file: pic27672.gif)
ids-bounces@iiug.org wrote on 01/24/2012 09:59:40 AM:
> From: "FRANK" <yunyaoqu@gmail.com>
> To: ids@iiug.org
> Date: 01/24/2012 10:00 AM
> Subject: search LIST column type [26026]
> Sent by: ids-bounces@iiug.org
>
> Folks,
>
> ID11.50 FC8, Redhat Linux
>
> I tested the LIST column type, and confused about a simple search.
>
> create table a ( c1 int, c2 list ( varchar(255) not null));>
> --insert into a values(10, List{'aa','bb','cc'});
> --insert into a values(20, List{' hi',' hello','hey'});
>
> select * from a;>
> c1 10
> c2 LIST{'aa','bb','cc'}
>
> c1 20
> c2 LIST{' hi',' hello','hey'}
>
> But, the following queries always get nothing!
>
> select * from a
> where c1=10
> and list {'aa'} in (c2)> ;
>
> select * from a
> where
> list {'aa'} in (c2)> ;
>
> How can we check a LIST contains a element or sub-list?
>
> Thanks,
> Frank
>
> --e89a8f23505b011dbb04b749e910
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
John,
That is excellent !
I do not know why I could not find such good example in the Infocenter
search.... I need search harder ... :-)
Thanks a lot John!
Frank
On Tue, Jan 24, 2012 at 3:45 PM, John Miller iii <miller3@us.ibm.com> wrote:
> Frank:
>
> You can use the "IN" keyword in the WHERE clause of
> an SQL statement to determine whether a collection cotains
> a specific element.
>
> select *
> from a
> where 'aa' in c2>
> RETURNS
> ===========
> c1 10
> c2 LIST{'aa','bb','cc'}
>
> For more information on this topic please
> see the section titled "Select from a collection"
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
> (Embedded image moved to file: pic27672.gif)
>
> ids-bounces@iiug.org wrote on 01/24/2012 09:59:40 AM:
>
> > From: "FRANK" <yunyaoqu@gmail.com>
> > To: ids@iiug.org
> > Date: 01/24/2012 10:00 AM
> > Subject: search LIST column type [26026]
> > Sent by: ids-bounces@iiug.org
> >
> > Folks,
> >
> > ID11.50 FC8, Redhat Linux
> >
> > I tested the LIST column type, and confused about a simple search.
> >
> > create table a ( c1 int, c2 list ( varchar(255) not null));> >
> > --insert into a values(10, List{'aa','bb','cc'});
> > --insert into a values(20, List{' hi',' hello','hey'});
> >
> > select * from a;> >
> > c1 10
> > c2 LIST{'aa','bb','cc'}
> >
> > c1 20
> > c2 LIST{' hi',' hello','hey'}
> >
> > But, the following queries always get nothing!
> >
> > select * from a
> > where c1=10
> > and list {'aa'} in (c2)> > ;
> >
> > select * from a
> > where
> > list {'aa'} in (c2)> > ;
> >
> > How can we check a LIST contains a element or sub-list?
> >
> > Thanks,
> > Frank
> >
> > --e89a8f23505b011dbb04b749e910
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d0442681a86fd0404b74cecc5