Skip to Main Content

SQL & PL/SQL

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!

How to use CONNECT BY LEVEL to get this result?

672680Apr 10 2011 — edited Apr 10 2011
Hi all,

my DB version is 10gR2.

here is the table and the data:
create table t (from_dt date, to_dt date, eno varchar2(2), deptno varchar2(10));

insert into T values (TO_DATE('2010-01-01','yyyy-mm-dd'),TO_DATE('2010-04-30','yyyy-mm-dd'),'A','DEPTA');
insert into T valueS (TO_DATE('2010-05-01','yyyy-mm-dd'),TO_DATE('2010-12-31','yyyy-mm-dd'),'A','DEPTB');
insert into T values (TO_DATE('2010-01-01','yyyy-mm-dd'),TO_DATE('2010-06-30','yyyy-mm-dd'),'B','DEPTA');
insert into T valueS (TO_DATE('2010-07-01','yyyy-mm-dd'),TO_DATE('2010-12-31','yyyy-mm-dd'),'B','DEPTB');

from_dt       to_dt           ENO   DEPTNO
------------------------------------------------
2010-01-01 2010-04-30  A     DEPTA
2010-05-01 2010-12-31  A     DEPTB
2010-01-01 2010-06-30  B     DEPTA
2010-07-01 2010-12-31  B     DEPTB
Here is the result I want:
MONTH    ENO   DEPTNO
-----------------------------
2010-01    A      DEPTA
2010-02    A      DEPTA
2010-03    A      DEPTA
2010-04    A      DEPTA
2010-05    A      DEPTB
...............
Here is what I came up with:
select distinct ADD_MONTHS(TRUNC(FROM_DT,'MON'),level-1) as MONTHS, 
       ENO, 
       DEPTNO
from (
    select FROM_DT, TO_DT, ENO, DEPTNO, ROUND(MONTHS_BETWEEN(TO_DT, FROM_DT)) as BET from T
) connect by level<=BET
order by eno, deptno,months ;
the problem with this is that, many duplicate rows are generated if I remove DISTINCT from the sql.
and I don`t know how that happens? How CONNECT BY LEVEL, returns me so many duplicate rows?

Anyone have any idea about this?
And is there any other way to do this as well?

Thanks

Comments

Francisco Amores

Hi,

just to confirm how the integration process works with SAP integration.

When you integrate SAP with FDMEE data will be extracted to a flat file in shared folder called FSMOUNT (typically located in the FDMEE server). The extract process is basically an ABAP program executed by ODI Agent. Then the ODI agent will take this file and import into FDMEE. Shared folder must be declared in SAP (Transaction AL11)

You need to ensure that the shared folder must be accessible from the SAP system using the <sid>adm user and from the operating system user that starts the ODI agent.

In the ODI Topology, you will have to configure Data Serve "FDMEE _SAP_FSMOUNT" to point the shared folder.

Regarding drill through, FDMEE will open the drill through url you configure in FDMEE depending on your requirements. You will have to enable ITS Server Version 6.2 or higher (Internet Transaction Server) so you can navigate your SAP instance through the web. FDMEE source adapter for SAP comes with some predefined drill through urls (including parameters) based on the SAP module you integrate.

I hope that clarifies.

user8628169

Thanks Francisco.

Correct me if I am wrong, the pre-built SAP adapter which comes with FDMEE is for SAP BW and not for SAP G/L. Does this adapter also provides the ability to drill through to transactional data ?

Francisco Amores

FDMEE supports both SAP ECC 4.6+ / R3 (ERP Financials) and SAP BW.

So you can integrate SAP GL modules (NEWGL and Classic GL) and others:

  • General Ledger
  • Profit Center
  • Cost Center
  • Vendor Balances
  • Customer Balances

For SAP BW, it extracts data from standard as well as custom infocubes.

Integration includes data load and drill through for all SAP integration.

Regards

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 8 2011
Added on Apr 10 2011
10 comments
79,972 views