Need help to change BO generated query at universe leavel

[Former Member]

Hi

Is there any possiblity to change sql at universe leavel.Kindly help me i am new to designer.

The below is the BO generated query.

SELECT specObjID

FROM SpecObj

WHERE SpecClass = dbo.fSpecClass('UNKNOWN')

-

---

-

---

Users are not intrested to go for coustomised sql in report leavel.

I need to change the above sql accordingly due to some performance issues.

The feilds that specify in the where clause SpecClass = dbo.fSpecClass('UNKNOWN') move to from clause.

Like below

SELECT specObjID

FROM SpecObj ,SpecClass = dbo.fSpecClass('UNKNOWN')

WHERE

-

---

-

---

Kindly suggest me greatly appriciated.

Thanks.

Raj

[Former Member · Accepted solution]

HI,

As per your expectation query like below is not possible to give condition in from clause as per my understand,

SELECT specObjID

FROM SpecObj ,SpecClass = dbo.fSpecClass('UNKNOWN')

WHERE

I think your expected result can be achieved by giving condition while creating the object SpecObj.

Steps:

Go to universe, then edit the object SpecObj there you can give the where clause condition

WHERE SpecClass = dbo.fSpecClass('UNKNOWN')

This will increase your performance.

Regrds,

Ragoth.C

[Former Member]

Hi, you can adjust whether a condition appears in the "from" or "where" clause by using ANSI 92 joins, and then updating the universe to show which objects should appear in the from clause. I wrote a blog post about this a while back that was specific to using outer joins, but I would think the same approach would allow you to solve the problem as you described it.

http://www.dagira.com/2010/08/17/handling-conditions-on-outer-joins/

[Former Member]

You can try to create a derived tabke as per the datarestriction you want. Join this table to other tables in the universe. Create Object and classes.

Now uese it in the report and see if your getting your desired result.

Hope this will help you out.