Re: [U2] What is the equivalent to UNNEST in Universe
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 To: U2-Users 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
Re: [U2] What is the equivalent to UNNEST in Universe
Does UniVerse have ASD( ... ) keyword? That's what you would use in UniData. David A. Green (480) 813-1725 DAG Consulting -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Adrian Halid Sent: Tuesday, August 20, 2013 4:39 PM To: U2-Users@listserver.u2ug.org 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
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 To: U2-Users 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
Re: [U2] What is the equivalent to UNNEST in Universe
Hi Adrian, I believe BY.EXP is what you're looking for. HTH. Regards, Brian. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Adrian Halid Sent: Wednesday, 21 August 2013 9:39 AM To: U2-Users@listserver.u2ug.org 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] 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
