Drill-thru question

From: Bala, Prakash <prakash.bala_at_cingular.com>
Date: Wed, 22 Aug 2001 09:20:29 -0700
Message-ID: <F001.003741AA.20010822090547@fatcity.com>

Hi,

I am a newbie to this OLAP area.

I have this table (company_dimension) where every company has a parent, which in turn can be a child to another parent and so on. It is about 10 levels deep.

TEST_at_D0MARS> desc company_dimension

 Name                                            Null?    Type
 ----------------------------------------------- --------
--------------------------------
 COMPANY_CODE                                    NOT NULL VARCHAR2(50)

---> primary key

 COMPANY_DESC
VARCHAR2(100)

 PARENT_COMPANY_CODE                                      VARCHAR2(50)

---> foreign key pointing to company_code

The following table maintains the mobile phone numbers for every company.

TEST_at_D0MARS> desc DLY_TRANSACTION_DETAIL

 Name                                            Null?    Type
 ----------------------------------------------- --------
--------------------------------
 ACTIVITY_DATE                                            DATE
 COMPANY_CODE                                          VARCHAR2(10)


---> foreign key to the company_dimension table

 MOBILE_NUMBER                                          NUMBER(10)

The requirement is to get all the phone numbers for a given company and its children for a given day. Since I cannot join these 2 tables using the 'connect by' clause, how can I accomplish it, without using a sub-query? (actually 2 other dimensions play a role in the above table and I have simplified the structure)

Created a dimension on the company_dimension table and used the dbms_olap.validate_dimension procedure to validate it. Everything looks good, but none of the manuals explains how to use the dimension in a query.

This is Oracle 8.1.7 on Sun Solaris.

Thanks in advance.

Received on Wed Aug 22 2001 - 11:20:29 CDT