Problem with Subquery

system · July 24, 1998, 7:58pm

Hi,
I am having problems with creating subqueries through query panel. I specifically would like to use the query panel b’cos of the flexibility that the users will get for making changes. The SQL is as follows
SELECT
BLD.SALES_CITY,
CMP.COMPANY_CITY,
BLD.CO_CLLI,
SRV.NPA.NXX
FROM
BUILDING BLD,
COMPANY CMP,
SERVICING_WIRE_NPA SRV
WHERE
(BLD.SALES_CITY = CMP.SALES_CITY)
AND SRV.NPA_NXX =
(SELECT MIN(SERVICING_WIRE_NPA.NPA_NXX)
FROM SERVICING_WIRE_NPA
WHERE BLD.CO_CLLI = SUBSTRING(SERVICING_WIRE_NPA.SWC_CLLI,1,8))

;
The above query gives us results as a free hand sql. Our database sybase sql server. I need to get Building sales_city, company sales_city, minimum NPA_NXX in each co_clli.

If any one has any suggestions of how to desighn as well to create this query will be greatful.

Thanks In advance.

Radhika

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

system · July 24, 1998, 8:54pm

Hi:

You may try creating two objects

SUBSTRING(SERVICING_WIRE_NPA.SWC_CLLI,1,8) MIN(SERVICING_WIRE_NPA.NPA_NXX)

Create the first part of the query:
SELECT
BLD.SALES_CITY,
CMP.COMPANY_CITY,
BLD.CO_CLLI,
SRV.NPA.NXX

Then Drag the SRV.NPA_NXX to the conditions window. Select the Equal to operator.
Select the Create a Subquery Any operand.

In the subquery panel create
SELECT MIN(SERVICING_WIRE_NPA.NPA_NXX), with your newly created min object

Drag the BLD.CO_CLLI to the Conditions window. Select the Equal to operator.
Select the object operand.
Select your newly created SUBSTRING(SERVICING_WIRE_NPA.SWC_CLLI,1,8) object.

Should be about the same as your freehand query.

Robert

Schmidt Interactive Software, Inc.

Hi,
I am having problems with creating subqueries through query panel. I
specifically would like to use the query panel b’cos of the flexibility that the users will get for making changes.
The SQL is as follows
SELECT
BLD.SALES_CITY,
CMP.COMPANY_CITY,
BLD.CO_CLLI,
SRV.NPA.NXX
FROM
BUILDING BLD,
COMPANY CMP,
SERVICING_WIRE_NPA SRV
WHERE
(BLD.SALES_CITY = CMP.SALES_CITY)
AND SRV.NPA_NXX =
(SELECT MIN(SERVICING_WIRE_NPA.NPA_NXX)
FROM SERVICING_WIRE_NPA
WHERE BLD.CO_CLLI =
SUBSTRING(SERVICING_WIRE_NPA.SWC_CLLI,1,8))

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

system · July 27, 1998, 5:03am

Radhika,

It looks like what you need is a “correlated sub-query” using the “Calculation wizard.”

For example, if in your report you want to show the minimum SERVICING_WIRE_NPA.NPA_NXX for each BLD.CO_CLLI, then in your report…

Select your query Result Objects:
BLD.SALES_CITY,
CMP.COMPANY_CITY,
BLD.CO_CLLI,
SRV.NPA.NXX
Choose the object on which you want to apply the condition (NPA_NXX) and place it in the Conditions panel, select your operator (Equal to), and your Operand (Calculation). THE WIZARD BEGINS…
SELECT AN OBJECT: Select the calculation object which will be compared with the condition object. Most of the time these are the same objects (NPA_NXX). {Note: I left out the table qualifier on the NPA_NXX object because as long as you use the same defined object, BusinessObjects will take care of setting up the required aliases.}
SELECT A FUNCTION: Select the calculation to be performed (Min).
SELECT LEVEL OF CALCULATION: Select Globally if no group by is required in the nested query.
SYNCHRONIZE YOUR CALCULATION: Tie the nested query to the outer query by choosing the link object. (BLD.CO_CLLI). This is the “correlation” piece of a correlated sub-query.
SET THE NUMBER OF VALUES TO COMPARE: If you will only be comparing one value (which is true most of the time), choose either option here.

FINISH.

