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.
