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
Original Message:
Sent: 07-01-2020 11:56 AM
From: Rakesh Reddy
Subject: Pulling Combinations of values from different Multi Valued Lookups in a Query
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.
------------------------------