Quering Complex Column Data

Community Support
1

I am having trouble with searching for a value in a column which is oftype simple list.Here is the table schemacom:([id:()] l:())with sample data ascom upsert(1;1 2)com upsert(2;3 4)`com upsert(5;6 7 8)and if i do select from com i getid| l–| -----1 | 1 22 | 3 43 | 5 6 7How do i select the row that has a particular value in the list. Inthis example i want to find the row that has value 2 and i triedselect from com where 2 in l[i] but this does not work.ThanksBharani

2

Date: Thu, 6 Aug 2009 11:19:23 +0100Message-ID: <1855e77f0908060319k4c68a725r9325244ac7fb1a70@mail.gmail.com>Subject: Re: [personal kdb+] Quering Complex Column DataFrom: Attila Vrabecz <attila.vrabecz>To: personal-kdbplus@googlegroups.comq)select from com where 2 in/:lid| l–| —1 | 1 2 AttilaOn Thu, Aug 6, 2009 at 11:17 AM, bharani<bharani_vms> wrote:>> I am having trouble with searching for a value in a column which is of> type simple list.>> Here is the table schema>> com:([id:()] l:())>> with sample data as>> com upsert(1;1 2)&gt; com upsert(2;3 4)> `com upsert(5;6 7 8)>> and if i do select from com i get> id| l> --| -----> 1 | 1 2> 2 | 3 4> 3 | 5 6 7>> How do i select the row that has a particular value in the list. In> this example i want to find the row that has value 2 and i tried>> select from com where 2 in l[i] but this does not work.>> Thanks> Bharani>>> >></bharani_vms></attila.vrabecz>

3

Thanks!! it works :)To understand the answer when i say something on the where clause itapplies to the whole column and i guess that is the reason to go forin/: - am i close ?-BharaniOn Aug 6, 3:19?pm, Attila Vrabecz <attila.vrab…> wrote:> q)select from com where 2 in/:l> id| l> --| —> 1 | 1 2> ? Attila>>>> On Thu, Aug 6, 2009 at 11:17 AM, bharani<bharani_…> wrote:>> > I am having trouble with searching for a value in a column which is of> > type simple list.>> > Here is the table schema>> > com:([id:()] l:())>> > with sample data as>> > com upsert(1;1 2)&gt; &gt; com upsert(2;3 4)> > `com upsert(5;6 7 8)>> > and if i do select from com i get> > id| l> > --| -----> > 1 | 1 2> > 2 | 3 4> > 3 | 5 6 7>> > How do i select the row that has a particular value in the list. In> > this example i want to find the row that has value 2 and i tried>> > select from com where 2 in l[i] but this does not work.>> > Thanks> > Bharani</bharani_…></attila.vrab…>

4

X-Mailer: Apple Mail (2.936)absolutely correct(and not just in the where clause) AttilaOn 6 Aug 2009, at 12:07, bharani wrote:>> Thanks!! it works :)>> To understand the answer when i say something on the where clause it> applies to the whole column and i guess that is the reason to go for> in/: - am i close ?>> -Bharani>> On Aug 6, 3:19 pm, Attila Vrabecz <attila.vrab…> wrote:>> q)select from com where 2 in/:l>> id| l>> --| —>> 1 | 1 2>> Attila>>>>>>>> On Thu, Aug 6, 2009 at 11:17 AM, bharani<bharani_…> >> wrote:>>>>> I am having trouble with searching for a value in a column which >>> is of>>> type simple list.>>>>> Here is the table schema>>>>> com:([id:()] l:())>>>>> with sample data as>>>>> com upsert(1;1 2)&gt;&gt;&gt; com upsert(2;3 4)>>> `com upsert(5;6 7 8)>>>>> and if i do select from com i get>>> id| l>>> --| ----->>> 1 | 1 2>>> 2 | 3 4>>> 3 | 5 6 7>>>>> How do i select the row that has a particular value in the list. In>>> this example i want to find the row that has value 2 and i tried>>>>> select from com where 2 in l[i] but this does not work.>>>>> Thanks>>> Bharani> ></bharani_…></attila.vrab…>

5

Another way

select from com where l in {$[not (count x)=(x ? 2);:x;]} each l

?

?