How to concat 2 fields in database view for creating generic datasource

[former_member198905]

Hi

How to concat 2 fields in database view for creating generic datasource?

I need to create a database view between RSEG and BKPF for creating generic datasource i.e.

RSEG's two fields BELNR &GJAHR are concat value is equal to BKPF's AWKEY value. 

so I define following in Table/join Condition tab in SE11:

RSEG - MANDT = BKPF - MANDT

RSEG - BELNR & GJAHR = BKPF - AWKEY  (Error: Field RSEG-BELNR&GJAHR does not belong to a base table)

How to resolve it?

Thanks

[former_member186445 · Accepted solution]

not possible in a view. you need to create a datasource based on a function module and use abap to link both tables.

M.

[former_member198905]

Hi

I try to write a FM code please check it and correct it if any thing is wrong:

TABLES: ZNADFLDGL14.

DATA: L_S_SELECT TYPE SRSC_S_SELECT.

STATICS: S_S_IF TYPE SRSC_S_IF_SIMPLE,

               S_COUNTER_DATAPAKID LIKE SY-TABIX,

               S_CURSOR TYPE CURSOR.

RANGES: SDNO FOR ZNADFLDGL14-AWKEY.

                SDNO FORZNADFLDGL14-BELNR.          

SDNO FORZNADFLDGL14-GJAHR.

IF I_INITFLAG = SBIWA_C_FLAG_ON.

CASE I_DSOURCE.

WHEN 'ZFMADFLDGL14'.

WHEN OTHERS.

     IF 1 = 2. MESSAGE E009(R3). ENDIF.

     LOG_WRITE 'E'           "message type

                         'R3'         "message class

                         '009'        "message number

                         I_DSOURCE "message variable 1

                         ' '.           "message variable 2

     RAISE ERROR_PASSED_TO_MESS_HANDLER.

ENDCASE.

APPEND LINES OF I_T_SELECT TO S_S_IF-T_SELECT.

S_S_IF-REQUNR = I_REQUNR.

S_S_IF-DSOURCE = I_DSOURCE.

S_S_IF-MAXSIZE = I_MAXSIZE.

APPEND LINES OF I_T_FIELDS TO S_S_IF-T_FIELDS.

ELSE.

IF S_COUNTER_DATAPAKID = 0.

LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT WHERE

     FIELDNM = 'AWKEY'

     FIELDNM = 'BELNR'

     FIELDNM = 'GJHAR'.

     MOVE-CORRESPONDING L_S_SELECT TO SDNO.

     APPEND SDNO.

ENDLOOP.

SELECT

     RSEG~BELNR RSEG~GJAHR

     BKPF~BELNR BKPF~GJAHR BKPF~AWKEY

FROM RSEG, BKPF

     INNER JOIN RSEG on EKPO~EBELN = RSEG~EBELN

     INNER JOIN BKPF on CONCATENATE RSEG~BELNR RSEG~GJAHR =  BKPF~AWKEY

WHERE

     RSEG~EBELN IN SDNO

     BKPF~AWKEY IN SDNO

ENDIF.

FETCH NEXT CURSOR S_CURSOR

APPENDING CORRESPONDING FIELDS

OF TABLE E_T_DATA

PACKAGE SIZE S_S_IF-MAXSIZE.

IF SY-SUBRC <> 0.

     CLOSE CURSOR S_CURSOR.

     RAISE NO_MORE_DATA.

ENDIF.

     S_COUNTER_DATAPAKID = S_COUNTER_DATAPAKID + 1.

ENDIF.

ENDFUNCTION.

Thanks

[former_member185132]

Did you try to check or activate this FM in SE37? Because it would throw an error at your select statements. CONCATENATE cannot be used a join condition.

The code needs to be reworked as there are multiple serious problems

