Skip to Content
-1

ABAP SELECT ON CDS VIEW WITH ORDER BY FIELDS

As i have to migrate the abap code to HANA. There is a Select statement on COSP table in existing ECC system as belows.

SELECT * FROM COSP INTO TABLE ITAB ORDER BY PRIMARY KEY.

Now i have to make it compatiable to HANA. So I wrote it as below and working fine.But it is not a good practice to write all fields here. Is there any substituation to handle "ORDER BY PRIMARY KEY" while migrating to HANA?

select * from cosp into table itab order by MANDT

LEDNR
OBJNR
GJAHR
WRTTP
VERSN
KSTAR
HRKFT
VRGNG
VBUND
PARGB
BEKNZ
TWAER
PERBL
MEINH
WTG001
WTG002
WTG003
WTG004
WTG005
WTG006
WTG007
WTG008
WTG009
WTG010
WTG011
WTG012
WTG013
WTG014
WTG015
WTG016
WOG001
WOG002
WOG003
WOG004
WOG005
WOG006
WOG007
WOG008
WOG009
WOG010
WOG011
WOG012
WOG013
WOG014
WOG015
WOG016
WKG001
WKG002
WKG003
WKG004
WKG005
WKG006
WKG007
WKG008
WKG009
WKG010
WKG011
WKG012
WKG013
WKG014
WKG015
WKG016
WKF001
WKF002
WKF003
WKF004
WKF005
WKF006
WKF007
WKF008
WKF009
WKF010
WKF011
WKF012
WKF013
WKF014
WKF015
WKF016
PAG001
PAG002
PAG003
PAG004
PAG005
PAG006
PAG007
PAG008
PAG009
PAG010
PAG011
PAG012
PAG013
PAG014
PAG015
PAG016
MEG001
MEG002
MEG003
MEG004
MEG005
MEG006
MEG007
MEG008
MEG009
MEG010
MEG011
MEG012
MEG013
MEG014
MEG015
MEG016
MEF001
MEF002
MEF003
MEF004
MEF005
MEF006
MEF007
MEF008
MEF009
MEF010
MEF011
MEF012
MEF013
MEF014
MEF015
MEF016
MUV001
MUV002
MUV003
MUV004
MUV005
MUV006
MUV007
MUV008
MUV009
MUV010
MUV011
MUV012
MUV013
MUV014
MUV015
MUV016
BELTP
TIMESTMP
BUKRS
FKBER
SEGMENT
GEBER
GRANT_NBR
BUDGET_PD

Is there any replacement of above code where we can replace all above key fields using one line statement? Or is there any other approach to handle the "ORDER BY PRIMARY KEY" while selecting data from equivalent CDS view?

Add comment
10|10000 characters needed characters exceeded

  • SELECT * from COSP into itab ORDER BY PRIMARY KEY has been used in the existing system which is not on HANA. I have to replace this select statement for HANA box to rectify the error. Please note old COSP table is converted to CDS view in HANA system with the same name and ORDER BY PRIMARY KEY doesn't work while selecting data from CDS view.

    So, I converted this select statement as below and it is working. I used below fields under ORDER BY clause as they are defined as KEYS in the CDS view. My question is --> Writing all fields in below select statements is very lengthy process and not a good practice. Is there any approach to write this SELECT statement to avoid this lengthy statement and follow a good practice?

    select * from cosp into table itab order by MANDT

    LEDNR
    OBJNR
    GJAHR
    WRTTP
    VERSN
    KSTAR
    HRKFT
    VRGNG
    VBUND
    PARGB
    BEKNZ
    TWAER
    PERBL
    MEINH
    WTG001
    WTG002
    WTG003
    WTG004
    WTG005
    WTG006
    WTG007
    WTG008
    WTG009
    WTG010
    WTG011
    WTG012
    WTG013
    WTG014
    WTG015
    WTG016
    WOG001
    WOG002
    WOG003
    WOG004
    WOG005
    WOG006
    WOG007
    WOG008
    WOG009
    WOG010
    WOG011
    WOG012
    WOG013
    WOG014
    WOG015
    WOG016
    WKG001
    WKG002
    WKG003
    WKG004
    WKG005
    WKG006
    WKG007
    WKG008
    WKG009
    WKG010
    WKG011
    WKG012
    WKG013
    WKG014
    WKG015
    WKG016
    WKF001
    WKF002
    WKF003
    WKF004
    WKF005
    WKF006
    WKF007
    WKF008
    WKF009
    WKF010
    WKF011
    WKF012
    WKF013
    WKF014
    WKF015
    WKF016
    PAG001
    PAG002
    PAG003
    PAG004
    PAG005
    PAG006
    PAG007
    PAG008
    PAG009
    PAG010
    PAG011
    PAG012
    PAG013
    PAG014
    PAG015
    PAG016
    MEG001
    MEG002
    MEG003
    MEG004
    MEG005
    MEG006
    MEG007
    MEG008
    MEG009
    MEG010
    MEG011
    MEG012
    MEG013
    MEG014
    MEG015
    MEG016
    MEF001
    MEF002
    MEF003
    MEF004
    MEF005
    MEF006
    MEF007
    MEF008
    MEF009
    MEF010
    MEF011
    MEF012
    MEF013
    MEF014
    MEF015
    MEF016
    MUV001
    MUV002
    MUV003
    MUV004
    MUV005
    MUV006
    MUV007
    MUV008
    MUV009
    MUV010
    MUV011
    MUV012
    MUV013
    MUV014
    MUV015
    MUV016
    BELTP
    TIMESTMP
    BUKRS
    FKBER
    SEGMENT
    GEBER
    GRANT_NBR
    BUDGET_PD

  • Get RSS Feed

