Select where Bedingung mit @

Select where Bedingung mit @

Ralf Wenzel hat geschrieben:

Code: Alles auswählen.

SELECT scarr~carrname, spfli~connid, sflight~fldate 
INTO CORRESPONDING FIELDS OF TABLE @itab FROM ( ( scar 
INNER JOIN spfli 
  ON spfli~carrid   = scarr~carrid 
  AND spfli~cityfrom = @p_cityfr 
  AND spfli~cityto   = @p_cityto ) 
INNER JOIN sflight 
  ON sflight~carrid = spfli~carrid 
  AND sflight~connid = spfli~connid ). 

Re: Select where Bedingung mit @

SAP_DOKU hat geschrieben:Open SQL in Release 7.40, SP05

1. Comma-separated lists

2. Escape character for host variables

3. SQL expressions

4. Switching automatic client handling

5. Rule changes for joins

6. Evaluating INTO CORRESPONDING

7. Access to CDS views

8. Stricter checks for syntax rules

9. Strict mode in the syntax check


Modification 1

Comma-Separated Lists



In Open SQL, all operands in lists can now be separated by commas and this is also the recommended way of separating them from Release 7.40, SP05. Until now, comma-separated lists could only be used when single target fields were specified in parentheses after INTO in SELECT and when data objects were specified in parentheses after WHERE. Comma-separated lists are now also possible in Unicode programs where the program attribute fixed point arithmetic is activated:
• In the statement SELECT

◦When columns, aggregate expressions, or SQL expressions are specified in the SELECT list
◦When columns are specified after GROUP BY
◦When columns are specified after ORDER BY
• In the statement UPDATE

◦When change expressions are specified after SET

This makes blank-separated lists obsolete. If one of these lists is separated by commas, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.

The constraint that blanks were forbidden after the opening parenthesis in comma-separated lists after INTO and IN if more than one comma occurs (if more than one data object is specified) no longer applies from SP05.





Modification 2

Escape Character for Host Variables



ABAP data objects used in Open SQL statements (usually variables) are now interpreted as host variables, as in statically embedded Native SQL. From Release 7.40, SP05, host variables can and should be prefixed with the escape character @. Host variables without the escape character are obsolete. If the escape character is used in front of a name of an Open SQL statement, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.

The escape character can only be used in Unicode programs, in which the program property fixed point arithmetic is activated. Using the escape character requires a strict syntax check of the complete statement. During this check, any errors that would normally only be displayed as syntax warnings are reported as syntax errors.





Modification 3

SQL Expressions



From Release 7.40, SP05, SQL expressions can be specified in a comma-separated SELECT list. The result of an expression of this type (whose operands can be the names of columns or host variables) is determined by the database system and passed to the application server in the appropriate column of the results set.

SQL expressions can only be used in Unicode programs, in which the program attribute fixed point arithmetic is activated. If SQL expressions are used, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.





Modification 4

Switching Automatic Client Handling



The new addition USING CLIENT clnt, which can be specified in all Open SQL statements as an alternative to CLIENT SPECIFIED, switches automatic client handling to the client specified in clnt.

When the addition USING CLIENT is used, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.





Modification 5

Rule Changes for Joins



The following previous constraints on joins have been lifted:
• From Release 7.40, SP05, it is no longer the case that all comparisons of the ON condition must contain a column from a database table or view on the right side as an operand.


• From Release 7.40, SP05, the right side of a join expression is no longer restricted to single tables or views. The right side can itself be a (parenthesized) join expression whose results set is then evaluated.


• In Release 7.40, SP05 and higher, LEFT OUTER JOIN and RIGHT OUTER JOIN can be used., ,


• From Release 7.40, SP05, fields from the right side in the WHERE condition of the current SELECT statement can be specified in LEFT OUTER JOIN. Fields from the left side can be specified in RIGHT OUTER JOIN.


In SELECT statements that exploit the rule changes above, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.





Modification 6

Evaluating INTO CORRESPONDING



If all required components are statically identifiable, the assignment of the fields in the addition CORRESPONDING after INTO is now determined when the program is generated and is not delayed until runtime.

The addition INTO CORRESPONDING now affects the results set defined in the SELECT list. If one or more names match, all the columns for which there are no name matches are removed from the results set. If there are no name matches, none of the columns are removed from the results set.





Modification 7

Access to CDS Views



SELECT can be used to access CDS views, which have been defined with the DDL of the ABAP CDS by using DEFINE VIEW.

Automatic client handling is performed for client-specific CDS views. If this handling is deactivated with addition CLIENT SPECIFIED, the client column is part of the results set, even though the column is not an element of the CDS view. The new addition CLIENT SPECIFIED of statement TYPES can be used to declare a suitable target area. New additions for CLIENT SPECIFIED after FROM make it possible to address the column in the SELECT statement.

If the real name cdsViewName is used for accesses (as recommended), the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.





Modification 8

Stricter Checks on Syntax Rules



In Release 7.40, SP02, a new SQL parser was introduced for Open SQL. These parser performs stricter checks on some rules than the old parser. More specifically, the same parser is now used for statically specified Open SQL and for the content of dynamic tokens. In Release 7.40, SP02, this parser will initially only be used for the statement SELECT. From Release 7.40, SP05, the new parser will be used for all Open SQL statements. One consequence of this is that any following syntax constructs that have always contained errors now produce syntax errors or runtime errors.
•Corrections for the WHERE Condition
All corrections in Release 7.40, SP02 that apply to the WHERE condition now also apply to the statements DELETE, OPEN CURSOR, and UPDATE from Release 7.40, SP05.

Example

From Release 7.40 SP05, syntax errors for:

DELETE FROM spfli WHERE NOT NOT carrid = 'LH'. •Corrections for dynamic tokens
All corrections in Release 7.40, SP02 that apply to dynamic tokens now also apply to all Open SQL statements from Release 7.40, SP05.

Example

From Release 7.40 SP05, exception for:

DELETE FROM (`SPFLI .`) WHERE (`. CARRID = 'LH'`). •Correction for OPEN CURSOR
The addition WITH HOLD of the statement OPEN CURSOR can be used only in reads performed on the standard database. If the addition CONNECTION is specified at the same time, a runtime error was produced before Release 7.40 SP05 (and not a syntax error), if the database table was specified dynamically. This gap was closed in Release 7.40 SP05.

Example

From Release 7.40 SP05, syntax errors for:

OPEN CURSOR WITH HOLD cursor
FOR SELECT *
FROM ('SPFLI') CONNECTION con.




Modification 9

Strict Mode in the Syntax Check



If one the new features listed here is used in an Open SQL statement, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.