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 <[email protected] <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 [email protected]<javascript:>
>> To unsubscribe from this group, send email to
>> [email protected] <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 [email protected] <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 [email protected]
To unsubscribe from this group, send email to
[email protected]
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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
