on 10-16-2015 6:15 AM
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
not possible in a view. you need to create a datasource based on a function module and use abap to link both tables.
M.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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?
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.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.