UNION and MINUS Operators

Hi all,

I am trying to amend some DPs by UNIONing the query with another within the DP, (I cant write two queries and link at the report level as the queries have measures which cant be dimensions, so I need to use the union operator) however, (and heres the good bit…) one of the queries has a MINUS operator within it… does anyone know how I can construct the query so that it does

(A -B) U (C-D),

at the moment it appears to do…

(A-(B U (C-D)))

I know that I could use the ‘Do Not Generate’ button, but I am reluctant to do as this will only cause problems in the future…

I have carried out some searches for a post like this but it appears that all cases in the past only have three queries and the responses have always been to reorder the set operators till the desired outcome is achieved, but in this case …!!!

I hope someone can help - I’m getting brain-ache!!! :blue: :cookie: :crazy_face: !

Thanks in advance…


snakeboy :uk: (BOB member since 2003-12-02)

Hi,

See if this helps u!!

Create 1 dataprovider with A-B
Create 2nd dataprovider with C-D

Create 3rd dataprovider which is a VBA Procedure and add the contents of dataprovider1 and dataprovider2 to it.

The VBA Procedure should look like this:


Dim col As DpVBAColumn
Set newCube = dpInterface.DpVBACubes.Item(1) 'there is only one cube
Set Cols = newCube.DpVBAColumns 'reference the columns of the cube
Cols.SetNbColumns (1) 'how many columns in the cube?

Set col = Cols.Item(1) 'look at the first column in the cube
col.Name = "ResortName" 'the name that appears in the report
col.Type = boCharacterObject 'what sort of information is it?
col.Qualification = boDimension 'dimension, detail or measure?
For i = 1 To 1 'for each row (item)
col.Item(i) = i 'set the value to a number
Next i
'set the column variable to the first column of dataprovider1 and add it 
'to  VBA procedure dataprovider

Dim coldp As Column
Set coldp = ThisDocument.DataProviders.Item(1).Columns.Item(1)

For i = 1 To coldp.Count
    col.Item(i) = coldp.Item(i)
Next i

'set the column variable to the first column of dataprovider2 and add it 
'to  VBA procedure dataprovider

Set coldp = ThisDocument.DataProviders.Item(1).Columns.Item(2)

For j = 1 To coldp.Count
    col.Item(i) = coldp.Item(j)
    i = i + 1
Next j

It is explained for 1 column. Similarly u can do this to access more columns in the dataproviders 1 & 2. Set the number of columns in the VBA PRocedure to the number of columns u are going to access.

You will see the common data from dataproviders 1 and 2 to be repeated in the microcube but when u take the objects from the microcube onto the report it will be seen only once.

I tested this with the sample beach.unv and it looks like it will satisfy your requirement…

Regards,
Ashish A.


Ashish Agarwal (BOB member since 2004-02-12)

Thanks Ashish,

The only problem though is that I have no idea how to use VBA!

Do you, or anyone else, have any ideas on how to solve this using ‘normal’ BOb procedures?

Thanks again in advance,

S.


snakeboy :uk: (BOB member since 2003-12-02)

Have you tested the query?

There should be an “order of operations” applied to the union and minus operations. In my testing, MINUS operations were performed first, followed by the UNION. So A - B U C - D would be evaluated as (A - B) U (C - D) which is, I think, what you were asking.

If I go back in and (with my test data) add the parens so that I am actually requesting (A - (B U (C - D)) I get a completely different answer.

This is on Oracle 9.


Dave Rathbun :us: (BOB member since 2002-06-06)

Hi Dave,

We are using Oracle 8i, with Bo 5.1.3. And i deduced that it worked as (A -(B U (C-D))) from looking into the sql generated by Bob ie the Bob imposed parens… and yes - testing it gave different results - loads more cases when i manully put the parens in the correct place…


snakeboy :uk: (BOB member since 2003-12-02)