Export (0) Print
Expand All

Syntax for a SELECT Statement

The following sections describe grammar specifications for implementing SELECT queries against the .NET Framework Data Provider for mySAP Business Suite. Notice that in several cases, the syntax is somewhat different from the base Transact-SQL syntax.

SELECT {TOP <const> }[0,1] <select_list>  {INTO FILE [‘file_name’ | “file_name”] {DELIMITED}[0,1]}[0,1]  FROM table_name  {AS alias_name }[0,1]  { INNER JOIN table_name {AS alias_name}[0,1] ON  <Join_Condition>}[0,1]{ WHERE <predicate> } [0,1] {;}[0,1] OPTION <option_list>

Where:

  • <select_list> = [ {table_name.}[0,1]column_name { AS alias_name } [0,1] } [ 1, …n ]

  • <Join_Condition> = [Alias_name.|table_name.]column_name <expr> [Alias_name.|table_name.]column_name

  • <predicate> = [ predicate [AND|OR] predicate [between|not between] predicate | NOT predicate | ‘(‘ predicate ‘)’ | condition ]

The supported conditions and expressions are:

  • <condition> = [ expr | expr [NOT | ] BETWEEN const AND const | expr [NOT | ] LIKE const ]

  • <expr> = [ const | column_name [= | ! = | > | > = | ! > | < | < = | ! < ] const | column_name | - const | const | column_name ]

Where:

  • <const> = integer | real | string | ? | NULL | xml_element

  • <option_list> = <option> [, <option>]

    where <option> = 'no_conversion' | 'batchsize <size>' | disabledatavalidation

    • When set to no_conversion, the result for the SELECT statement has the same value as available in the SAP backend. The SAP ADO provider does not convert those values to any other format based on the user settings.

    • When set to batchsize <size>, the SELECT statement retrieves only those numbers of record as specified for the batch size. For example, if you specify this as 'batchsize 100', the SELECT query only retrieves the records in batches of 100. Note that you should specify an optimum value for the batch size based on the physical memory of the computer and the number of rows in the SAP backend. Default batch size is 10000 rows. Failure in specifying an optimum value for batch size may result in out of memory exceptions.

    • When set to disabledatavalidation, the Data Provider for SAP does not validate the values present in the DATS, TIMS, and NUMC columns but instead exposes them as string.

      This is useful in scenarios where ADO.NET clients fail to retrieve data from an SAP table having invalid data in DATS, TIMS, and NUMC columns. Because SAP allows invalid data to be present in DATS, TIMS, and NUMC columns, ADO.NET clients attempting to read the data will not be able to parse the invalid data and will throw an exception. If the disabledatavalidation option is set on the SELECT query, the Data Provider for SAP does not parse the invalid data but extracts them as strings.

For example statements, see Examples for SELECT Statement.

The following specifies the grammar for using predicates in a SELECT statement:

Predicate:

Predicates [AND | OR] Predicates [between|not between] Predicates | [NOT] Predicates | '(' Predicates ')' | Condition

Condition:

Expr | LExpr [NOT] BETWEEN RExpr AND RExpr | LExpr [NOT] LIKE Const

Expr:

LExpr [=|!=|>|>=|!>|<|<=|!<] RExpr>

LExpr:

ColumnName

RExpr:

Const | PlaceHolder

ColumnName:

Column | TableName.Column | '['Column']'

Tablename:

Table | '['Table']'

