Hello, I'd appreciate some help regarding user-written base code. Consider the following example input table, which is filtered on ORDER_NO=4687: ORDER_NO ORDER_SEQ_NO ORDER_STATUS COMMENT DATE 4687 1 OPP 15SEP2011 4687 2 FIN 27SEP2011 4687 3 AB 05OCT2011 4687 4 FIN 06OCT2011 4687 5 FIN Hello world! 27JAN2014 Each time a change is made to an order, a new row is triggered (the one with the highest ORDER_SEQ_NO being the most recent). This ideally only happens when the order status changes. However, if a consultant manually edits the order (here by adding a comment), another row is triggered despite the status not having changed. So far, my job has always selected the date from the most recent row (27JAN2014), i.e. from the row with the highest ORDER_SEQ_NO. However, I need it to select the first date for the most recently triggered order status. So, in the example above, the logic should select the date 06OCT2011. Not 27SEP2011, as there's been a different status since back then. I can't simply group by the most recent ORDER_STATUS and select min(DATE), as that would select the date from row 2. I need row 4 from this example, and of course I can't just select ORDER_SEQ_NO=4 directly since the logic must apply to numerous other orders as well. ... View more

Hello, At the right-hand side of most transformations in DI Studio there's a work table. When I run a transformation (in DI Studio 4.21) and open the work table, with "create as view" disabled, the table opens almost immediately, and the total number of rows is displayed on the header. However we've recently installed DI Studio 4.7, and when I try to do the same there, the work table takes forever to load. It basically just loads the rows I can see, and I have to keep scrolling down to the last row for the whole table to be loaded... and only then does the total number of rows display in the header. For example, if a table has 30 000 000 rows, I'd have to manually scroll through all those rows in order for the total number of rows to be registered on the header. This is veeery impractical when troubleshooting. How can I make DI Studio 4.7 load the work table straight away, and display the total number of rows straight away, like 4.21 did? ... View more

Data Integration Studio Imagine that you have an SQL Join with the two following input tables: INPUT 1: SOFTWARE_ID VERSION_ID STATUS_CODE 1234 100 AAA 1234 101 AAE 1234 102 ABB 1234 103 ABF INPUT 2: SOFTWARE_ID NUMERIC_VALUE 1234 1250,25 The SQL Join uses INNER JOIN on input1.SOFTWARE_ID=input2.SOFTWARE_ID In the output table, the expression used for NUMERIC_VALUE is: case   when input1.VERSION_ID = max(input1.VERSION_ID) then input2.NUMERIC_VALUE   else 0 end This results in the following output table: SOFTWARE_ID VERSION_ID STATUS_CODE NUMERIC_VALUE 1234 100 AAA 0 1234 101 AAE 0 1234 102 ABB 0 1234 103 ABF 1250,25 MY PROBLEM: I want to make sure that for each SOFTWARE_ID (there's many more than in this simplified example), the NUMERIC_VALUE is placed on the max VERSION_ID that has STATUS_CODE either AAA or AAE. Basically something like this (if this worked): case   when input1.VERSION_ID = max(input1.VERSION_ID where input1.STATUS_CODE in ("AAA","AAE")) then input2.NUMERIC_VALUE   else 0 end I want the output to be like this: SOFTWARE_ID VERSION_ID STATUS_CODE NUMERIC_VALUE 1234 100 AAA 0 1234 101 AAE 1250,25 1234 102 ABB 0 1234 103 ABF 0 I'd greatly appreciate advice on how to modify the SQL JOIN expression used for NUMERIC_VALUE. Thanks so much for your time. ... View more