Lists: | pgsql-general |
---|
From: | "Kelly McTiernan" <kelly(dot)mctiernan(at)verizon(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Work Around For Oracle Feature |
Date: | 2002-02-24 01:32:36 |
Message-ID: | a59g8f$1m88$1@jupiter.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hi!
I've been unable to get nested queries to work. e.g:
select x,y from z where y in (select r from t);
fails in a JDBC call (JBoss JDBC Driver). I wound up having to do =>
select r from t;
iterate through the result set and build a string list of str = 'a,b,c...',
then do a select like:
"select x,y from z where y in (" + str + ")"
Anyone have any ideas?
"David Griffiths" <dgriffiths(at)boats(dot)com> wrote in message
news:a4bu6e$2ipc$1(at)jupiter(dot)hub(dot)org(dot)(dot)(dot)
> One of our two databases uses context indexes on a column.
>
> If your not familiar, this is part of the Oracle interMedia option. The
idea
> here is to be able to search for words within a text block. The text block
> is in a row, in one of the columns. It can also be HTML, a Word document,
> etc. When you index the column, it speeds up finding matching words.
>
> Once that's done, you can search like this:
>
> SELECT SCORE(1) title FROM news_items
> WHERE CONTAINS(text, 'Bolivia', 1) > 0;
>
> This would find all rows in the table "news_item" that has the text
> "Bolivia" in the "title" column.
>
> SELECT SCORE(1) title FROM news_items
> WHERE CONTAINS(text, 'Bolivia OR Peru', 1) > 0;
>
> This would find all rows in the table "news_item" that has the text
> "Bolivia" or "Peru" in the "title" column in any order.
>
> This is the most basic usage of the interMedia (you can get it to do a
> soundex to match a sound, or get a "near" match or fuzzy match rather than
> an exact match, etc.)
>
> But I'm looking for the basic "return all rows where the text in this
column
> returns this word or these words".
>
> My two thoughts are, write my own function in PERL, or store the text in a
> file outside the database and use some OS-features to do the search
>
> Any suggestions?
>
> David
>
>
>
From: | Doug McNaught <doug(at)wireboard(dot)com> |
---|---|
To: | "Kelly McTiernan" <kelly(dot)mctiernan(at)verizon(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Work Around For Oracle Feature |
Date: | 2002-02-25 15:20:39 |
Message-ID: | m3u1s5ziyg.fsf@varsoon.denali.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Kelly McTiernan" <kelly(dot)mctiernan(at)verizon(dot)net> writes:
> Hi!
> I've been unable to get nested queries to work. e.g:
>
> select x,y from z where y in (select r from t);
>
> fails in a JDBC call (JBoss JDBC Driver). I wound up having to do =>
"Fails". What's the error message you get? That kind of query works
fine in general. Have you tried the same query in 'psql' to see if it
works there?
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Kelly McTiernan" <kelly(dot)mctiernan(at)verizon(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Work Around For Oracle Feature |
Date: | 2002-02-25 15:48:35 |
Message-ID: | 21161.1014652115@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Kelly McTiernan" <kelly(dot)mctiernan(at)verizon(dot)net> writes:
> I've been unable to get nested queries to work. e.g:
> select x,y from z where y in (select r from t);
Looks fine to me. What happens exactly?
regards, tom lane
From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Kelly McTiernan <kelly(dot)mctiernan(at)verizon(dot)net> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Work Around For Oracle Feature |
Date: | 2002-02-25 16:20:30 |
Message-ID: | 20020225082013.A75578-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Sat, 23 Feb 2002, Kelly McTiernan wrote:
> Hi!
> I've been unable to get nested queries to work. e.g:
>
> select x,y from z where y in (select r from t);
What error do you get?
From: | "Kelly McTiernan" <kelly(dot)mctiernan(at)verizon(dot)net> |
---|---|
To: | "Stephan Szabo" <sszabo(at)megazone23(dot)bigpanda(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Work Around For Oracle Feature |
Date: | 2002-02-26 03:52:24 |
Message-ID: | 002701c1be79$00413c00$640110ac@koalact5fu9ujz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
My apologies. I don't know what I wac doing wrong, but when I tried coding
it for a second time, it worked just fine! I do still have a problem with
container managed transactions, but that's a JBoss issue (for that matter
Weblogic has similar issues). I can get around that one by setting
transaction not-supported, and doing my own commits. Thanks.
Kelly McTiernan
----- Original Message -----
From: "Stephan Szabo" <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: "Kelly McTiernan" <kelly(dot)mctiernan(at)verizon(dot)net>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Monday, February 25, 2002 8:20 AM
Subject: Re: [GENERAL] Work Around For Oracle Feature
> On Sat, 23 Feb 2002, Kelly McTiernan wrote:
>
> > Hi!
> > I've been unable to get nested queries to work. e.g:
> >
> > select x,y from z where y in (select r from t);
>
> What error do you get?
>
>
>