Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Selecting Dates from Date selector in OBIEE

994691Mar 27 2013 — edited Mar 27 2013

Hi,

Can any one tell me how to proceed , and what might be the other steps in order to acheive this.

Q)The user would like to select multiple dates from date selector.now that date is and actual date datatype, the selector is a calendar,making it difficult to select multiple dates at the same time. version using is 11.1.1.6

Please provide me with your valuable suggestions.

Thanking you!

Thanks,
Ven

Warren Tolentino

in your sample output is the data below
CD 345
DF 455
are from column value1 and value2 of table 2? thanks.

468585

Yes those are value1 and value2. Can you post a syntax for the same?

Warren Tolentino

you can use the BREAK command in SQL*Plus:

SQL> select * from table1;

EMPLID     EMAILID
---------- --------------------
00001      aa@test.com
00002      bb@test.com

SQL> select * from table2;

EMPLID     VALUE1     VALUE2
---------- ---------- ----------
00001      Ab         123
00001      CD         345
00001      DF         455
00002      AA         089
00002      BB         999

SQL> Break on emplid on emailid
SQL> Select t1.emplid, t1.emailid,
  2         t2.value1, t2.value2
  3    from table1 t1,
  4         table2 t2
  5   where t1.emplid = t2.emplid;

EMPLID     EMAILID              VALUE1     VALUE2
---------- -------------------- ---------- ----------
00001      aa@test.com          Ab         123
                                CD         345
                                DF         455
00002      bb@test.com          AA         089
                                BB         999

SQL>

hope this helps.

468585

Hi Warren,
Thanks for your reply. I need the ouput in the format you had sent. But is it possible to write a query without break statement.

Warren Tolentino

it is possible if you are using oracle reports.

Jens Petersen

Using Warrens sample

Select decode(rn, 1, emplid, null) emplid,
       decode(rn, 1, emailid, null) emailid,
       value1, 
       value2
from (select t1.emplid, 
             t1.emailid,
             t2.value1, 
             t2.value2,
             ROW_NUMBER() OVER (PARTITION BY t1.emplid, t1.emailid ORDER BY t2.value1, t2.value2) rn
        from table1 t1,
             table2 t2
       where t1.emplid = t2.emplid
      ) t
order by emplid, emailid, rn;

468585

Thanks Petersen...It worked...

Aketi Jyuuzou

create table table1(
EmpID char(5),
Email char(11));

insert into table1 values('00001','aa@test.com');
insert into table1 values('00002','bb@test.com');

create table table2(
EmpID  char(5),
Value1 char(2),
Value2 char(3));

insert into table2 values('00001','AB','123');
insert into table2 values('00001','CD','345');
insert into table2 values('00001','DF','455');
insert into table2 values('00002','AA','089');
insert into table2 values('00002','BB','999');
commit;

select
case Row_Number() over(partition by a.EmpID order by b.Value1,b.Value2)
when 1 then a.EmpID end as EmpID,
case Row_Number() over(partition by a.EmpID order by b.Value1,b.Value2)
when 1 then a.Email end as Email,
b.Value1,b.Value2
 from table1 a,table2 b
where a.EmpID = b.EmpID
order by a.EmpID,b.Value1,b.Value2;

OracleSQLPuzzle
http://www.geocities.jp/oraclesqlpuzzle

1 - 8

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 24 2013

Added on Mar 27 2013

#11g, #answers, #bi, #business-intelligence-applications, #business-intelligence-foundation, #business-intelligence-suite-enterprise-edition-obiee, #dashboard, #obiee, #oracle, #prompt, #publisher, #reports

3 comments

150 views

Analytics Software

Selecting Dates from Date selector in OBIEE

Comments

Post Details