BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Launch Works 
Launch Works (Opens a new window)  

General Notice: No events within the next 45 days.

SubQueries


 
Search this topic... | Search Desktop Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Desktop Intelligence  Previous TopicPrint TopicNext Topic
Author Message
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Tue Jun 02, 1998 1:20 pm 
Post subject: SubQueries



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


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Thu Jul 30, 1998 7:16 am 
Post subject: Subqueries



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


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Thu Jul 30, 1998 7:23 am 
Post subject: Subqueries



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


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Thu Jul 30, 1998 7:54 am 
Post subject: Subqueries



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


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Fri Jul 31, 1998 4:26 am 
Post subject: Subqueries



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)


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Fri Jul 31, 1998 6:16 am 
Post subject: Subqueries



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


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Desktop Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0541 seconds using 17 queries. (SQL 0.0117 Parse 0.0346 Other 0.0078)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo