Creating a view from original model query subjects

[EarthDog]

I have created in my Database layer the following view:

Code Select Expand

select ILLOTN,ILMCU,ILLOCN,max(ILTRDJ) ILTRDJ
from [AS400GR].F4111 f4111
where ILFRTO='T' and ILLOTN <> '' and ILLOTN <>'TEMP'
group by ILLOTN,ILMCU,ILLOCN
having sum(ILTRQT) <> 0

It works well like that.

But i am wondering if i am missing the proper way to do it.

F4111 table exists in Databse Layer. Can i create the above view only by using framework and not by inputting the statement directly?

[bdbits]

You want to build the view without using the SQL from the view, is that right?

I see nothing there that should limit you from building this in FM. You want to build a summary query subject.

Read this: http://www-01.ibm.com/support/knowledgecenter/SSRL5J_1.0.1/com.ibm.swg.ba.cognos.ug_cr_rptstd.10.1.1.doc/c_cr_rptstd_wrkdat_add_summary_rel.html%23cr_rptstd_wrkdat_add_summary_rel

Hope that helps.

[EarthDog]

Let me state it simpler, i hope..

I need a view that has group by and having by clauses.... I created this in database layer and i am happy..What i need ot know is if there is a best practice that says i have to do it in another manner..

I don't need to just summarize a field. I need to get a grouped version of a table with having and put this as a dimension to a fact table in FM....

Is there any way to put in the data item definition DIRECTLY the subquery select? I think not...

[Lynn]

Let me state it simpler, i hope..

I need a view that has group by and having by clauses.... I created this in database layer and i am happy..What i need ot know is if there is a best practice that says i have to do it in another manner..

I don't need to just summarize a field. I need to get a grouped version of a table with having and put this as a dimension to a fact table in FM....

Is there any way to put in the data item definition DIRECTLY the subquery select? I think not...

I would create the view in the database, not in the framework. Import the view and then model as necessary from there.

I don't really understand the question in your last sentence. Put what data item definition where? Data item expression refer to elements within a query subject.

[EarthDog]

There are reasons that i dont want to create the view in the db, so i will assume that the next best thing is to do it as i have done it. Create it in the db layer.

As for the last thing..i was looking for a way to define the subquery itself on the Query Subject definition screen on the specific data item.....

Thanks!