BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: No events within the next 45 days.

Derived Table


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
vsenni
Senior Member
Senior Member



Joined: 10 Nov 2015

Posts: 70



PostPosted: Sat Oct 13, 2018 7:16 am 
Post subject: Derived Table

Hi ,

I am trying to pass a Prompt value in the inside of the derived table query , also i want to give the list of values so i have chosen the class and objects in the prompt ( i have tried with class and objects from same derived table also tried with date dimension included in same context) , but i am getting the error.

The Class doesn't exist (UNV0037)

Can you please help me
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11432
Location: Manchester, UK


flag
PostPosted: Mon Oct 15, 2018 5:06 am 
Post subject: Re: Derived Table

Can you post the SQL please?
_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
vsenni
Senior Member
Senior Member



Joined: 10 Nov 2015

Posts: 70



PostPosted: Mon Oct 15, 2018 7:22 am 
Post subject: Re: Derived Table

Hi,

Thanks, please find my query below. if i pass pass the prompt at the end the result is coming only Not Available.

( Note: I have to show the LOV in the prompt)

select * from (select
CASE
when count(distinct mo_id ) over(partition by 1) >1 then 5

WHEN MO_ID=to_char(sysdate,'yyyymm') then 1
When MO_ID<to_char(sysdate,'yyyymm') then 3
else null end as RES_IND ,
ind,mo_id, live_dt, case when mo_beg_dt<live_dt then 0 else MONTHS_BETWEEN(mo_beg_dt, live_dt)
end as diff_mo, mtd_pm from (

select *

from (
select 1 ind,MO_DIM.mo_id,mo_dim.mo_beg_dt, (select mo_beg_dt from mo_dim where mo_id=201806) LIVE_DT, SubStr(MO_NAM,1,3)|| ' - ' || SubStr(YR_NUM,3,2) mtd_pm from mo_dim where yr_num>=2017
union
select 1 ind,MO_DIM.mo_id,mo_dim.mo_beg_dt, (select mo_beg_dt from mo_dim where mo_id=201806) LIVE_DT, 'PREVIOUS' mtd_pm from mo_dim where yr_num>=2017
union
select 1 ind, MO_DIM.mo_id,mo_dim.mo_beg_dt, (select mo_beg_dt from mo_dim where mo_id=201806) LIVE_DT, 'PREVIOUS WEEK' mtd_pm from mo_dim where yr_num>=2017
union
select 1 ind,MO_DIM.mo_id,mo_dim.mo_beg_dt, (select mo_beg_dt from mo_dim where mo_id=201806) LIVE_DT, 'PREVIOUS 4 WEEKS' mtd_pm from mo_dim where yr_num>=2017
union
select 1 ind,MO_DIM.mo_id,mo_dim.mo_beg_dt, (select mo_beg_dt from mo_dim where mo_id=201806) LIVE_DT, SubStr(MO_NAM,1,3)|| ' - ' || SubStr(YR_NUM,3,2) mtd_pm from mo_dim where yr_num>=2017
union
select 3 ind,MO_DIM.mo_id,mo_dim.mo_beg_dt, (select mo_beg_dt from mo_dim where mo_id=201806) LIVE_DT, 'PREVIOUS' mtd_pm from mo_dim where yr_num>=2017
union
select 3 ind,MO_DIM.mo_id,mo_dim.mo_beg_dt, (select mo_beg_dt from mo_dim where mo_id=201806) LIVE_DT, SubStr(MO_NAM,1,3)|| ' - ' || SubStr(YR_NUM,3,2) mtd_pm from mo_dim where yr_num>=2017
union
select 5 ind,MO_DIM.mo_id,mo_dim.mo_beg_dt, (select mo_beg_dt from mo_dim where mo_id=201806) LIVE_DT, 'NOT AVAILABLE' mtd_pm from mo_dim where yr_num>=2017
)

)where mo_id in @Prompt('Enter value','A',,Multi,Free,Not_Persistent)


) where RES_IND=IND



Once aging thanks for your help
Back to top
mathieuBO
Principal Member
Principal Member



Joined: 06 Jan 2011

Posts: 123



PostPosted: Wed Oct 17, 2018 8:39 am 
Post subject: Re: Derived Table

It sounds good to me, I don't tested but if I remember : the derivered dtable is not axcepted because of the prompt. Let's make two steps :
1) create the derivered table without the prompt andcreate the objects on it
2) modify the derived table including the prompt :: it will show an error and no columns anymore lol
But it will work on the fly via Web Intelligence icon_smile.gif

If still not, try something different :
1) derivered table without the prompt filter
2) make a prompt filter as an object in the business layer panel or in the WHERE statement of an object.

let us know.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0282 seconds using 18 queries. (SQL 0.0030 Parse 0.0009 Other 0.0244)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo