CDS view with parameter
Sample CDS view with parameter for sales order.
- Declaring parameter using with parameter keyword
- There are various inbuild functions supported by cds views, here we use currency_conversion inbuild function to achieve currency conversion.
- @AbapCatalog.sqlViewName: 'ZSALORDER_PARAMV'
- @AbapCatalog.compiler.compareFilter: true
- @AbapCatalog.preserveKey: true
- @AccessControl.authorizationCheck: #CHECK
- @EndUserText.label: 'Currency conversion using parameter'
- define view zsalesorder_parameter
- //declaring parameter
- with parameters
- target_currency : abap.cuky,
- exchange_data : abap.dats
- as select from vbak as SalesHeader left outer join vbap as SalesItem on SalesHeader.vbeln = SalesItem.vbeln
- {
- SalesItem.vbeln as SalesOrder,
- SalesItem.posnr as Item,
- SalesItem.erdat as OrderDate,
- SalesItem.matnr as Material,
- //Establishing FK relationship
- @DefaultAggregation: #SUM
- @Semantics.amount.currencyCode: 'Currency'
- SalesItem.netwr as ActualPrice,
- @Semantics.amount.currencyCode:true
- SalesItem.waerk as Currency,
- //Concatenate actual price and currency
- concat(cast(SalesItem.netwr as abap.char( 20 )), SalesItem.waerk) as PriceCurrency,
- //Logic for currency conversion
- currency_conversion( amount => SalesItem.netwr, source_currency => SalesItem.waerk, target_currency => $parameters.target_currency, exchange_rate_date => $parameters.exchange_data ) as ConvertedValue,
- $parameters.target_currency as ConvertedCurrency
- }
- Similarly there are various inbuild functions available like
- //inbluid func to calculate days between two date
- dats_days_between(SalesItem.erdat, $parameters.exchange_data) as DaysBetween.
- There is no message classes in CDS view. we cannot display any message using CDS views
Conversion Functions
The following table shows the conversion functions supported by ABAP CDS and Open SQL. The last two columns indicate where a function can be used.
SQL Function | Result | ABAP CDS | Open SQL |
FLTP_TO_DEC( arg AS dtype ) | Conversion of an argument of type FLTP to a packed number. | x | - |
UNIT_CONVERSION( p1 => a1, p2 => a2, ... ) | Conversion of units. | x | - |
CURRENCY_CONVERSION( p1 => a1, p2 => a2, ... ) | Conversion of currencies. | x | - |
DECIMAL_SHIFT( p1 => a1, p2 => a2, ... ) | Setting the decimal separator. | x | - |
SQL Functions for Byte Strings
The following table shows the SQL functions for byte strings supported by ABAP CDS and Open SQL. The last two columns indicate where a function can be used.
SQL Function | Result | ABAP CDS | Open SQL |
BINTOHEX(arg) | Character string containing the half bytes arg converted to the hexadecimal characters "0" to "9" and "A" to "F" (left-justified). | x | - |
HEXTOBIN(arg) | Byte string whose half bytes are determined from the hexadecimal characters in arg. Any leading blanks are removed before the conversion from arg and all trailing blanks are then replaced by "0". | x | - |
Date Functions and Time Functions
The following table shows the date and time functions supported by ABAP CDS and Open SQL. The last two columns indicate where a function can be used.
SQL Function | Result | ABAP CDS | Open SQL |
DATS_IS_VALID( date ) | Shows whether the argument is a valid date. | x | x |
DATS_DAYS_BETWEEN( date1, date2 ) | Difference between two dates. | x | x |
DATS_ADD_DAYS( date, days , on_error ) | Total of days and a date. | x | x |
DATS_ADD_MONTHS ( date, months, on_error ) | Total of months and a date. | x | x |
TIMS_IS_VALID( time ) | Shows whether the argument is a valid time. | x | - |
TSTMP_IS_VALID( tstmp ) | Shows whether the argument is a valid time stamp. | x | - |
TSTMP_CURRENT_UTCTIMESTAMP( ) | Current UTC time stamp. | x | - |
TSTMP_SECONDS_BETWEEN ( tstmp1, tstmp2, on_error ) | Difference between two time stamps in seconds. | x | - |
TSTMP_ADD_SECONDS( tstmp, seconds, on_error ) | Total of seconds and a time stamp. | x | - |
TSTMP_TO_DATS( tstmp, tzone, clnt, on_error ) | Local date of a time stamp. | x | - |
TSTMP_TO_TIMS( tstmp, tzone, clnt, on_error ) | Local time of a time stamp. | x | - |
TSTMP_TO_DST( tstmp, tzone, clnt, on_error ) | Local summer time marker of a time stamp. | x | - |
DATS_TIMS_TO_TSTMP( date, time, tzone, clnt, on_error ) | Time stamp for a local date and a local time. | x | - |
ABAP_SYSTEM_TIMEZONE( clnt, on_error ) | System time zone of AS ABAP. | x | - |
ABAP_USER_TIMEZONE( user, clnt, on_error ) | User time zone of AS ABAP. | x | - |
SQL Functions for Numeric Values
The following table shows the numeric SQL functions supported by ABAP CDS and Open SQL. The last two columns indicate where a function can be used.
SQL Function | Result | ABAP CDS | Open SQL |
ABS(arg) | Absolute amount of arg. | x | x |
CEIL(arg) | Smallest integer number not less than the value of arg. | x | x |
DIV(arg1, arg2) | Integer part of the division of arg1 by arg2 The sign is assigned after the amounts are divided; positive if the arguments have the same sign, and negative if the arguments have different signs. Exception: arg2 has the value 0. | x | x |
DIVISION(arg1, arg2, dec) | Division of arg1 by arg2 The result is rounded to dec decimal places. | x | x |
FLOOR(arg) | Largest integer number not greater than the value of arg. | x | x |
MOD(arg1, arg2) | Positive or negative integer remainder of the division of arg1 by arg2. | x | x |
ROUND(arg, pos) | Rounded value of arg. If pos is greater than 0, the value is rounded to the position pos on the right of the decimal separator. If this is not the case, position abs(pos)+1 to the left of the decimal separator is rounded. This results in a 0 if the number of places is not sufficient. | x | x |
SQL Functions for Strings
The following table shows the SQL functions for strings supported by ABAP CDS and Open SQL. The last two columns indicate where a function can be used.
SQL Function | Result | ABAP CDS | Open SQL |
CONCAT( arg1, arg2 ) | Chaining of character strings in arg1 and arg2. Trailing blanks in arg1, arg2, and in the result are ignored. The maximum length of the result is 1333. | x | x |
CONCAT_WITH_SPACE( arg1, arg2, spaces ) | Concatenation of strings in arg1 and arg2 as with CONCAT. The number of blanks specified in spaces is inserted between arg1 and arg2. The maximum length of the result is 1333. | x | x |
INSTR( arg, sub ) | Position of the first occurrence of the string from sub in arg (case-sensitive). arg respects leading blanks and ignores trailing blanks. sub respects all blanks. sub must contain at least one character. If no occurrences are found, the result is 0. | x | x |
LEFT( arg, len ) | String of the length len with the len left characters of arg (ignoring the trailing blanks). The value of len cannot be greater than the length of arg. | x | x |
LENGTH( arg ) | Number of characters in arg ignoring trailing blanks. | x | x |
LOWER( arg ) | String with a length of arg, in which all upper and lowercase characters are have been converted. | x | x |
LPAD( arg, len, src ) | String of the length len with the right-justified content of arg without trailing blanks and in which leading blanks produced by the expanded string are replaced by the characters from the argument src (respecting all blanks). Trailing blanks from arg are preserved. If more characters are required than exist in src, the content of src is used repeatedly. If len is less than the length of arg, it is truncated on the right. If src is empty and len is greater than the length of arg, arg remains unchanged. | x | x |
LTRIM( arg, char ) | String with the content of arg in which all trailing blanks and leading characters are removed that match the character in char. A blank in char is significant. | x | x |
REPLACE( arg1, arg2, arg3 ) | Character string arg1, in which all instances of arg2 are replaced by the content from arg3. The replacement of letters is case-sensitive. Trailing blanks are ignored in all arguments. The maximum length of the result is 1333. | x | x |
RIGHT( arg, len ) | String of the length len with the len right characters of arg (ignoring the trailing blanks). The value of len cannot be greater than the length of arg. | x | x |
RPAD( arg, len, src ) | String of the length len with the left-justified content of arg without trailing blanks and in which trailing blanks produced by the expanded string are replaced by the characters from the argument src (respecting all blanks). Trailing blanks from arg are preserved. If more characters are required than exist in src, the content of src is used repeatedly. If len is less than the length of arg, it is truncated on the right. If src is empty and len is greater than the length of arg, arg remains unchanged. | x | x |
RTRIM( arg, char ) | String with the content of arg in which all trailing blanks are removed and all trailing characters that match the character in char. A blank in char is significant. | x | x |
SUBSTRING( arg, pos, len ) | Substring of arg from the position pos in the length len. pos and len must be specified so that the substring is within arg. | x | x |
UPPER( arg ) | String with a length of arg, in which all lower and uppercase characters are have been converted. | x | x |
SQL Functions for Null Values
The following table shows the SQL functions for null values that are supported by ABAP CDS and Open SQL. The last two columns indicate where a function can be used.
SQL Function | Result | ABAP CDS | Open SQL |
COALESCE( arg1, arg2, ... ) | Value of the first argument that does not have a null value. | x | x |
No comments:
Post a Comment