Check Variant Consistency Report

 Execute the report: RSVARDOC_NEW

Give the program to be checked (FG Master Program for example): ZPROG_XYZ and execute to see which variants needs adjustments.

SAP HANA 2.0 - Database Procedures - Snippets

DATABASE PROCEDURE USING 'FOR' TO CREATE A TABLE & USAGE OF EXIT HANDLER:

CREATE PROCEDURE ZAG_FOR_001 (out count1 integer) 

LANGUAGE SQLSCRIPT

DEFAULT SCHEMA "<ENTER_SCHEMA>"

AS

BEGIN

DECLARE i INTEGER = 0;

BEGIN

DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 288

DROP TABLE ZAG_EMP_001;

CREATE COLUMN TABLE zag_emp_001 ( empid integer, empname varchar(20), primary key(empid) );

END;

FOR i in 1..10 DO

 INSERT INTO zag_emp_001 VALUES (:i, 'Employee: ' || :i); 

END FOR;

count1 = i;

select count(*) into count1 from <ENTER.SCHEMA>.zag_emp_001;

END;

DATABASE PROCEDURE USING CURSOR:

CREATE OR REPLACE PROCEDURE ZAG_CURSOR_001(OUT ev_matnr varchar(40))

LANGUAGE SQLSCRIPT

DEFAULT SCHEMA "UMAMAHESWARA"

AS

BEGIN

DECLARE lv_matnr varchar(40);

DECLARE CURSOR c1 FOR SELECT MATNR FROM SAPHANADB.MARA;

OPEN c1;

FETCH c1 INTO lv_matnr;

select lv_matnr into ev_matnr from dummy;

CLOSE c1;

END;

DATABASE PROCEDURE USING INLINE TABLE DECLARATION:

CREATE PROCEDURE ZAG_INLINE_TABLE_001(OUT et_mara TABLE(matnr varchar(40), ERSDA date))

LANGUAGE SQLSCRIPT

DEFAULT SCHEMA "<SCHEMA_NAME>"

AS

BEGIN

et_mara = select matnr, ersda from saphanadb.mara;

END;

Now, the same with an input parameter.

CREATE OR REPLACE PROCEDURE ZAG_INLINE_TABLE_001(IN mtart varchar(4), OUT et_mara TABLE(matnr varchar(40), ERSDA date))

LANGUAGE SQLSCRIPT

DEFAULT SCHEMA "<SCHEMA_NAME"

AS

BEGIN

et_mara = select matnr, ersda from saphanadb.mara where mtart = :mtart;

END;

DATABASE PROCEDURE TO CREATE SIMPLE TABLE WITH DATA (The below code works only in HANA 2.0):

CREATE OR REPLACE PROCEDURE ZAG_SIMPLE_TABLE ( OUT mytab TABLE (sno integer, amount integer), OUT cnt integer )

LANGUAGE SQLSCRIPT

DEFAULT SCHEMA "<SCHEMA_NAME>"

AS

BEGIN

mytab.sno[1] = 1;

mytab.amount[1] = 1000;

mytab.sno[2] = 2;

mytab.amount[2] = 2000;

:mytab.insert( (3, 3000),3 );

if(is_empty(:mytab)) THEN

 SIGNAL SQL_ERROR_CODE 13000 set message_text = 'Custom text';

ELSE

 cnt = record_count(:mytab);

END IF;

END;

To execute: CALL ZAG_SIMPLE_TABLE(?)

DATABASE PROCEDURE USING 'EXECUTE IMMEDIATE':

CREATE OR REPLACE PROCEDURE ZAG_DYN_TAB_001 ( IN TABNAME VARCHAR(60) )

LANGUAGE SQLSCRIPT

DEFAULT SCHEMA "<ENTER_SCHEMA>"

AS

BEGIN

EXECUTE IMMEDIATE 'SELECT COUNT(*) AS COUNT FROM ' || :TABNAME;

END;

Explanation: EXECUTE IMMEDIATE executes the SQL statement passed in a string argument. The results of queries

executed with EXECUTE IMMEDIATE are appended to the result iterator of the procedure.

DATABASE PROCEDURE WITH A SIMPLE ARRAY:

CREATE OR REPLACE PROCEDURE ZAG_PREPARE_ARRAY ( out val integer )

LANGUAGE SQLSCRIPT

DEFAULT SCHEMA "<ENTER_SCHEMA>"

AS

BEGIN

DECLARE arr TINYINT ARRAY = array (1,2,3,4,5);

DECLARE index_arr INTEGER ARRAY = array (10,20);

arr[1] = :index_arr[1];

arr[2] = :index_arr[2];

val = :arr[1];

END;

