TAW_11 Chapter 4, ABAP open SQL Flashcards
What are the two components of the SAP database interface (DBSS)
ABAP Open SQL interface and native SQL interface
What are the tasks of the ABAP OPEN SQL interface?
Convert OPEN SSQL to native sql
Client handling
Managing the table buffer
What tcode is used for sql tracing
ST05
There are two types of database optimizers. What are they?
Rule based and Cost based
What is the recommended maximum size of a buffered table?
Less than 10mb
Will secondary indexes use the table buffers?
Nope
What table stores info for possible buffered table changes?
DDLOG
What transaction contains the buffer trace?
ST05
When can ORDER BY not be used?
Cluster and Pooled tables
What are the prereqs for ORDER BY PRIMARY KEY?
All primary key fields including MANDT must be in the field list. Only a single transparent table can be in the FROM
When using DISTINCT on pooled and cluster tables what syntax must be used?
SELECT DISTINCT *
You cannot select individual columns
What are the 5 aggregate expressions for open SQL
MAX( col ) MIN( col ) COUNT( * ) RETURNED AS TYPE I AVG( col ) RETURNED AS TYPE F SUM
Which aggregate expressions can be combined with DISTINCT?
All of them, but min, max, avg , and sum make little sense primarily it is used with count
Can a GROUP BY be used with SELECT *?
No, a field list must exist
Can group by be used with POOL or CLUSTER tables
no
What field types can not be used in GROUP BY
STRING and RAWSTRING
What fields must be listed in the group by?
All fields that are not part of the aggregate functions
Can group by contain table fields that are not part of the field list?
Yes, but it is a bad idea because it makes the results difficult to read
Can HAVING be used without GROUP BY?
No
What fields can be contained in the HAVING
Fields that are part of the GROUP BY and aggregate functions. In this case the aggregate functions will operate against the GROUPS created by the group by
The only reason to use
SELECT …. ENDSELECT is
Extremely large amounts of data that would overwhelm the app server memory
With in a SELECT … END?SELECT can I select into an internal table
Yes, if you use the package size addition
Does SAP Open SQL support UNION and UNION ALL
Yes
If in a select statement I want to combine field with a literal, what would that look like
SELECT bob && ‘name’ AS bobname
What are the new operators and functions in open sql for string expressions
&& LENGTH LPAD LTRIM RTRIM RIGHT REPLACE SUBSTRING CONCAT
Is there a difference between && and CONCAT?
&& can have multiple occurrences such as
fld1 && fld2 && fld3
CONCAT has exactly 2 occurrences
The result of all && can be 255 chars, CONCAT can be 1333 chars
&& is only used between elementary objects. CONCAT can handle string objects and string functions
&& keeps spaces in constants and lieterals, CONCAT has no special space handling
What are the new operators and functions in open SQL for arithmetic expressions
+,-,*,/
ABS, CEIL, FLOOR DIV, MOD, ROUND
If the field in the select does not have the required target type, how can i be converted?
With the CAST as type
CAST( fld1 as FLTP )
When using operator / in a select, what type do the operands need to be?
Type F
So CAST( num1 AS FLTP ) /
CAST( num2 AS FLTP) *
CAST( 100 AS FLTP ) AS fld2
Give an example of case in a select
Simple
CASE fld1
WHEN opr1 then result1
WHEN opr2 then result2
ELSE result3
END AS fld2
Complex
CASE WHEN sql_cond1 then result1
WHEN sql_cond2 then result2
ELSE result3
END AS fld2In a JOIN can fields from the right table exist in the where clause?
Yes they can now!
Is a field from the right table required for the ON of a join?
Not anymore
Must the mandt be supplied in a join
not anymore
How many tables can be in a join
50
How many tables can be in a join
50
The ON operator can only be =, is this true
Not anymore Between, less than, greater than
How is a subquery initiated?
WHERE EXISTS ( Select….) or
WHERE fld1 = ( SELECT MAX( myfld ) from …)
WHERE fld1 IN ( SELECT DISTINCT fld1 )
