Thanks guys. The keyword WHEN was exactly what I was looking for.
To pick up only those rows which match. I have always been comfortable using WITH and then performing post processing to figure out the records I needed. Using WHEN will save that time. Regards Adrian Halid -----Original Message----- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wjhonson Sent: Wednesday, 21 August 2013 8:12 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] What is the equivalent to UNNEST in Universe The BY.EXP command will Unnest, and provided that you have properly associated the fields, they will stay in that associated order. HOWEVER the WITH command will select ALL the multi-values if ANY of them match the WITH clause. If you only want to see those multivalues which match the clause you should use the WHEN command. So if you have six "rows" the WITH will pick all six if any of them match. The WHEN will only pick those rows which match -----Original Message----- From: Adrian Halid <adrian.ha...@itvision.com.au> To: U2-Users <U2-Users@listserver.u2ug.org> Sent: Tue, Aug 20, 2013 4:39 pm Subject: [U2] What is the equivalent to UNNEST in Universe Hi All, How do you perform a LIST or SELECT in Universe which will unnest or explode its Associated Multi Value Data before the query. For example. If I have a file called CUSTOMERS which has an association ORDERS. The DICT on CUSTOMERS have the following. A single value attribute called CUSTOMER.NAME. And then a set of a multi value attributes below. ORDER.DATE PRODUCT.KEY AMOUNT How to I perform a query to get all the records that have an ORDER.DATE > "20/08/2013" and PRODUCT.KEY = "ABC" and AMOUNT = "100" when comparing against the same multi value position. I know I can use a Universe SQL query below with the UNNEST keyword to get the data. SELECT CUSTOMER.NAME, ORDER.DATE, PRODUCT.KEY, AMOUNT FROM UNNEST ON ORDERS WHERE ORDER.DATE > '20/08/2013' AND PRODUCT.KEY = 'ABC' AND AMOUNT = '100'; How would you achieve the same result using a standard Universe LIST or SELECT command. Not a Universe SQL command. Regards Adrian Halid _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