DATABASE PROCEDURE WITH INPUT FOR A SIMPLE CURSOR:

CREATE PROCEDURE ZAG_CURSOR_INP_001(IN cat varchar(20), out pid varchar(20), out pid1 varchar(20))

LANGUAGE SQLSCRIPT

DEFAULT SCHEMA "<SCHEMA_NAME>"

AS

BEGIN

DECLARE wa varchar(20);

DECLARE wa1 varchar(20);

--Declaration of Cursor

DECLARE CURSOR c1 (icat varchar(20)) FOR SELECT MATNR FROM SAPHANADB.MARA

                                                          WHERE MTART = :icat;

OPEN c1(:cat);

--Read table itab into wa index 1

FETCH c1 INTO wa;

pid = wa;

--Read table itab into wa index 2

FETCH c1 INTO wa1;

pid1 = wa1;

CLOSE c1;

END;

DATABASE PROCEDURE WITH CURSOR AND FOR:

CREATE OR REPLACE PROCEDURE ZAG_CURSOR_FOR_001(IN cat varchar(20), out pid varchar(20), out pid1 varchar(20))

LANGUAGE SQLSCRIPT

DEFAULT SCHEMA "<SCHEMA_NAME>"

AS

BEGIN

DECLARE wa varchar(20);

DECLARE allProds varchar(20) ARRAY;

--Declaration of Cursor

DECLARE CURSOR c1 (icat varchar(20)) FOR SELECT MATNR FROM SAPHANADB.MARA

                                                          WHERE MTART = :icat;

FOR wa AS c1(:cat) DO

 allProds[c1::rowcount] = wa.matnr;

END FOR;

pid = :allProds[1];

pid1 = :allProds[2];

END;

DATABASE PROCEDURE WITH CURSOR, FOR and UNNEST Function:

UNNEST Function is used to convert ARRAY to a TABLE

CREATE OR REPLACE PROCEDURE ZAG_CURSOR_FOR_002(IN cat varchar(20), out pid table(matnr varchar(20)))

LANGUAGE SQLSCRIPT

DEFAULT SCHEMA "<SCHEMA_NAME>"

AS

BEGIN

DECLARE wa varchar(20);

DECLARE allProds varchar(20) ARRAY;

--Declaration of Cursor

DECLARE CURSOR c1 (icat varchar(20)) FOR SELECT MATNR FROM SAPHANADB.MARA

                                                          WHERE MTART = :icat;

FOR wa AS c1(:cat) DO

 allProds[c1::rowcount] = wa.matnr;

END FOR;

--UNNEST Function - Helps to convert an ARRAY to a TABLE

pid = UNNEST(:allprods) as (matnr);

END;

DATABASE PROCEDURE WITH CURSOR, FOR and UNNEST Function - With Multiple ARRAYs:

CREATE OR REPLACE PROCEDURE ZAG_CURSOR_FOR_003(IN cat varchar(20), 

out pid table(Material varchar(40), MaterialType varchar(4)) )

LANGUAGE SQLSCRIPT

DEFAULT SCHEMA "<SCHEMA_NAME>"

AS

BEGIN

DECLARE wa varchar(20);

DECLARE allProds varchar(40) ARRAY;

DECLARE mattype varchar(4) ARRAY;

--Declaration of Cursor

DECLARE CURSOR c1 (icat varchar(20)) FOR SELECT MATNR, MTART FROM SAPHANADB.MARA

                                                          WHERE MTART = :icat;

FOR wa AS c1(:cat) DO

 allProds[c1::rowcount] = wa.matnr;

 mattype[c1::rowcount] = wa.mtart;

END FOR;

--UNNEST Function - Helps to convert an ARRAY to a TABLE

pid = UNNEST(:allprods, :mattype) as (Material, MaterialType);

END;

DATABASE PROCEDURE TO DISPLAY TABLE DATA IN OUTPUT:

Relationship: OData Operation, HTTP Method and ABAP Statement (General Methods used during OData Service Development)

The table below shows the relationship between an OData operation, the corresponding HTTP method call and the corresponding ABAP statement call. These methods are mostly used by the Developer to implement their own functionality in the corresponding DPC extension class.

