I’m perplexed by something which just does not seem possible in Brio;
I have 2 Table results which I am using in a new Query (as Local Tables). The Query does not have an .oce associated, since the tables it uses are locally available. Anyway - what I have is a single row of data on each Table and I want to find out which entries from one Table are NOT in the other Table. It’s called an “unmatched” Query.
Problem is, if I use the built in JOIN Type (of “not equal”) it returns millions of rows, since it is comparing the first row of data with every row of data in the other Table, and so on…
What I would like it to return is a list of entries which are present in one Table - but not in the other. “Unmatched”.
I can do this in SQL>PL SQL, MSAccess, etc. but I need to create the same thing in Brio and, because these are Local Tables, I cannot use the majority of the tools that are normally available to Queries (“Return Unique Records”, "Return first 100 Results, etc.)
Has anyone managed to do this, before? Any pointers much appreciated - I’ve scoured the web and found some references over at the ITToolbox, but it doesn’t cover this particular scenario… and I thought it would be easy!
I wanted to achieve an unmatched result from 2 similar tables where only some entries are the same - a bit like this:
Select TABLE_001.NUMBER, TABLE_002.NUMBER Into TABLE_003
From TABLE_001 Left Join TABLE_002 On TABLE_001.NUMBER =
TABLE_003.NUMBER
Where (((TABLE_003.NUMBER) Is Null));
Create a Query (Query1) for all of the Values in Table 1 (the Full Table)
Create a Query (Query2) for all of the values in Table 2 (the Partial Table)
Create a Script to export all of the Values from Table 2, to an external .txt file (Table2.txt)
Update Query1 by applying a Limit on the Results, which uses the Operator “Not Equal” to the Values from Table2.txt, as a Loaded External File.
Create a script that runs an Export of Query2 and overwrites Table2.txt each time you open the document. After that, each time you run Query1, it will use the updated values as an unmatched Query and return the results required.
As a little furry rodent currently advertising cheap car insurance says; “Simples!” <squeak!>
I have a feeling that this will make no sense to anyone but me - but I am happy I found an answer - as complex as it may seem… lol
