Re: Work Around For Oracle Feature
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@boats.com> wrote in message
news:a4bu6e$2ipc$1@jupiter.hub.org...
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
Show quoted text
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 searchAny suggestions?
David
Import Notes
Reference msg id not found: a4bu6e$2ipc$1@jupiter.hub.org
"Kelly McTiernan" <kelly.mctiernan@verizon.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
Import Notes
Reply to msg id not found: KellyMcTiernan'smessageofSat23Feb2002173236-0800Reference msg id not found: a4bu6e$2ipc$1@jupiter.hub.org
"Kelly McTiernan" <kelly.mctiernan@verizon.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
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?
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@megazone23.bigpanda.com>
To: "Kelly McTiernan" <kelly.mctiernan@verizon.net>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, February 25, 2002 8:20 AM
Subject: Re: [GENERAL] Work Around For Oracle Feature
Show quoted text
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?