On Sunday, September 29, 2013 12:19:11 PM UTC-6, Michael Moore wrote: > > SQL> DROP TABLE syn > Table dropped. > SQL> CREATE TABLE syn > ( > val VARCHAR2 (30), > lik VARCHAR2 (30) > ) > Table created. > SQL> INSERT ALL > INTO syn > VALUES ('red', 'red') > INTO syn > VALUES ('red', 'rouge') > INTO syn > VALUES ('red', 'crimson') > INTO syn > VALUES ('car', 'car') > INTO syn > VALUES ('car', 'motor') > INTO syn > VALUES ('car', 'vehicle') > SELECT 1 FROM DUAL > 6 rows created. > SQL> COMMIT > Commit complete. > SQL> SELECT x.lik, y.lik > FROM syn x CROSS JOIN syn y > WHERE x.val = 'red' AND y.val = 'car' > > LIK LIK_1 > ------------------------------ ------------------------------ > red car > red motor > red vehicle > rouge car > rouge motor > rouge vehicle > crimson car > crimson motor > crimson vehicle > > 9 rows selected. > > > On Sun, Sep 29, 2013 at 3:51 AM, Phil W <philwi...@gmail.com <javascript:> > > wrote: > >> Given a phase, and a table of "like" words, essentially a thesaurus, I'd >> like to generate all permutations of the phrase >> making use of the thesaurus entries. >> >> I am sure it is possible in an SQL statement as I'd like to avoid PL/SQL >> if possible but I am thinking now that a function/pipelined function >> might be my only option. Does anyone with better SQL than me know how this >> can be done please? >> >> create table word_syn >> (value varchar2(30), >> likeness varchar2(30)) >> / >> >> insert all >> into word_syn values ('red','rouge') >> into word_syn values ('red','crimson') >> into word_syn values ('car','motor') >> into word_syn values ('car','vehicle') >> select 1 from dual >> / >> >> >> /* Find options for "big red car" >> Desired output is: >> big rouge car >> big rouge motor >> big rouge vehicle >> big red motor >> big crimson motor... and so on. >> */ >> >> >> -- >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to oracle...@googlegroups.com<javascript:> >> To unsubscribe from this group, send email to >> oracle-plsql...@googlegroups.com <javascript:> >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en >> >> --- >> You received this message because you are subscribed to the Google Groups >> "Oracle PL/SQL" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to oracle-plsql...@googlegroups.com <javascript:>. >> For more options, visit https://groups.google.com/groups/opt_out. >> > > Taking this one step further: SQL> select z.likeness, alike, blike 2 from (select a1.likeness alike, b1.likeness blike from word_syn a1 cross join word_syn b1 3 where b1.value = 'car' and a1.value='red') cross join word_syn z 4 where z.value = 'big' 5 / LIKENESS ALIKE BLIKE ------------------------------ ------------------------------ ------------------------------ big red car huge red car large red car gigantic red car grand red car humongous red car big red motor huge red motor large red motor gigantic red motor grand red motor LIKENESS ALIKE BLIKE ------------------------------ ------------------------------ ------------------------------ humongous red motor big red vehicle huge red vehicle large red vehicle gigantic red vehicle grand red vehicle humongous red vehicle big rouge car huge rouge car large rouge car gigantic rouge car LIKENESS ALIKE BLIKE ------------------------------ ------------------------------ ------------------------------ grand rouge car humongous rouge car big rouge motor huge rouge motor large rouge motor gigantic rouge motor grand rouge motor humongous rouge motor big rouge vehicle huge rouge vehicle large rouge vehicle LIKENESS ALIKE BLIKE ------------------------------ ------------------------------ ------------------------------ gigantic rouge vehicle grand rouge vehicle humongous rouge vehicle big crimson car huge crimson car large crimson car gigantic crimson car grand crimson car humongous crimson car big crimson motor huge crimson motor LIKENESS ALIKE BLIKE ------------------------------ ------------------------------ ------------------------------ large crimson motor gigantic crimson motor grand crimson motor humongous crimson motor big crimson vehicle huge crimson vehicle large crimson vehicle gigantic crimson vehicle grand crimson vehicle humongous crimson vehicle 54 rows selected. SQL> David Fitzjarrell
-- -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en --- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To unsubscribe from this group and stop receiving emails from it, send an email to oracle-plsql+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.