Hi need help in making universe (not Exists condition)

building semantic layer
1

hi all

i want to make universe by seeing this sql .guys is it posible
bcos they want to select mutiple values and i cnt hardcore all in sql.

i tryed to subquery in in bo report but when creating prompts for second subquery sql option was disables and even run option.

experts now its up to you

select 
a.l1_id,
a.mbpn,
a.dc_part_no,
a.part_color,
a.part_desc,
a.part_sec_cd,
a.part_item_no,
a.loc_cd_from,
a.loc_cd_to,
a.fin_proc_loc,
a.share_rate,
a.qty_unit,
a_efct_beg_dt,
a_efct_end_dt,
a.bm_y_model_cd,
a.bm_option_cd,
a.bm_type_cd

from
 ghpej5.gab32d00 a
where a.bm_y_model_cd ='zoja'
and a.bm_type_cd in (' 51',' 59')
and a.bm_option_cd in('','')
and a.bm_ext_clr_cd in ('nh1 ','nh5  ')
and a_efct_end_dt > '20050808'
and a.plant_cd ='hpe'

and not exists 

(select * from ghpej5.gab32d00 b
   where a.dc_part_no = b.dc_part_no
     and b.plant_cd ='hpe'   
     and b.bm_y_model_cd ='zoja'
and b.bm_type_cd in (' 44',' 45')
and b.bm_option_cd in('','')
and b.bm_ext_clr_cd in ('r280 ','mh4  ')
and b.efct_end_dt > '20050808'
and b.plant_cd ='hpe'
 
)

please help me in this sutuation .

thanks you
regards
kiran

reddy108 (BOB member since 2005-09-08)

2

Can’t you split the query and create 2 data providers and a union / intersect?

MikeD :south_africa: (BOB member since 2002-06-18)

3

In the SQL that you have given, I see that table a and table b are the same. If all of the columns that you wish to see are not listed as objects in the universe then you might want to add them. If you want to create a universe for this report then you will have only one table in it which is the ghpej5.gab32d00 table. You have not specified the scenario but you are trying to create a self join for the column dc_part_no. Once you insert the table in the universe, just pull it into the object pane so that it creates a class with the table name and all the columns in the table as objects. now you have to create a condition object in which you have one set of conditions in the ‘where’ clause in it and the second set of conditions (those you have put in the paranthsis in the where clause except the join part a.dc_part_no = b.dc_part_no) should be put in another object which selects * from ghpej5.gab32d00 . when done with this just save it and open the query panel, pull in all the objects that you see in the SQL here along with the object for selecting * from that table. In the condition panel pull in the conditon object that you have created. Now run the report or check for the SQL. it should be the way in which you desired.

pj820 (BOB member since 2005-10-07)

4

Create a predefined filter with the correlated subquery. This should get you the sql that you want.

kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)