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 <[email protected]>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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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] >>>>>>>> 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 [email protected] >>>>> 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 [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 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 >> [email protected]. >> >> 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 [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. > -- -- 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.
