Brio "unmatched" Query?

[tony]

Hai guys!

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! :/

Thanks guys!

Tony

[ecki]

Hi Tony,

The best way would be to put the logic in to view.

cheers,

e.

[tony]

Hmmm... can't upload graphics...

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));

...but in Brio....

Any ideas...?

Thanks!

Tony

[tony]

I *think* I have a workaround - but a bit messy...

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

Tony

[cavagnaro]

Giac!

[tony]

No idea what that means cav, but.... 



T


[quote author=cavagnaro link=topic=5470.msg23830#msg23830 date=1271177950]
Giac!
[/quote]

[cavagnaro]

[quote author=Tony Tillyer link=topic=5470.msg23837#msg23837 date=1271239655]
No idea what that means cav, but.... 



T


[quote author=cavagnaro link=topic=5470.msg23830#msg23830 date=1271177950]
Giac!
[/quote]
[/quote]

puaj, ew, agh!

[tony]

u no like solution..?

[cavagnaro]

I do like the fact that you solved the issue in a very challenging way, I don't like why Brio has to be so complex for "easy" tasks

[tony]

True - it can't do a simple "unmatched" query....

T

[cavagnaro]

In general managing SQL is a hell in Brio...I'm already looking for some alternative to it

[Fra]

Brio? I associate this word with 'feeling sick' ! 

Fra

[tony]

Not much different in Hyperion... But it comes "free" with Routing so it's no wonder there are a lot of customized reports out there... lol

T

[cavagnaro]

Yes, I'm looking for a solution also free to replace that headache...

[tony]

Jasper...?