4 Answers

  • Apr 20 at 07:17 AM
    -1

    SELECT * from COSP into itab ORDER BY PRIMARY KEY has been used in the existing system which is not on HANA. I have to replace this select statement for HANA box to rectify the error. Please note old COSP table is converted to CDS view in HANA system with the same name and ORDER BY PRIMARY KEY doesn't work while selecting data from CDS view.

    So, I converted this select statement as below and it is working. I used below fields under ORDER BY clause as they are defined as KEYS in the CDS view. My question is --> Writing all fields in below select statements is very lengthy process and not a good practice. Is there any approach to write this SELECT statement to avoid this lengthy statement and follow a good practice?

    select * from cosp into table itab order by MANDT

    LEDNR
    OBJNR
    GJAHR
    WRTTP
    VERSN
    KSTAR
    HRKFT
    VRGNG
    VBUND
    PARGB
    BEKNZ
    TWAER
    PERBL
    MEINH
    WTG001
    WTG002
    WTG003
    WTG004
    WTG005
    WTG006
    WTG007
    WTG008
    WTG009
    WTG010
    WTG011
    WTG012
    WTG013
    WTG014
    WTG015
    WTG016
    WOG001
    WOG002
    WOG003
    WOG004
    WOG005
    WOG006
    WOG007
    WOG008
    WOG009
    WOG010
    WOG011
    WOG012
    WOG013
    WOG014
    WOG015
    WOG016
    WKG001
    WKG002
    WKG003
    WKG004
    WKG005
    WKG006
    WKG007
    WKG008
    WKG009
    WKG010
    WKG011
    WKG012
    WKG013
    WKG014
    WKG015
    WKG016
    WKF001
    WKF002
    WKF003
    WKF004
    WKF005
    WKF006
    WKF007
    WKF008
    WKF009
    WKF010
    WKF011
    WKF012
    WKF013
    WKF014
    WKF015
    WKF016
    PAG001
    PAG002
    PAG003
    PAG004
    PAG005
    PAG006
    PAG007
    PAG008
    PAG009
    PAG010
    PAG011
    PAG012
    PAG013
    PAG014
    PAG015
    PAG016
    MEG001
    MEG002
    MEG003
    MEG004
    MEG005
    MEG006
    MEG007
    MEG008
    MEG009
    MEG010
    MEG011
    MEG012
    MEG013
    MEG014
    MEG015
    MEG016
    MEF001
    MEF002
    MEF003
    MEF004
    MEF005
    MEF006
    MEF007
    MEF008
    MEF009
    MEF010
    MEF011
    MEF012
    MEF013
    MEF014
    MEF015
    MEF016
    MUV001
    MUV002
    MUV003
    MUV004
    MUV005
    MUV006
    MUV007
    MUV008
    MUV009
    MUV010
    MUV011
    MUV012
    MUV013
    MUV014
    MUV015
    MUV016
    BELTP
    TIMESTMP
    BUKRS
    FKBER
    SEGMENT
    GEBER
    GRANT_NBR
    BUDGET_PD

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 19 at 02:54 PM

    ORDER BY PRIMARY KEY doesn't require every field of the table, only the primary keys (AFAIK 14 first fields)

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 20 at 10:11 AM

    Hi Himanshu,

    Thanks for response.

    1) I can't see the key in DDL source of COSP . Below is the piece of code, i am getting while opened the DDL source : V_COSP_DDL.

    2) Is there any table where i can pass this CDS view/DDL Source of COSP and get the key fields? I can see all fields are key fields in the CDS view COSP.

    @AbapCatalog.sqlViewName: 'COSP'
    @EndUserText.label: 'CO Object: Cost Totals for External Postings ("COSP")'
    define view v_cosp_view as
       select
           mandt, lednr, objnr, gjahr, wrttp, versn,
           kstar, hrkft, vrgng, vbund, pargb, beknz,
    @Semantics.currencyCode
    
           twaer,
    
           perbl,
    
           meinh,
    
           wtg001,
    
           wtg002,
    
           wtg003,
    
           wtg004,
    
           wtg005,
    
           wtg006,
    
           wtg007,
    
           wtg008,
    
           wtg009,
    
           wtg010,
    
           wtg011,
    
           wtg012,
    
           wtg013,
    Add comment
    10|10000 characters needed characters exceeded

  • Apr 20 at 09:08 AM

    Hi Deepak,

    you don't have to put all the fields in the order by statement in HANA.

    if you want to replace order by primary key then you have to specify only key fields in the order by statement.

    As per DDL source of COSP table , key fields has been defined till perbl field only.

    Metadata.ignorePropagatedAnnotations: true
    @AccessControl.authorizationCheck: #NOT_REQUIRED
    @ObjectModel.usageType.serviceQuality: #P
    @AbapCatalog.preserveKey: true
    define view v_cosp_view as
        select
           key mandt, key lednr, key objnr, key gjahr, key wrttp, key versn,
           key kstar, key hrkft, key vrgng, key vbund, key pargb, key beknz,
    @Semantics.currencyCode
           key twaer,
           key perbl,
           meinh,
           wtg001,
           wtg002,
    
    Add comment
    10|10000 characters needed characters exceeded