OData Operation	HTTP Method	ABAP Statement	Comments	Front-end method
Create (CREATE_ENTITY)	POST	INSERT		Call create( )
Read (GET_ENTITY)	GET	SELECT SINGLE *		Call bindElement( )
Read (GET_ENTITYSET)	GET	SELECT		Call bindRows( )
Update (UPDATE_ENTITY)	PUT	UPDATE		Call update( )
Delete (DELETE_ENTITY)	DELETE	DELETE		Call remove( )
Function Import	GET, POST	SELECT, INSERT	Custom Operations	Call callfunction( )
Create File (CREATE_STREAM)	POST	INSERT	Upload a file to a database table
Get File (GET_STREAM)	GET	SELECT	Download a file
Update File (UPDATE_STREAM)	PUT	UPDATE	Update the file content in a database table
Delete a File (DELETE_STREAM)	DELETE	DELETE	Delete the file content in a database table
Execute Action (EXECUTE_ACTION)	GET / POST	SELECT / INSERT
GET_EXPANDED_ENTITY	GET	SELECT	Select a record based on a specific field/ID
GET_EXPANDED_ENTITYSET	GET	SELECT	Select item records based on a specific header record

Note:

The above methods are available in the DPC class.

Tip:

Where to find the DPC / Data Provider Class?

Answer: Go to SEGW transaction, open your Project for which the generation is done, open the Runtime Artifacts to navigate to the Class.

Useful Transaction Codes / Tools in OData ABAP Service / SAP UI5/ SAP Fiori

Tips: 

1) FND stands for 'Front End'

2) GW stands for Gate Way / Gateway

S. No.	Transaction Code	Description
1	SEGW	SAP Service Gateway Builder
2	/IWFND/ERROR_LOG	SAP Gateway Error Log
3	/IWFND/APPS_LOG	SAP Gateway Application Log Viewer
4	/IWFND/VIEW_LOG	SAP Gateway Application Log Viewer
5	/IWFND/CACHE_CLEANUP	Cleanup of GW Model Cache - GW stands for Gateway
6	/IWFND/TRACES	SAP Gateway Traces
7	/IWFND/SRV_VALIDATE	This transaction exists only on SAP Gateway 2.0 (Netweaver 7.31 and below) systems
8	/IWFND/REG_SERVICE	Activate Services (Unavailable in latest releases)
9	/IWFND/MAINT_SERVICE	Activate and Maintain Services
10	/IWFND/VIRUS_SCAN	Configuration of SAP GW Virus Scan
11	SICF	Edit HTTP Service Hierarchy
12	RZ20	CCMS Monitoring
13	PFCG	Role Maintenance
14	SPRO	Customizing - Execute Project
15	ST22	ABAP Dump Analysis
16	LPD_CUST	Launchpad customizing
17	/UI5/THEME_TOOL	UI Theme Tool
18	/UI5/THEME_DESIGNER	UI Theme Designer

OData Supported Data Types

OData ABAP service data types are also knows as 'EDM' data types.

The supported data types are:

1. EDM.Binary > Fixed or variable length binary data
2. EDM.Boolean > To represent binary value logic (True/False)
3. EDM.Byte > Unsigned 8-bit integer value
4. EDM.DateTime > Date and time values
5. EDM.Decimal > Numeric values with fixed precision and scale
6. EDM.Double > Floating point number with 15 digit precision
7. EDM.Float> Floating point number with 7 digit precision
8. EDM.Sbyte > Signed 8-bit integer value
9. EDM.Int16 > Signed 16-bit integer value
10. EDM.Int32 > Signed 32-bit integer value
11. EDM.Int64 > Signed 64-bit integer value
12. EDM.Single > Floating point number with 7-digit precision
13. EDM.String > Fixed or variable length character data
14. EDM.Time > Time of the day with values ranging from 00:00:00 to 23:59:59

The above are not ABAP Data Types. They are OData service data types used at OData level, and are called EDM data types.

EDM stands for Entity Data Model.

Get structure names used in a database table

Aim:
To get the structure name of the .INCLUDE structures used in a database table.
Tip: Navigate to PRECFIELD field in the output to see the Structure name of the include

Report:
REPORT zag_test_extract_structures.

PARAMETERS: p_table TYPE tabname16 DEFAULT 'EKKO'.

DATA: lt_fields TYPE TABLE OF dd03p,
      lv_name   TYPE ddobjname.
DATA(lref_structype) = cl_abap_typedescr=>describe_by_name( p_table ).
lv_name = p_table.

CALL FUNCTION 'DDIF_TABL_GET'
  EXPORTING
    name          = lv_name
  TABLES
    dd03p_tab     = lt_fields
  EXCEPTIONS
    illegal_input = 1
    OTHERS        = 2.
IF sy-subrc = 0.
  CALL METHOD cl_demo_output=>display_data
    EXPORTING
      value = lt_fields
      name  = 'Navigate to PRECFIELD field to see the Structure name of the include'.
ELSE.
  WRITE 'Error'.
ENDIF.

<End of Document>

Table fetch tips

1) How to get Vendor/Plant combination data?

Ans: Inner join between tables LFM1 and T024W