No, I don't think so unless you get into dynamic sql. You might want to
abandon the cross join and look into the powermultiset function. I haven't
worked it out yet, but that's where I'd be looking.
On Mon, Sep 30, 2013 at 1:58 PM, Phil W <philwinfi...@gmail.com> wrote:
> Michael,
>
> Sorry, I mean split into phrases with the cross join just operating at the
> 'like word' level as follows
>
> car was red and next to a crimson motor
> motor was red and next to a crimson motor
> vehicle was red and next to a crimson motor
> car was rouge and next to a crimson motor
> car *was* crimson *and next to a* crimson motor
> ... and so on...
>
> Do you think this would be possible so that the output could effectively
> cross join x times on the underlined (or any defined equivalent word) to
> provide all equivalent phrases?
>
> I think it is quite a challenge to do this elegantly!
>
> Regards
>
> Phil
>
>
>
>
> On Monday, 30 September 2013 21:37:27 UTC+1, Michael Moore wrote:
>
>> Phil,
>> you can do it with SQL, but it gets kind of ugly.
>>
>> SELECT EXTRACTVALUE (VALUE (d), '/g') AS rslt
>> FROM TABLE
>> (XMLSEQUENCE
>> (EXTRACT
>> (XMLTYPE.createxml ( '<row><g>'
>> || REPLACE
>> ('car was red and next to
>> a crimson motor',
>> ' ',
>> '</g><g>'
>> )
>> || '</g></row>'
>> ),
>> '/row/g'
>> )
>> )
>> ) d;
>>
>>
>> RSLT
>>
>> ------------------------------**------------------------------**
>> --------------------
>> car
>>
>> was
>>
>> red
>>
>> and
>>
>> next
>>
>> to
>>
>> a
>>
>> crimson
>>
>> motor
>>
>>
>> 9 rows selected.
>> If this is something going into a production environment, I would use
>> PL/SQL, simply for debugging reasons.
>> Mike
>>
>>
>>
>> On Mon, Sep 30, 2013 at 1:19 PM, Phil W <philwi...@gmail.com> wrote:
>>
>>> David,
>>>
>>> This is spot on and where I'd almost arrived at but what I'm ideally
>>> looking for is to return a set of results from an input string of several
>>> words. Unfortunately, I won't know if there are 2, 3 or 8 words and some
>>> will not have a likeness to worry about.
>>>
>>> For example: 'car was red and next to a crimson motor'
>>>
>>> I'm really wondering if SQL alone can do this and will probably have to
>>> try and write a function. The function will have to dynamically split the
>>> phrase up into sections to cater for the like words and then cross join the
>>> whole thing together.
>>>
>>> Thanks v much for your help so far!
>>>
>>> Phil
>>>
>>>
>>> On Monday, 30 September 2013 16:19:37 UTC+1, ddf wrote:
>>>>
>>>>
>>>> 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> 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
>>>>>> To unsubscribe from this group, send email to
>>>>>> Oracle-PLSQL...@googlegroups.**c**om
>>>>>> For more options, visit this group at
>>>>>> http://groups.google.com/**group**/Oracle-PLSQL?hl=en<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.**c**om.
>>>>>> For more options, visit
>>>>>> https://groups.google.com/**grou**ps/opt_out<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...@googlegroups.com
>>> To unsubscribe from this group, send email to
>>> Oracle-PLSQL...@**googlegroups.com
>>> For more options, visit this group at
>>> http://groups.google.com/**group/Oracle-PLSQL?hl=en<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.
>>> For more options, visit
>>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out>
>>> .
>>>
>>
>> --
> --
> 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.
>
--
--
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.
