Skip to main content

SQL Reference

This section provides a complete reference for all ERPL SQL functions and their usage.

SAP Connection Functions​

sap_connect​

Establishes a connection to an SAP ERP system.

SELECT sap_connect(
    host = 'sap-server.com',
    system_number = '00',
    client = '100',
    user = 'username',
    password = 'password'
);

Parameters:

  • host: SAP application server hostname or IP address
  • system_number: Two-digit SAP system number
  • client: Three-digit SAP client number
  • user: SAP username
  • password: SAP password

Returns: Connection status message

sap_disconnect​

Closes the connection to SAP system.

SELECT sap_disconnect();

Returns: Disconnection status message

Table Access Functions​

sap_show_tables​

Lists available SAP tables.

SELECT * FROM sap_show_tables();
SELECT * FROM sap_show_tables(TABNAME='*CUSTOMER*');

Parameters:

  • TABNAME: Optional table name pattern (supports wildcards)

Returns: Table of available SAP tables with metadata

sap_show_table_columns​

Shows column information for a specific SAP table.

SELECT * FROM sap_show_table_columns(TABNAME='KNA1');

Parameters:

  • TABNAME: SAP table name

Returns: Table of column information

sap_read_table​

Reads data from an SAP table.

SELECT * FROM sap_read_table('KNA1');
SELECT * FROM sap_read_table('KNA1', WHERE='LAND1 = ''DE''');

Parameters:

  • table_name: SAP table name
  • WHERE: Optional WHERE clause for filtering

Returns: Table data from SAP

BAPI Function Calls​

sap_call_function​

Calls SAP BAPI functions.

SELECT * FROM sap_call_function(
    function_name = 'BAPI_CUSTOMER_GETDETAIL2',
    parameters = {
        'CUSTOMERNO': '0000001000'
    }
);

Parameters:

  • function_name: BAPI function name
  • parameters: Dictionary of function parameters

Returns: Function results and return messages

BW Query Functions (BICS Extension)​

bics_list_queries​

Lists available BW queries.

SELECT * FROM bics_list_queries();
SELECT * FROM bics_list_queries(query_name='*SALES*');

bics_get_query_info​

Gets metadata for a specific BW query.

SELECT * FROM bics_get_query_info('Z_SALES_ANALYSIS');

bics_execute_query​

Executes a BW query.

SELECT * FROM bics_execute_query(
    query_name = 'Z_SALES_ANALYSIS',
    variables = {
        'P_CALMONTH': '202401'
    }
);

ODP Functions (ODP Extension)​

odp_list_extractors​

Lists available ODP extractors.

SELECT * FROM odp_list_extractors();

odp_extract_data​

Extracts data using ODP.

SELECT * FROM odp_extract_data('MCHB');

odp_extract_delta​

Performs delta extraction.

SELECT * FROM odp_extract_delta(
    extractor_name = 'MCHB',
    delta_token = 'token_value'
);

Utility Functions​

sap_ping​

Tests SAP connection.

SELECT sap_ping();

Returns: Connection status ('OK' or error message)

Error Handling​

All ERPL functions return structured error information when failures occur:

  • Error Messages: Descriptive error text
  • Error Codes: Numeric error codes
  • Parameter Information: Details about failed parameters

Performance Tips​

  1. Use Filters: Always use WHERE clauses to limit data
  2. Selective Columns: Only retrieve needed columns
  3. Connection Pooling: Reuse connections when possible
  4. Batch Processing: Process large datasets in chunks

Examples​

Basic Table Access​

-- Connect to SAP
SELECT sap_connect(
    host = 'sap-server.com',
    system_number = '00',
    client = '100',
    user = 'user',
    password = 'pass'
);

-- List available tables
SELECT * FROM sap_show_tables(TABNAME='*KNA1*');

-- Read customer data
SELECT 
    KUNNR as customer_number,
    NAME1 as customer_name,
    LAND1 as country
FROM sap_read_table('KNA1')
WHERE LAND1 = 'DE'
LIMIT 100;

BAPI Function Call​

-- Call customer detail BAPI
SELECT * FROM sap_call_function(
    function_name = 'BAPI_CUSTOMER_GETDETAIL2',
    parameters = {
        'CUSTOMERNO': '0000001000'
    }
);

BW Query Execution​

-- Execute sales analysis query
SELECT 
    customer,
    product,
    sales_amount
FROM bics_execute_query(
    query_name = 'Z_SALES_ANALYSIS',
    variables = {
        'P_CALMONTH': '202401',
        'P_REGION': 'EUROPE'
    }
);

Troubleshooting​

Common Issues​

  1. Connection Failures: Check network connectivity and SAP system status
  2. Permission Errors: Verify user authorizations
  3. Table Not Found: Confirm table name and client
  4. Performance Issues: Use filters and limit result sets

Debugging​

-- Enable detailed logging
SET log_level = 'debug';

-- Test connection
SELECT sap_ping();

-- Check available tables
SELECT * FROM sap_show_tables() LIMIT 10;

For more detailed information, see the troubleshooting guide.