Subquery on multiple columns

building reports webintelligence
1

Hi Experts,

Don’t know if you have encountered performance issue about this topic before. Here is the example when I created a subquery on Customer and Product

SQL:
Select …
From …
Where Customer + ’ ’ + Product in ( Select … )

Webi generates where statement by concatenating Customer and Product INTELLIGENTLY. It gave me very poor query performance as it will not hit indexes and kill my temp storage in database

Is it possible to change the statement into (Customer, Product) ?

Any input will be helpful. Thanks in advance

ohtiek :hong_kong: (BOB member since 2006-09-03)

2

can you continue the example? what’s in the subquery?

ww55d (BOB member since 2011-06-14)

3

Some databases support this syntax:

select stuff
from tables
where (col1, col2) in
  (select col 1, col2
   from tables)

Web Intelligence does not currently support that format, which is why they concatenate the two columns. You can try creating the condition you need by using predefined conditions (filter objects) in the universe.

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