Examples for SELECT Statement

This topic shows example syntax for various SELECT statements.

Sample statements

• To list details about flights listed in the table named SPFLI, use the following syntax:

  Select * from SPFLI  
  

• To store extracted data into a file named flight.txt at \\SAPServer\Extracts, use the following syntax:

  Select * Into file '\\SAPServer\Extracts\flight.txt' from SPFLI  
  

• To list details of all flights from New York to San Francisco, use the following syntax:

  Select * from SPFLI where cityfrom='NEW YORK' and cityto='SAN FRANCISCO'  
  

• To list details of all flights from New York whose connid field values are between 1000 and 5000, use the following syntax:

  Select * from SPFLI where cityfrom='NEW YORK' and (connid>1000 and connid<5000)  
  

• To list details of all flights from New York to a user-specified city, use the following syntax:

  Select * from SPFLI where cityfrom='NEW YORK' and cityto=@variable  
  

  In this instance, create an SAP parameter named @variable, specify the value, and add it to the corresponding command object.

• 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 others are considered string values and are ignored.

  • Example to demonstrate the use of percentage "%"

    SELECT NAME1, PSTLZ  from KNA1 where (MANDT between 596 AND 999) AND NAME1 LIKE '%MODE%'  
    

    Here, %MODE% fetches all records where Name1 contains the string "MODE".

  • Example to demonstrate the use of underscore "_"

    SELECT NAME1  AS [MYANME],  LAND1, KUNNR  from KNA1 where (NAME1 LIKE 'D_' )  
    

    Here, "D_" fetches all records where Name1 starts with "D" and contains two characters.

• Example to demonstrate a "between" predicate clause

  SELECT NAME1, PSTLZ  from KNA1 where (MANDT between 596 AND 999) AND NAME1 LIKE '%MODE%'  
  

• Example to demonstrate a "not between" predicate clause

  SELECT NAME1, PSTLZ  from KNA1 where (MANDT not between 596 AND 599) AND NAME1 LIKE '%MODE%'  
  

• Example for SELECT statement using Join and a TOP clause

  SELECT TOP 1 * FROM spfli INNER JOIN sflight ON spfli.mandt = sflight.mandt  
  

• Example for SELECT statement using the OPTION clause

  SELECT top 50000 * from bseg option 'batchsize 20000'