This section lists the points that you must keep in mind when using the SELECT statement with Data Provider for SAP.

  • When specifying date-time values in parameters or queries, provide the values as a string. Provide date-time strings in the SAP date-time format.

    • SAP date format: YYYYMMDD

      For example, the date 2004 November 10 in a SAP query is expressed '20041110'.

      The date 2004 November 10 in a SAPParameter p1 is the string p1.Value='20041110'.

    • SAP time format: HHMMSS

      For example, the time 10:34:32 in a SAP query is expressed '103432'.

      The time 10:34:32 in a SAPParameter p2 is the string p2.Value='103432'.

    For a SELECT statement, the Data Provider for SAP returns DATE field values as .NET System.DateTime objects, and returns TIME field values as System.TimeSpan objects. If the value of the SAP DATE object is less than the minimum allowable SQL Server 2005 value (1/1/1753), the Data Provider for SAP returns this minimum value, 1/1/1753.

  • In the TOP clause of a SELECT query, when using the special characters "#", "^", "&", and "%" before or after an integer, the special characters are ignored, although no error message is raised. For example, the following are ignored in the TOP clause of a SELECT query:

    #5, 5^, %5%, or &5

    However, using these characters between integers, as in 5$5, does throw an error.

  • Column names returned in a schema for a table are returned as all uppercase characters. For example, a query result set on the field Last Name returns the column heading LAST NAME. To avoid uniqueness conflicts, using all uppercase in SELECT statements is recommended.

  • In the LIKE clause of a SELECT query, only the percent sign, "%" (for any string of zero or more characters), and underscore, "_" (for any single character), are allowable special characters. All other special characters are considered string values and are ignored.

  • The Data Provider for SAP uses the Z_EXTRACT_DATA_OO RFC to perform SELECT queries on the SAP system. The RFC supports reading data from tables that satisfy the following conditions:

    • TabClass for the table is TRANSP, CUSTER, or POOL.

    • TabClass is VIEW and ViewClass is either D or P.

    Make sure the SELECT statement reads data from tables that satisfy these conditions.

  • Values of data types that can take more than 255 characters, for example STRING, RAWSTRING, LRAW, VARC, and LCHAR cannot be used in a SELECT query. The Data Provider for SAP uses a custom RFC shipped with the SAP adapter, Z_EXTRACT_DATA_OO, to perform SELECT queries on the SAP system. This custom RFC does not support any data type that can take more than 255 characters.

  • The maximum number of columns or fields that are supported in a SELECT statement is 1000.

  • The maximum number of predicates supported in a WHERE clause is 100.

  • Selecting the same field multiple times in the same SELECT statement is not supported. The custom RFC (Z_EXTRACT_DATA_OO) used by the Data Provider for SAP removes the duplicate fields from the statement before executing. For example, this statement:

    SELECT BUKRS, BUKRS, BUKRS from T001

    executes as though written like this statement:

    SELECT BUKRS from T001

  • Data Provider for SAP does not support duplicate alias names in a SELECT statement. Therefore, the following SELECT statement throws an error:

    SELECT KUNNR AS [MYKNA1], JMJAH AS MYKNA1 from KNA1 where KUNNR LIKE 'T-S62A08' AND JMJAH=1995
    
  • Data Provider for SAP does not support a SELECT statement with duplicate column names. Therefore, the following SELECT statement throws an error:

    SELECT KUNNR AS [MYKNA1], KUNNR AS MYKNA2 from KNA1 where MYKNA2='T-S62A08'
    
  • SAP does not store NULL values in the tables. As a result, the Data Provider for SAP does not support an "IS NULL" value in a SELECT statement. Therefore, the following SELECT statement throws an error:

    SELECT NAME1, PSTLZ  from KNA1 where CITY IS NULL AND NAME1 LIKE '%MODE%'
    
  • Data Provider for SAP does not support an ORDER BY clause in a SELECT statement. Therefore, the following SELECT statement throws an error:

    SELECT NAME1  AS [MYNAME],  LAND1, KUNNR  from KNA1 where NAME1 LIKE '%MODE%'  ORDER BY NAME1  ASC
    
  • Data Provider for SAP does not support specifying an asterisk (*) to select all the fields in an SAP table. Therefore, the following SELECT statement throws an error:

    SELECT spfli.* from spfli inner join sflight on spfli.carrid = sflight.carrid
    
    To select all the fields, you must specify the field names individually.

  • As part of the SELECT statement, you can specify a file to which the output of the SELECT statement will be written. However, if the output file is on a network share, make sure the SAP service account under which the SAP service is running has write permission to the network share. For example:

    SELECT * into file '\\share\output.txt' from spfli inner join sflight on spfli.carrid = sflight.carrid
    
    In the preceding example, the SAP service account must have write permission to the network share with the name "share."

  • A SELECT statement using Data Provider for SAP supports parameter names for argument values in a SELECT query. However, make sure you follow these rules with respect to parameter names:

    • In the SELECT query, an "@" symbol must precede the parameter name.

    • The "@" symbol must be followed by an alphabetic character (A-Z or a-z).

    • The parameter name can contain alphanumeric characters (A-Z, a-z, or 0-9) and special characters. The only special characters that can be included in the parameter name are underscore "_" and hash "#".

  • When you run a SELECT query on a View, make sure that all the primary key columns of the base table are also present in the View. Also, as a practice, you must mark the columns as primary key columns in the View also.

    If the primary key columns are not present in the View, a SELECT query on the View will result in an exception.

  • When you run a SELECT query on a table to select fields of type LRAW, make sure you select the corresponding PREC field. Also, the PREC field must appear immediately before the LRAW field in the SELECT clause.

    Every LRAW field in a table has a corresponding PREC field that stores the length of data in the LRAW field. Specifying just the LRAW field in the SELECT clause without the PREC field may result in incorrect data being extracted.

  • In an SAP system, character comparisons are case sensitive. So, the following two queries may return different results.

    SELECT * FROM KNA1 WHERE LAND1 LIKE 'D%'
    
    And

    SELECT * FROM KNA1 WHERE LAND1 LIKE 'd%'
    
    Make sure you use the right cases when framing a select query. Also, in an SAP system, not all columns can contain lowercase or uppercase characters. You can use the SAP GUI to find out whether a column in a table stores lowercase or uppercase characters. For instructions on using the SAP GUI, see Determining Whether a Column Stores Lowercase or Uppercase Values.

  • The WHERE condition is supported only for comparison of a field value with some data value, and not with some other table field value. Because the Data Provider for SAP supports only one table SELECT query, table field queries in join conditions should use the join condition to support the same.

  • A join condition must contain a table name.

    The following is a correct SELECT statement

    select A.x, B.y from A inner join B on A.m = B.n
    
    The following is an incorrect SELECT statement

    select A.x, B.y from A inner join B on m = n
    
  • In a join condition, the left table in a join condition should be on the left side of the condition, and the right table of the join condition should be specified on the right side of the join condition.

    The following is the correct way of specifying a join condition:

    select A.x, B.y from A inner join B on A.m = B.n
    
    The following is an incorrect way of specifying a join condition:

    select A.x, B.y from A inner join B on B.n = A.m
    
  • A SELECT statement can only contain an “equal to” condition in a JOIN clause. For example:

    select * from spfli inner join sflight on spfli.carrid = sflight.carrid
    
  • A SELECT statement does not retrieve columns of type STRING from the SAP system.

  • A SELECT statement must contain only a single JOIN. For example:

    select * from spfli inner join sflight on spfli.carrid = sflight.carrid
    
Show:
© 2015 Microsoft