Make NOT EXISTS clause in Webi

Question

I have an Oracle query that I need to rewrite in WebI. I cannot figure out how to mimic the NOT EXISTS from Oracle. My query returns an object if it has a transaction date within a given time frame, but not if it has anything prior to that time frame. That is, something like:

SELECT Object_Number 
     , Transaction_Date 
     , <other fields>
FROM Object_Table 
INNER JOIN Transaction_Table   ON  Transaction_Table.Key = Object_Table.Key
WHERE Transaction_Table.Date BETWEEN '2017-07-01' AND '2017-01-31' 
AND NOT EXISTS (SELECT 1 FROM Transaction_Table  
                WHERE Transaction_Table.Key = Object_table.Key 
                AND   Transaction_Table.Date < '2017-07-01' 
               ) 

It's a bit more complex, but that is a good approximation.

I thought I could use a subquery filter, but I don't see how to tie that subquery to the original Object_Number or how to mimic that NOT EXISTS. I don't think a NOT IN is the same as NOT EXISTS because the record complex in the universe includes the transaction_date (it is basically a JOIN of the Object_Table and the Transaction_Table), so all I'd be checking is that the date that I have on my current record (a date inside the range) is not outside the range. I'm sure that this filter subquery can do more, but that I am not understanding it.

I cannot find any complex filter examples (despite many that claim to be "complex" - they have 3 "AND" statements - oooohhhh!).

I think it can be accomplished with a new dimension in the universe [using MIN(Transaction_Date)], but that path is not available to me.

It seems to me that without this type of basic functionality (at least it's basic in Oracle SQL), this WebI tool is severely limited.

Answer 1

You are correct that NOT EXISTS is not identical to NOT IN, but in your sample code, either one would work. The query could be rewritten with NOT IN as so:

SELECT Object_Number 
     , Transaction_Date 
     , <other fields>
FROM Object_Table 
INNER JOIN Transaction_Table   ON  Transaction_Table.Key = Object_Table.Key
WHERE Transaction_Table.Date BETWEEN '2017-07-01' AND '2017-01-31' 
      AND object_table.key NOT IN 
          (SELECT transaction_table.key
             FROM transaction_table
            WHERE transaction_table.date < '2017-07-01'

And replicating this logic in WebI can be done with a subquery. I used my universe for the following screenshot, so my Session Id, Event Id, and Event Date Local correspond with your object_table.key, transaction_table.key, and transaction_table.date.

If the necessary logic does in fact require a NOT EXISTS, then than cannot be done with a standard subquery in WebI. However, you can cheat by creating a predefined condition in the universe to implement the logic. The condition would contain the entire NOT EXISTS clause from your query; dropping this into a query in WebI would add the clause to the SQL.