counting and comparing a split string 2

[jad]

i currently have a procedure that phonetically compares strings.

we have a contacts database and some of our sales staff are morons and don't bother looking to see if a company already exists before trying to add the company to the system.

i'm using Oracle 8.0.5

to get around this when someone enters a company name in the creation box (web forms) i split the string up, remove padding words 'the', 'and', 'ltd', 'limited', etc., and soundex each part before comparing.

i also create an acronym after the noise is removed to compare.

if a new company is created i store an soundex string as a variable in the table, as one string of concatenated soundex strings.

at the moment to compare these strings i store each individual soundex i create in a a table called 'temp_storage_table', run a select query with a join on this table, then delete from the 'temp_storage_table'

this gives me a count of all possible matches on the individual words in the company name, the higher the count the closer it matches.

i would like to remove the need for temp_storage_table, and run it as dynamic SQL or similar if possible.

is there any way that in dynamic sql you can have a table that exists only for the lifetime of the sql ... or produce a select that will return as many rows as i want from dual?

can i have a query that does something like:

select count(pieces.piece_id)
    from companies, (select piece_id from ('S234','N120','P235'))
    where companies.phonetic like '%' || piece_id || '%';

that you're aware of?

Thanks

 

[jee]

Sounds like what you want is what Oracle calls a temporary table. You set this up like any other table, only use this syntax:

CREATE GLOBAL TEMPORARY TABLE current_inventory

This creates a table that has the following characteristics:

1) Data stored is only accessible by the "current" user. So multiple users can be using the table but they only see the rows that they inserted themselves.
2) Data is deleted automatically (when you set up the table you choose whether this is when a commit is issued - via ON COMMIT DELETE ROWS at the end of the create table statement or when the user's session ends - default).

There are some restrictions on temporary tables - like they can't have foreign keys - for details see the Oracle SQL Reference Manual. There is also information in the Oracle 8i Concepts manual.

 

[jad]

does Oracle 8.0.5 do that? i thought temporary tables were 8i only ...

 

[jee]

Sorry jad - I failed to notice the reference to 8.0.5 in your first message. You are correct - temporary tables first appeared sometime after 8.0.5 (a reason to upgrade??)

As a suggestion for doing the thing with dynamic SQL... Try building something like this:

SELECT COUNT (p.piece_id)
FROM companies c, (
SELECT 'S234' piece_id
FROM dual
UNION
SELECT 'N120' piece_id
FROM dual
UNION
SELECT 'P235' piece_id
FROM dual) pieces
WHERE c.phonetic LIKE '%' || p.piece_id || '%';

 

[jad]

we're looking to upgrade ... got the bosses wrangling for a good price ... ?

but at the moment i'm still stuck in 8.0.5

i hadn't thought of union ... that'd work

thankyou