A Query to be created in BO Designer

[Anu1284]

Please let me know how to create the objects in designer for the below mentioned query: I'm facing problems in creating the objects for the below query. I created one object for the subquery. But this query is still not incorporated. Please provide me with a solution.

SELECT
SUM(a.DURATION_HOURS)/(COUNT(*)/2)
FROM
ps_absw_tmpl_det a,
ps_absw_schd_det b,
ps_absw_schedule c
WHERE
a.absw_tmpl_id = b.absw_tmpl_id
AND b.absw_schd_id = c.absw_schd_id
AND c.emplid = '106168'
AND c.empl_rcd# = 0
AND c.begin_dt = (
SELECT
max(d.begin_dt)
FROM
ps_absw_schedule d
WHERE
d.emplid = '106168'
AND d.empl_rcd# = 0
)
AND a.duration_hours <> 0

Thanks,
Anuradha.A

 

[Sridharan]

Don't create an Object for SQ instead put that in the WHERE Condition of the Object you are creating... But remember you cannot have the Aliasing thing you do in the Query (FROM Clause) instead have to supply full table name in the conditions while creating the object... Once done try to make a report out of it and see how it comes.. What do you mean

&quot;But this query is still not incorporated. &quot;

Sri

 

[Anu1284]

This solution doesn't seem effective.

My problem is the above mentioned query needs to be created in BO Designer. Please let me know how to create the object with the above mentioned SQL query.

Do I need to create two objects for this query to get executed?? I'm unable to provide the subquery information in the objects. Please suggest the solution.

 

[Sridharan]

Your subject itself says you want the object to be created in Designer.

The solution that I've given is also for BO Designer only.. If it is at the BO Reporter then it will be a simple stuff.. No need to create two objects... When you create an Object in Designer what are the things you can specify

1. Select Clause
2. Where Condition.

Can you see those???? Is that fine?????

So put the

       SUM(ps_absw_tmpl_det.DURATION_HOURS)/(COUNT(*)/2)

in the Select and in the Where Clause put the condition without the Alias stuff replacing them with Actual Table Names.

HTH
Sri

 

[Anu1284]

Thanks Sridharan for your suggestion. I got that object working with the query.

But when I use that object(hours) in another object allotment = (table.field / hours object), while parsing I get the following error

&quot; A select statement with no Group by clause contains the column name and the column function in the select clause, or a column name is contained in the select clause and not in the group by clause&quot;.

I specified a group by clause for this error but another error is displayed.

&quot;The use of the reserved word BY following &quot;'&quot; is not valid. Expected tokens include &quot;, FROM, INTO&quot;.

Any suggestions on eliminating this error.