SubQueries

building reports desktopintelligence
1

Jason,

I’m not sure if this is what you need but here’s what we do:

Select 1

Where
GL_BALANCES.PERIOD_NAME||’’ in (
select z.PERIOD_NAME
from GL_PERIODS z
where z.PERIOD_SET_NAME =‘FOREMOST FARMS’ and z.PERIOD_NUM = @prompt(‘Month’,‘N’,‘Periods\Period Number’,) and (z.PERIOD_YEAR = @prompt(‘Year’,‘A’,‘Periods\Period Year’,)
or z.PERIOD_YEAR =
to_char(to_number(@prompt(‘Year’,‘A’,‘Periods\Period Year’,)) -1)))

Then, we set this object name = 1 in conditions.

Julie

You Wrote:

is it possible with Business Objects to create an in-line subquery (I believe
they are also called derived tables) such as the following without using free
hand SQL:

SELECT
z.some_column
,sum(b.some_column)
FROM
(
SELECT
a.some_column
FROM
some_table a
WHERE
a.some_column = some_condition
) z
,another_table b
WHERE
z.some_column = b.some_column
GROUP BY
z.some_column

Listserv Archives (BOB member since 2002-06-25)

2

Today’s dose of brain failure…

I am struggling with creating a simple sub-query in BusObj that is easy in SQL.

First, the very simple (part) table structure

TableA.ColumnA-------TableB.ColumnA
TableB.ColumnB-------TableC.ColumnB

In SQL what I want is:
Select ColumnA from TableA where ColumnA not in (select ColumnA from TableB)

ie basically selecting all the values in table A which don’t have a value B associated with them.

Now, in SQL this is simple because you’re selecting a single column from the ‘joining’ table (B). In BusObj you can’t do this because you don’t physically see anything from table B, you only see the valid values in tables A and C meaning that you have to include 2 objects in your sub-query, which of course you can’t do with an ‘In List’ operator.

What am I missing?

Regards

Jonathan

Project Leader
Global Medical, Regulatory and Product Strategy (GMRPS) IS

Listserv Archives (BOB member since 2002-06-25)

3

Hi,

I was having a similar related problem recently. I need to include an IN statement in the where clause and couldn’t find where to put it. In the where clause builder it doesn’t mention anything about sub selects…but you can just type them in and they work fine.

So you can set up your object to correspond to the column, and in the where clause include the sub query you want. Its a shame this isn’t documented. It got me stumped for a while.

btw. You’re probably already aware, but its possible to transform a sub-query of this form into a straight join.

Your SQL becomes :

select a.columnA from TableA a, TableB b where a.ColumnA = b.ColumnB

which acheives the same result. But I think in this case you can simply add in the clause you need in the Where box.

Let me know if you need any other help

L.

______________________________ Reply Separator _________________________________
Author: “Cirkel; Jonathan D” jc14547@GLAXOWELLCOME.CO.UK at DTCG.UK.INTERNET
Date: 30/07/98 13:16

Today’s dose of brain failure…

I am struggling with creating a simple sub-query in BusObj that is easy in SQL.

First, the very simple (part) table structure

TableA.ColumnA-------TableB.ColumnA
TableB.ColumnB-------TableC.ColumnB

In SQL what I want is:
Select ColumnA from TableA where ColumnA not in (select ColumnA from TableB)

ie basically selecting all the values in table A which don’t have a value B associated with them.

Now, in SQL this is simple because you’re selecting a single column from the ‘joining’ table (B). In BusObj you can’t do this because you don’t physically see anything from table B, you only see the valid values in tables A and C meaning that you have to include 2 objects in your sub-query, which of course you can’t do with an ‘In List’ operator.

What am I missing?

Regards

Jonathan

Project Leader
Global Medical, Regulatory and Product Strategy (GMRPS) IS

Listserv Archives (BOB member since 2002-06-25)

4

Jonathan D. Cirkel wrote:

I am struggling with creating a simple sub-query in BusObj that is easy in SQL.

TableA.ColumnA-------TableB.ColumnA
TableB.ColumnB-------TableC.ColumnB
In SQL what I want is:
Select ColumnA from TableA where ColumnA not in (select ColumnA from TableB)

What am I missing?

Unless you have two different objects representing ColumnA (one representing ColumnA in TableA and one representing ColumnA in TableB), your have a few (relatively compplex) shoices. One is to edit the SQL data provider by hand or use free-hand SQL. A second is two create a minus query selecting simply ColumnA from Table A, and then a minus clause and selecting ColumnA from table A PLUS a where condition saying a value from an object in table B is equal to the same object in table B. This will basically produce a list of all distinct values in TableA (query 1), all distinct values in TableA & B (query2) and take the difference and give it as a result

If you do have two objects for ColumnA, create a condition on TableA.ColumnA where it is not in list of a subquery of TableB.ColumnA. Note, however, that in general I do not recommend having two objects representing the same piece of information because of the end-user confusion it can cause.

FYI,

Bob Molby
GE Lighting

Listserv Archives (BOB member since 2002-06-25)

5

Select for the condition the ‘not in list’ operator and the ‘create a subquery’ operand. Then you create your subquery by simply selecting ColumnA form TableB.

Hope I don’t see things to simple

Peter

“Cirkel, Jonathan D” jc14547@GLAXOWELLCOME.CO.UK 30/07/98 14:16:58 >>>
Today’s dose of brain failure…

I am struggling with creating a simple sub-query in BusObj that is easy in SQL.

First, the very simple (part) table structure

TableA.ColumnA-------TableB.ColumnA
TableB.ColumnB-------TableC.ColumnB

In SQL what I want is:
Select ColumnA from TableA where ColumnA not in (select ColumnA from TableB)

Listserv Archives (BOB member since 2002-06-25)

6

Peter,

Hope I don’t see things to simple

I’m afraid you do!! Table B is not visible to users through the object definitions - objects are only based on the ‘look up list’ items not the foreign keys. This is why it’s simple in SQL and difficult in BusObj.

Regards

Jonathan

Project Leader
Global Medical, Regulatory and Product Strategy (GMRPS) IS

Listserv Archives (BOB member since 2002-06-25)