ALTER view issue with Sybase ASE

[Former Member]

Hi,

I'm trying to Alter a view on Sybase ASE via ABAP native SQL.

I'm Facing following issues :

• With Alter view : http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1510/html/iqrefso/B...
  • However it gives error “[ASE Error SQL102:42000]Incorrect syntax near 'ENCRKEYALTER'.#”
• Create or Replace : http://dcx.sybase.com/1101/en/dbreference_en11/create-view-statement.html
  • Give error : “[ASE Error SQL156]Incorrect syntax near the keyword 'replace'.#”

I tried using UPPER and Lower case SQL, still results in error.

Sample view SQL :

CREATE OR REPLACE VIEW test1 AS

SELECT bkpf.bukrs AS bukrs,

       bkpf.hwaer AS hwaer,

       bkpf.budat AS budat,

       bkpf.bstat AS bstat,

       bseg.kunnr AS kunnr,

       bseg.koart AS koart,

       bseg.augdt AS augdt,

       bseg.madat AS madat,

       bseg.mandt AS mandt,

       bseg.umskz AS shbkz,

       bseg.dmbtr AS dmbtr,

       bseg.rstgr AS rstgr,

       bseg.manst AS manst,

       kna1.name1 AS name1,

       kna1.land1 AS land1,

       kna1.ort01 AS ort01,

       kna1.pstlz AS pstlz,

       kna1.stras AS stras,

       kna1.regio AS regio,

       kna1.brsch AS brsch,

       kna1.vbund AS vbund_kna1,

       kna1.begru AS begru_kna1,

       knb1.begru AS begru_knb1,

       knb1.busab AS busab,

       t001s.sname AS sname,

       knkk.ctlpc AS ctlpc_knkk,

       bseg_r.zfbdt AS zfbdt_1,

       bseg_r.zbd1t AS zbd1t_1,

       bseg_r.zbd2t AS zbd2t_1,

       bseg_r.zbd3t AS zbd3t_1,

       bkpf.bldat AS bldat_1

FROM bseg

INNER JOIN bkpf bkpf ON bseg.mandt = bkpf.mandt

AND bseg.bukrs = bkpf.bukrs

AND bseg.gjahr = bkpf.gjahr

AND bseg.belnr = bkpf.belnr

LEFT JOIN (bseg bseg_r

           INNER JOIN bkpf bkpf_r ON bseg_r.mandt = bkpf_r.mandt

           AND bseg_r.bukrs = bkpf_r.bukrs

           AND bseg_r.gjahr = bkpf_r.gjahr

           AND bseg_r.belnr = bkpf_r.belnr) ON bseg.mandt = bseg_r.mandt

AND bseg.bukrs = bseg_r.bukrs

AND bseg.rebzg = bseg_r.belnr

AND bseg.rebzj = bseg_r.gjahr

AND bseg.rebzz = bseg_r.buzei

LEFT JOIN knkk ON bseg.mandt = knkk.mandt

AND bseg.kunnr = knkk.kunnr

AND bseg.kkber = knkk.kkber

LEFT JOIN t014 ON bseg.mandt = t014.mandt

AND bseg.kkber = t014.kkber

INNER JOIN kna1 ON bseg.mandt = kna1.mandt

AND bseg.kunnr = kna1.kunnr

INNER JOIN (knb1 knb1

            LEFT JOIN t001s ON knb1.mandt = t001s.mandt

            AND knb1.bukrs = t001s.bukrs

            AND knb1.busab = t001s.busab) ON bseg.mandt = knb1.mandt

AND bseg.bukrs = knb1.bukrs

AND bseg.kunnr = knb1.kunnr

Am I missing something ? or Drop - Create is the only option ?

[JanStallkamp]

Hi.

Please be very, very carefull in changing database objects (tables, views...) of the SAP schema. This can lead to severe issues and you might run out of support. New objects should be always created using the SAP data dictionary (e.g. Tx SE11) and are then created on the database during 'activation' in the DDIC.

If you need to access data directly please keep in mind that SAP Sybase ASE is case sensitive: While there is not difference between 'ALTER' and 'alter' there is a difference between table 'SFLIGHT' and table 'sflight'. All SAP tables and views are using upper case. The same is true for the columns of this tables.

Regards,

Jan

[Former Member]

Dear Jan,

what shall I do in such a way with CDS-view ?

http://scn.sap.com/thread/3561112

Regards, Evgeniy

[tilman_model-bosch]

Dear Mr Seleznev,

The issue with CDS views described  in SAP note

2013665 - SYB: CDS view is not generated

The note describes solutiona adn workaround.

Rgds

Tilman

[Former Member]

dear Tilman,

thanks a lot. I coped with the situation.

Regards, Evgeniy