I have created in my Database layer the following view:
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?
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 (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.
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...
Quote from: EarthDog on 19 Nov 2015 05:57:20 AM
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.
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!