Does it work if you include B as a likeness of B and C as a likeness of C?
I wish I had time to think more on this. This is a great puzzle, but my
darn job keeps getting in my way.
On Tue, Oct 1, 2013 at 10:09 AM, Phil Winfield <philwinfi...@gmail.com>wrote:
> I'm not convinced. POWERMULTISET provides me with an output of all
> outcomes from a set, what I want is different:
> I provide A B C as my input, but B has likeness options of J, K and L and
> C has likeness of X and Y.
>
> In this scenario, if I just want the possible combinations (and order is
> not a problem) I'm not looking at a multiset issue as far as I can see?
>
> I'd need to swap out B for J first, then K and L. Then B & C for J and X,
> then J and Y and so on. The multiset option allows me to specify a set and
> return the results in all permutations only.
>
> *Results*
> A B C
> A J C
> A K C
> A L C
> A J X
> A J Y
> A K X
> A K Y
> A L X
> A L Y
>
> So, procedurally, I will code the above and return it pipelined. I was
> just hoping something like multiset would be available but in this
> situation it does not look possible.
>
> Thank you all for the time to suggest options.
>
> Regards
>
> Phil
>
>
>
> On 1 October 2013 17:48, ddf <orat...@msn.com> wrote:
>
>>
>> On Monday, September 30, 2013 2:58:07 PM UTC-6, Phil W 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>
>>>>> .
>>>>>
>>>>
>>>>
>> It may be that you cannot do this elegantly (or as elegantly as you might
>> desire). I would take Michael's advice and pursue this using
>> powermultiset functionality.
>>
>>
>> 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 a topic in the
>> Google Groups "Oracle PL/SQL" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/oracle-plsql/KEd0BoKeonw/unsubscribe.
>> To unsubscribe from this group and all its topics, 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.
>
--
--
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.
