Joseph,
I am not fully clear about what you are doing but I remember encountering similar problem. instead of a subquery you can create a condition object in the designer itself as follows:
in the where clause window of the condition object say Cond_date just enter the following:
FORECAST.STAGE_DATE = ALL
(
SELECT MIN(fc_alias.STAGE_DATE)
FROM FORECAST fc_alias
WHERE fc_alias.ID = LEAD.ID AND
fc_alias.STAGE = ‘WON’
)
Parsing the object will fail because it will not find Lead.ID column because it is not in the from clause. Ignore the failed parse.
Go to the report query panel and drag the condition object Cond_date in the condition window of the query. Try it it should work but as I said I am not sure this is what you want. This will eliminate the sub query to be created by business objects.
Manoj
______________________________ Reply Separator _________________________________
Date: 7/28/98 5:27 PM
Maybe someone here might be able to help me on a particularly thorny problem:
What I have is a correlated subquery involving a calculation. I’ve simplified the problem in the following SQL:
SELECT
LEAD.ID, LEAD.OWNER, FORECAST.STAGE
FROM
LEAD, FORECAST
WHERE
(LEAD.ID = FORECAST.ID) AND
FORECAST.STAGE = ‘WON’ AND
FORECAST.STAGE_DATE = ALL
(
SELECT MIN(fc_alias.STAGE_DATE)
FROM FORECAST fc_alias
WHERE fc_alias.ID = LEAD.ID AND
fc_alias.STAGE = ‘WON’
)
In this case we store the STAGE_DATE as a truncated date so it’s entirely possible that we have more than record from the forecast table with the same ID and STAGE_DATE, but different STAGE’s (therefore the necessity for qualifying on STAGE in both the outer and inner queries).
The question is - How do we accomplish this in BO without resorting to modifying the query by hand? Is there a transformation here that would not require a subquery?
-
BO does not allow us to qualify on a field when we create a calculation.
-
If we get tricky and enter the qualification within the Designer (or create a subquery and return a calculation), BO does something else which I simply don’t understand. When we try to construct a correlated subquery without using the calculation wizard, BO adds the table in the outer query into the inner query. When I try to create the above query, BO will produce the following:
…
(
SELECT MIN(fc_alias.STAGE_DATE)
FROM FORECAST fc_alias, LEAD <------ notice the LEAD table here
WHERE fc_alias.ID = LEAD.ID AND
fc_alias.STAGE = ‘WON’
)
This foils the query because Oracle apparently reads this as a different instance of the table.
Any ideas? This post was rather long, thank you for your patience.
Joseph Stiehm
Cisco Systems
jstiehm@cisco.com
(919)472-2228
Listserv Archives (BOB member since 2002-06-25)