Topic
  • 2 replies
  • Latest Post - ‏2011-07-04T23:37:47Z by mgibson
mgibson
mgibson
592 Posts
ACCEPTED ANSWER

Pinned topic Usage of "Not Exists" in Report Studio...

‏2011-06-30T03:36:01Z |
Hi,

I'm wishing to employ some "Not Exists" logic in a Report Studio report, and I'm looking for opinions on the best approach.

I know similar matters have been discussed in the forum in other threads, but I'm looking for some more practical advice than what I've already read, and something that operates most efficiently (because the datasets are large).

And please keep in mind that it has to be an RS based solution, I do not wish to include it in the FM model.

If we consider the simplified example:

Select *                                                                
From TABLE1                                                             
Where NOT EXISTS (                                                      
     Select 'x'                                                         
     from TABLE2                                                        
     where TABLE1.KEY = TABLE2.KEY                                      
     and TABLE2.DATE < ')


I've used one or two methods, but I'm wondering what advice would people give for doing the same in RS?

Regards
Mick
Updated on 2014-03-25T08:04:42Z at 2014-03-25T08:04:42Z by iron-man
  • SystemAdmin
    SystemAdmin
    15496 Posts
    ACCEPTED ANSWER

    Re: Usage of "Not Exists" in Report Studio...

    ‏2011-06-30T04:13:05Z   in response to mgibson
    One option (although a poor one for maintenance) is to create your own custom SQL statement using the exists or not exists clause. If you want to work within the capabilities of Report Studio then you will have to look at using joins to identify the records you need. You might approach this in the following way

    Query1:
    Table2.KEY
    Filter Table2.DATE < Something

    Query2:
    Table1.KEY

    Query3:
    Except operation between Query2 and Query1 to give you a list of keys from Table1 that do not exist in the query from Table2

    Query4:
    Table1.*

    Query5:
    Join Query3 and Query4 on the key to get only the records that pass the except operation from Query3.

    If the keys and the date field are indexed then I would expect the database to be able to do Query1 to Query3 (and then possibly the join to Query5) using only index lookups.
    • mgibson
      mgibson
      592 Posts
      ACCEPTED ANSWER

      Re: Usage of "Not Exists" in Report Studio...

      ‏2011-07-04T23:37:47Z   in response to SystemAdmin
      Hi Phil,

      I finally got back to this issue, and have resolved it. In all honesty I really didn't want to use the 'Except' operator option, so I resorted to an old method I'd used before - which in this case actually does operate quickly.

      I thought I'd post it for the benefit of others...
      In short, I joined the 2 queries (i.e. the main query is outer joined to the sub-query, the sub-query being the one which would normally serve as the not-exists sub-query), and on the resultant query, I included a columns from the sub-query and also included a filter where that item IS NULL.

      I was hoping for a neater option, but it provides the correct result.

      Regards
      Mick