• there is a FETCH yet there is no OPEN CURSOR
• there is a SELECT from BKPF (usually a big table) yet there is no delta-capability, so you are essentially pulling data from all eternity each time there is a load
• the selection criteria for three fields (AWKEY, BELNR and GJAHR) are all "merged" into one ITAB though their respective sizes and domains are completely different
• the RSEG table is being filtered on the condition of "EBELN in SDNO", but SDNO is a table that contains AWKEY, BELNR and GJAHR values, none of which are Purchase Orders (EBELNs)
• you are pulling from a big table (BKPF) but you are also joining that with another table RSEG, and the result is likely to be huge and will probably cause performance overheads in ECC.

I would suggest that you clarify the requirement first (on what condition is each table supposed to be filtered, and what are the relations between them)

Then, I would suggest to go with standard extractors for BKPF/BSEG (the 0FI_GL* family of datasources)  so you get all the FI docs to BW. Hint: if you are in a mature BW system this might already be happening. Extract RSEG separately to BW. That way you'll have the delta capability in place.

Finally, do the joining in BW. This is a flexible design - tomorrow if you need to use BKPF/BSEG or RSEG data individually you won't need to rebuild from scratch and can reuse this. It's also easier to manage and easier on the source system too.

[former_member198905]

Hi

Finally, do the joining in BW. This is a flexible design - tomorrow if you need to use BKPF/BSEG or RSEG data individually you won't need to rebuild from scratch and can reuse this. It's also easier to manage and easier on the source system too.

You mean create a generic data source of each table but how to join in BW?

[former_member185132]

I don't mean to create a generic DS for each table. 0FI_GL_4 or 14 should be good enough to get you the BKPF and BSEG data. Look for std sources for RSEG too, otherwise you could go for a gen DS for that.

How to join it in BW is by using lookups in transformations. The join logic would be based on the same field(s) as in ECC.

[former_member198905]

Actually we are using 0FI_GL_14 but we need 11 more fields from EKKO, EKPO, BKPF, PAYR and LFA1 which are not available in GL14 that why I think generic data source is the solution. 

[former_member185132]

Even in such situations you could always use standard extractors to get the EKKO/EKPO data (2LIS_02*) and BKPF/BSEG etc separately and combine in BW. This is actually quite a common pattern in BW wherein data for each different type of object (Acc Doc, Pur Doc etc) is pulled separately and then combined in BW.

The problem with going with one DS that gets ALL the data is that (a) it will put a lot of load on the source system and (b) you will miss out on delta capabilities because the create/update events for all those tables are different.

[former_member198905]

I found 7 DS when I combine them then 10 fields from 11 required fields are available but what will I do the 1 field which is not available in any data source?

Is it good practice that use Data source for 2/3 fields and 7/8 DS's uses to fulfill requirement instead of 1 generic data source?

[Former Member]

Hi Imran,

The data base view will work when the table has common Key fields. You cannot join the fields like that. the system knows to identify one to one mapping.

Regards,

M Priya

[Former Member]

Hi Imran,

Both fields BELNR(doc number) & GJAHR(fisc year) exists in both tables RSEG & BKPF, you can use these two fields in your view join conditions? then why do you need to concatenate BELNR and GJAHR to join with AWKEY? 

you can simply use the common fields (doc number, fisc year and comp code) to join and create a view.

[former_member198905]

Hi Jyothi

Check in you ECC system BELNR of both tables are not equal at all.

[Former Member]

ok, in that case you can go for Functional module as suggested by others.

[former_member220624]

Hi,

I don't think it's possible to specify this join condition in se11.

You can think of wriitng a code to accomplish the requirement.

Something like below -

select belnr gjahr from RSEG into table <internal table 1> <where = "filter condition" if any>

loop at <internal table 1> into <work area>.

concatenate <work area-beln> <work area-gjahr> into <work area-zfield>.

endloop.

select * from BKPF into table <internal table 2> for all entries in <internal table 1> where awkey = <internal table 1>-zfield.

Regards,

Amit

[former_member198905]

Hi

Is it possible that we create a copy of RSEG table and add new field i.e. ZAWKEY  which contain concat values of BELNR+GJAHR, then we join it to BKPF's AWKEY?

If it is possible then how to concat values of BELNR+GJAHR and save it to our new field i.e. ZAWKEY

[former_member220624]

This message was moderated.