Clarity

 View Only
  • 1.  Pulling Combinations of values from different Multi Valued Lookups in a Query

    Posted Jul 01, 2020 11:57 AM

    Hi there,

    I have a requirement where I  need to create different instances in a subobject based on the values of 2 mutivalued and 1 single valued lookups in a Master Object. 

    Example:
    Let us consider a Master Object named MASTOBJ.It has 3 attributes in which 2 are multivalued Lookups and 1 is Single Valued lookup.
    Let MVL1,MVL2 be multivalued lookups and SVL isa single valued Lookup.

    If I select MVL1=a,b  (2 values)
                  MVL2=c,d,e (3 values)
                    SVL=x   (single value as it is single valued lookup) 

    Now,I have to run a process which creates 2*3*1(MVL1*MVL2*SVL)=6 Instances in a subobject SUBOBJ under MASTOBJ with values as

    1. a,c,x  (3 attributes are string data type)
    2.a,d,x
    3.a,e,x
    4.b,c,x
    5.b,d,x
    6.b,e,x

    Could you please help me write a query for making these combinations.
    Thanks in Advance..!!




    ------------------------------
    Warm Regards,
    Rakesh Reddy.
    ------------------------------


  • 2.  RE: Pulling Combinations of values from different Multi Valued Lookups in a Query

    Posted Jul 01, 2020 12:16 PM
    Multi valued lookup contents are held in the odf_multi_valued_lookups table, so you would be joining your instance against two copies of that table and one copy of the normal lookup table


  • 3.  RE: Pulling Combinations of values from different Multi Valued Lookups in a Query
    Best Answer

    Posted Jul 03, 2020 10:45 AM
    Hi Rakesh. You can you a cross join. see the example below
    SELECT t1.Value,t2.value2 ,t3.value3 FROM
    (SELECT value FROM (
    SELECT 'a' value FROM dual
    UNION
    SELECT 'b' value FROM dual
    ) ) t1
    CROSS JOIN
    (SELECT value2 FROM (
    SELECT 'c' value2 FROM dual
    UNION
    SELECT 'd' value2 FROM dual
    UNION
    SELECT 'e' value FROM dual
    )
    ) t2
    CROSS JOIN
    (SELECT value3 FROM (
    SELECT 'x' value3 FROM dual
    )
    ) t3

    I used select select from dual but you can substirute with appropriate select from odf_multi_valued_lookups ...

    Stefano