Minus query-help needed

building reports webintelligence
1

Hi All,

I have to report rows in table A which are not available in table B.
ie)I need to display table A.num, table A.name , table A.desc where
select table A.num minus table B. num

Pls note no columns or values from table B are needed.

I’m able to get the right number of records(ie the count) by doing A.num minus table B. num . But how to select the table A.num, table A. name , table A.desc for those selected table A.num rows?

Thanks.

meenaj (BOB member since 2005-01-12)

2

Hi,

You can’t do it with a simple MINUS query. You need to use a subquery of this form:

select A.num, A.name, A.desc
  from table A
 where A.num IN (select A.num
                   from table A
                 minus
                 select B.num
                   from table B
                )

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Exactly. But how to implement it in WebI?
Ie) how to get a select over the minus query.

Also noticed the minus queries are real slow. Is there a way to simulate the minus query.

Thanks in advance

meenaj (BOB member since 2005-01-12)

4

I don’t think you can do it in WebI without anything being done in the universe beforehand.

Another way how to get rows from the table A that have no appropriate records in the table B is to use a subquery of this type:

select A.num, A.name, A.desc
  from table A
 where not exists (select 1
                     from table B
                    where B.num = A.num
                  )

Test both cases which one is faster in your case. The performance may depend on the number of rows in both tables and some other factors. So test both cases with your real tables A and B.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

Thanks Marek.

meenaj (BOB member since 2005-01-12)

6

Hi Meenaj,

I am suspecting Marek Chladny query also will take time. Basically exist operator is more fast but i am thinking in diff way like if TableA have 1000 record and TableB have 50 Record then it will go 1000 times in the loop so can u check this below query and let us know which one is very fast.

 [color=darkblue]select num, name, desc 
  from tableA 
where not in (select num
                     from tableB   [/color]                
                  )

bopradeep :india: (BOB member since 2009-08-03)

7

Hi,

Generally,

where not exists

performs better than

where not in

So test in your real data which one is better in terms of performance.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

8

Thanks again to both.
A quick question.
In WebI how to implement the not exists clause

meenaj (BOB member since 2005-01-12)

9

Custom SQL

rimpa :india: (BOB member since 2008-04-14)

10

Hi Mark,

I know

 Basically exists operator is more fast

But i am suspecting like if master table have more data then search time also be more.

Meena,

can u check both a query in your Bo and let us know which one is better?

bopradeep :india: (BOB member since 2009-08-03)