Hope this helps,
Good luck,
Luis Gonzalez

PS - Also check out the tech note, “BusinessObjects and Correlated Sub-queries”
at http://tech.busobj.com/technotes4/technotes4.htm

Hi,
I am having problems with creating subqueries through query panel. I specifically would like to use the query panel b’cos of the flexibility that the users will get for making changes. The SQL is as follows
SELECT
BLD.SALES_CITY,
CMP.COMPANY_CITY,
BLD.CO_CLLI,
SRV.NPA.NXX
FROM
BUILDING BLD,
COMPANY CMP,
SERVICING_WIRE_NPA SRV
WHERE
(BLD.SALES_CITY = CMP.SALES_CITY)
AND SRV.NPA_NXX =
(SELECT MIN(SERVICING_WIRE_NPA.NPA_NXX)
FROM SERVICING_WIRE_NPA
WHERE BLD.CO_CLLI =
SUBSTRING(SERVICING_WIRE_NPA.SWC_CLLI,1,8))

;
The above query gives us results as a free hand sql. Our database sybase sql server. I need to get Building sales_city, company sales_city, minimum NPA_NXX in each co_clli.

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

system · February 25, 2004, 6:07pm

Hi,

I am just wondering whether Radhika has solved her problem with corelated subquery. I am facing a similar problem while creating a report. I could create a report using free-hand sql. I am just wondering how can I refer an object from the main query in a conditoin using sub-query. My free-hand SQL is as follows:

SELECT
session_id,
rep_name
FROM
session_log sl,
report_log rl
WHERE
sl.session_id = rl.session_id
AND rl.event_type = ‘A’
AND NOT EXISTS
(
SELECT session_id
FROM report_log a
WHERE event_type = ‘B’
AND a.session_id = rl.session_id
AND a.rep_name = rl.rep_name
)

Please help me.

Regards

krs_sar (BOB member since 2004-02-25)

system · February 25, 2004, 6:17pm

Any chance of having the Designer create a predefined condition in the universe with:

AND NOT EXISTS 
( 
SELECT session_id 
FROM report_log a 
WHERE event_type = 'B' 
AND a.session_id = rl.session_id 
AND a.rep_name = rl.rep_name 
)

I never fully understood the complex calculation wizard and have yet to see a presentation that explains it in detail

Andreas (BOB member since 2002-06-20)

system · February 25, 2004, 7:46pm

If you have access to the tech support website, you can check out my presentation from last year’s User Conference. It’s called Using Subquery and Combined Query Techniques and there is a whole section in it on the calculation wizard and correlated queries. Not sure what version of Business Objects you are using, but there is an example on page 100 in the V5 User Guide. In V6, I think it is in the Data Analysis guide probably under Complex Conditions or complex calculations. The V6 explanation is more descriptive and shows you step-by-step what each dialog box means and what options to select.

Again, if you have access to the tech support website, you can open the V6 documentation is PDF format and see what it says, even if you haven’t installed v6 yet. As far as I can tell, the calculation wizard in full client hasn’t changed at all from v5.

If you can’t get at the conference presentation, send me some email and I’ll forward it to you.

Debbie Kelly (BOB member since 2002-08-15)

system · March 26, 2004, 8:17pm

Debbie,

I’m using BO to report against PeopleSoft. As you know, PS HRMS is all effective dated. How do I use the complex query calculation wizard to accomplish the same task when using PS’s Query Tool.

For example, I would like to select the latest record in the job table within a particular date range. The proper SQL is as follows:

But when I use the wizard, it returns this:

How would you generate the proper correlated subquery without having to manually edit it? We could always edit the SQL statement and prevent BO from generating its own statement before running but for maintenance practicality it would be nice if we can find a mechnanism to accomplish the historical data look up.

Thank you.

dbknight (BOB member since 2003-11-25)

system · August 26, 2004, 1:44pm

would it be possible to get a copy of this presentation either via e-mail or url? I don’t have access to the BO support site? My email is chris_chesebro@bose.com

thx

chese79 (BOB member since 2004-07-21)

system · March 13, 2007, 8:22am

Hi,

Would it be possible to share the presentation to me too? My email is suchada_d@yahoo.com.

Thank you very much.

Best regards,
Aey

Aey (BOB member since 2005-09-06)