Derived Table

building semantic layer
1

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

vsenni (BOB member since 2015-11-10)

2

Can you post the SQL please?

3

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

vsenni (BOB member since 2015-11-10)

4

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 :slight_smile:

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.

mathieuBO (BOB member since 2011-01-06)