{"id":"01055008-b5d5-4489-ae7c-796c51119c48","shortId":"k3WLjk","kind":"skill","title":"abap-sql-amdp","tagline":"Help with modern ABAP SQL features and AMDP (ABAP Managed Database Procedures) including inline declarations, window functions, GROUP BY, HAVING, PRIVILEGED ACCESS, string functions, aggregate expressions, common table expressions (CTE), AMDP classes, AMDP procedures, AMDP table ","description":"# ABAP SQL & AMDP\n\nGuide for writing modern ABAP SQL statements and ABAP Managed Database Procedures (AMDP) in ABAP Cloud and Standard ABAP.\n\n## Workflow\n\n1. **Determine the user's goal**:\n   - Writing or optimizing ABAP SQL queries\n   - Using advanced SQL features (window functions, CTEs, aggregates)\n   - Creating AMDP procedures or functions\n   - Implementing CDS table functions via AMDP\n   - Understanding PRIVILEGED ACCESS for authorization bypass\n\n2. **Identify the context**:\n   - ABAP for Cloud Development vs. Standard ABAP (affects available syntax)\n   - Performance optimization needs\n   - Whether AMDP is justified (prefer ABAP SQL when possible)\n\n3. **Guide implementation** using modern ABAP SQL syntax\n\n## Modern ABAP SQL Quick Reference\n\n### Basic SELECT with Inline Declaration\n\n```abap\n\"Single record\nSELECT SINGLE FROM ztravel\n  FIELDS travel_id, description, total_price, currency_code\n  WHERE travel_id = @lv_travel_id\n  INTO @DATA(ls_travel).\n\n\"Multiple records into internal table\nSELECT FROM ztravel\n  FIELDS travel_id, description, total_price, currency_code\n  WHERE status = 'O'\n  ORDER BY total_price DESCENDING\n  INTO TABLE @DATA(lt_travels)\n  UP TO 100 ROWS.\n```\n\n### Expressions in SELECT List\n\n```abap\nSELECT FROM zflight\n  FIELDS carrier_id,\n         connection_id,\n         flight_date,\n         seats_max - seats_occupied AS seats_free,\n         CASE WHEN seats_occupied > seats_max * 80 / 100\n              THEN 'FULL'\n              ELSE 'AVAILABLE'\n         END AS availability,\n         CAST( price AS DECFLOAT34 ) AS price_dec,\n         CONCAT( carrier_id, connection_id ) AS flight_key\n  INTO TABLE @DATA(lt_flights).\n```\n\n### Aggregate Functions and GROUP BY\n\n```abap\nSELECT FROM zflight\n  FIELDS carrier_id,\n         COUNT(*) AS flight_count,\n         SUM( seats_occupied ) AS total_passengers,\n         AVG( price ) AS avg_price,\n         MIN( flight_date ) AS first_flight,\n         MAX( flight_date ) AS last_flight\n  GROUP BY carrier_id\n  HAVING COUNT(*) > 10\n  INTO TABLE @DATA(lt_stats).\n```\n\n### Window Functions\n\n```abap\nSELECT FROM zflight\n  FIELDS carrier_id,\n         connection_id,\n         flight_date,\n         price,\n         \"Running total\n         SUM( price ) OVER( PARTITION BY carrier_id\n                            ORDER BY flight_date\n                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total,\n         \"Row number within partition\n         ROW_NUMBER( ) OVER( PARTITION BY carrier_id\n                             ORDER BY flight_date DESCENDING ) AS row_num,\n         \"Ranking\n         RANK( ) OVER( PARTITION BY carrier_id ORDER BY price DESCENDING ) AS price_rank,\n         \"Lead/Lag\n         LAG( price, 1 ) OVER( PARTITION BY carrier_id ORDER BY flight_date ) AS prev_price,\n         LEAD( price, 1 ) OVER( PARTITION BY carrier_id ORDER BY flight_date ) AS next_price\n  INTO TABLE @DATA(lt_window).\n```\n\n### Common Table Expressions (CTE)\n\n```abap\nWITH\n  +connections AS (\n    SELECT FROM zflsch\n      FIELDS carrier_id, connection_id, city_from, city_to\n      WHERE carrier_id IN @lt_carriers ),\n  +flight_counts AS (\n    SELECT FROM zflight\n      FIELDS carrier_id, connection_id,\n             COUNT(*) AS cnt\n      GROUP BY carrier_id, connection_id ),\n  +result AS (\n    SELECT FROM +connections AS c\n      INNER JOIN +flight_counts AS f\n        ON c~carrier_id = f~carrier_id AND c~connection_id = f~connection_id\n      FIELDS c~carrier_id, c~city_from, c~city_to, f~cnt )\n  SELECT FROM +result\n    FIELDS *\n    ORDER BY cnt DESCENDING\n    INTO TABLE @DATA(lt_result).\n```\n\n### Set Operations (UNION, INTERSECT, EXCEPT)\n\n```abap\n\"UNION ALL (keeps duplicates) / UNION (removes duplicates)\nSELECT FROM ztable1 FIELDS col1, col2\nUNION ALL\nSELECT FROM ztable2 FIELDS col1, col2\nINTO TABLE @DATA(lt_union).\n\n\"INTERSECT — rows in both\nSELECT FROM ztable1 FIELDS col1\nINTERSECT\nSELECT FROM ztable2 FIELDS col1\nINTO TABLE @DATA(lt_intersect).\n\n\"EXCEPT — rows in first but not second\nSELECT FROM ztable1 FIELDS col1\nEXCEPT\nSELECT FROM ztable2 FIELDS col1\nINTO TABLE @DATA(lt_except).\n```\n\n### PRIVILEGED ACCESS\n\nBypasses CDS access control (DCL) — use with care:\n\n```abap\n\"Skips access control defined in CDS DCL\nSELECT FROM zi_travel\n  FIELDS travel_id, description\n  WHERE status = 'O'\n  INTO TABLE @DATA(lt_all_travels)\n  PRIVILEGED ACCESS.\n```\n\n### Built-in SQL Functions\n\n| Category       | Functions                                                                                                       |\n| -------------- | --------------------------------------------------------------------------------------------------------------- |\n| **String**     | `CONCAT`, `SUBSTRING`, `LENGTH`, `LEFT`, `RIGHT`, `LTRIM`, `RTRIM`, `UPPER`, `LOWER`, `REPLACE`, `LPAD`, `RPAD` |\n| **Numeric**    | `ABS`, `CEIL`, `FLOOR`, `ROUND`, `MOD`, `DIV`, `DIVISION`                                                       |\n| **Date/Time**  | `DATS_ADD_DAYS`, `DATS_DAYS_BETWEEN`, `TSTMP_ADD_SECONDS`, `TSTMP_CURRENT_UTCTIMESTAMP`, `DATN_ADD_MONTHS`      |\n| **Conversion** | `CAST`, `COALESCE`, `CURRENCY_CONVERSION`, `UNIT_CONVERSION`                                                    |\n| **Null**       | `COALESCE`, `CASE WHEN ... IS NULL`                                                                             |\n| **Aggregate**  | `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `STRING_AGG`                                                               |\n\n### Subqueries\n\n```abap\n\"Scalar subquery\nSELECT FROM ztravel\n  FIELDS travel_id,\n         total_price,\n         ( SELECT AVG( total_price ) FROM ztravel ) AS avg_price\n  INTO TABLE @DATA(lt_with_avg).\n\n\"EXISTS subquery\nSELECT FROM ztravel AS t\n  FIELDS t~travel_id, t~description\n  WHERE EXISTS ( SELECT FROM zbooking AS b\n                   WHERE b~travel_id = t~travel_id\n                     AND b~flight_date > @sy-datum )\n  INTO TABLE @DATA(lt_with_bookings).\n```\n\n## AMDP (ABAP Managed Database Procedures)\n\n### When to Use AMDP\n\n- Prefer ABAP SQL for most scenarios\n- Use AMDP when: complex calculations benefit from SQLScript, CDS table functions are needed, or mass data processing requires database-level optimization\n\n### AMDP Class Structure\n\n```abap\nCLASS zcl_my_amdp DEFINITION\n  PUBLIC FINAL CREATE PUBLIC.\n  PUBLIC SECTION.\n    INTERFACES if_amdp_marker_hdb.  \"Mandatory for AMDP\n\n    TYPES: BEGIN OF ty_result,\n             carrier_id TYPE s_carr_id,\n             total      TYPE i,\n           END OF ty_result,\n           tt_result TYPE STANDARD TABLE OF ty_result WITH EMPTY KEY.\n\n    \"AMDP procedure\n    METHODS get_carrier_stats\n      AMDP OPTIONS READ-ONLY CDS SESSION CLIENT DEPENDENT\n      EXPORTING VALUE(et_result) TYPE tt_result.\n\n    \"AMDP table function for CDS table function\n    CLASS-METHODS get_data FOR TABLE FUNCTION zdemo_amdp_tf.\nENDCLASS.\n```\n\n### AMDP Procedure Implementation\n\n```abap\nMETHOD get_carrier_stats\n  BY DATABASE PROCEDURE\n  FOR HDB\n  LANGUAGE SQLSCRIPT\n  OPTIONS READ-ONLY\n  USING zflight_ve.\n\n  et_result = SELECT carrier_id,\n                     COUNT(*) AS total\n              FROM zflight_ve\n              GROUP BY carrier_id\n              ORDER BY total DESC;\nENDMETHOD.\n```\n\n### AMDP Table Function for CDS Table Function\n\nCDS table function definition:\n\n```cds\n@ClientHandling.type: #CLIENT_DEPENDENT\n@ClientHandling.algorithm: #SESSION_VARIABLE\ndefine table function ZDEMO_AMDP_TF\n  with parameters @Environment.systemField: #SYSTEM_LANGUAGE p_lang : abap.lang\n  returns {\n    key carrier_id : s_carr_id;\n    carrier_name   : s_carrname;\n    flight_count   : abap.int4;\n  }\n  implemented by method zcl_my_amdp=>get_data;\n```\n\nAMDP implementation:\n\n```abap\nMETHOD get_data\n  BY DATABASE FUNCTION\n  FOR HDB\n  LANGUAGE SQLSCRIPT\n  OPTIONS READ-ONLY\n  USING zcarrier_ve zflight_ve.\n\n  RETURN SELECT c.carrier_id,\n                c.carrier_name,\n                COUNT(*) AS flight_count\n         FROM zcarrier_ve AS c\n         INNER JOIN zflight_ve AS f\n           ON c.carrier_id = f.carrier_id\n         GROUP BY c.carrier_id, c.carrier_name;\nENDMETHOD.\n```\n\n### AMDP Client Safety (ABAP Cloud)\n\n| Addition                       | Use Case                                      |\n| ------------------------------ | --------------------------------------------- |\n| `CDS SESSION CLIENT DEPENDENT` | Uses client-dependent CDS views (most common) |\n| `CLIENT INDEPENDENT`           | Uses only client-independent objects          |\n| `AMDP OPTIONS READ-ONLY`       | Mandatory in ABAP for Cloud Development       |\n\n## Output Format\n\nWhen helping with ABAP SQL or AMDP topics, structure responses as:\n\n```markdown\n## ABAP SQL / AMDP Guidance\n\n### Query\n\n[The ABAP SQL statement or AMDP implementation]\n\n### Explanation\n\n[Key features used and why]\n\n### Performance Notes\n\n[Optimization considerations if relevant]\n```\n\n## References\n\n- ABAP SQL Cheat Sheet: https://github.com/SAP-samples/abap-cheat-sheets\n- AMDP Cheat Sheet: https://github.com/SAP-samples/abap-cheat-sheets/blob/main/12_AMDP.md\n- ABAP SQL Reference: https://help.sap.com/doc/abapdocu_cp_index_htm/CLOUD/en-US/index.htm?file=abenabap_sql.htm","tags":["abap","sql","amdp","skills","likweitan","agent-skills","sap"],"capabilities":["skill","source-likweitan","skill-abap-sql-amdp","topic-abap","topic-agent-skills","topic-sap"],"categories":["abap-skills"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/likweitan/abap-skills/abap-sql-amdp","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"cli":"npx skills add likweitan/abap-skills","source_repo":"https://github.com/likweitan/abap-skills","install_from":"skills.sh"}},"qualityScore":"0.456","qualityRationale":"deterministic score 0.46 from registry signals: · indexed on github topic:agent-skills · 12 github stars · SKILL.md body (9,107 chars)","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill-github:v1","enrichmentVersion":1,"enrichedAt":"2026-04-24T01:03:15.770Z","embedding":null,"createdAt":"2026-04-23T13:03:44.381Z","updatedAt":"2026-04-24T01:03:15.770Z","lastSeenAt":"2026-04-24T01:03:15.770Z","tsv":"'/doc/abapdocu_cp_index_htm/cloud/en-us/index.htm?file=abenabap_sql.htm':1125 '/sap-samples/abap-cheat-sheets':1113 '/sap-samples/abap-cheat-sheets/blob/main/12_amdp.md':1119 '1':64,384,399 '10':305 '100':201,232 '2':101 '3':127 '80':231 'ab':641 'abap':2,8,13,41,48,52,58,62,73,105,111,123,132,136,145,207,265,313,421,513,593,686,749,758,788,881,976,1032,1064,1073,1082,1088,1107,1120 'abap-sql-amdp':1 'abap.int4':965 'abap.lang':951 'access':26,97,584,587,595,619 'add':650,656,662 'addit':1034 'advanc':77 'affect':112 'agg':684 'aggreg':29,83,260,677 'amdp':4,12,35,37,39,43,56,85,94,119,748,756,764,785,792,802,807,837,843,859,875,878,920,942,971,974,1029,1057,1076,1084,1092,1114 'author':99 'avail':113,236,239 'avg':282,285,680,698,704,711 'b':730,732,737 'basic':140 'begin':809 'benefit':768 'book':747 'built':621 'built-in':620 'bypass':100,585 'c':469,477,482,487,489,491,1010 'c.carrier':998,1000,1018,1024,1026 'calcul':767 'care':592 'carr':817,957 'carrier':212,248,270,301,318,332,357,372,388,403,429,438,442,450,459,813,841,884,903,913,954,959 'carrnam':962 'case':225,673,1036 'cast':240,665 'categori':625 'cds':90,586,599,771,848,863,924,927,931,1037,1045 'ceil':642 'cheat':1109,1115 'citi':433,435 'class':36,786,789,867 'class-method':866 'client':850,933,1030,1039,1043,1049,1054 'client-depend':1042 'client-independ':1053 'clienthandling.algorithm':935 'clienthandling.type':932 'cloud':59,107,1033,1066 'cnt':456,501 'coalesc':666,672 'code':159,185 'col1':525,533,548,554,571,577 'col2':526,534 'common':31,417,1048 'complex':766 'concat':247,628 'connect':214,250,320,423,431,452,461,467 'consider':1103 'context':104 'control':588,596 'convers':664,668,670 'count':272,275,304,444,454,473,678,905,964,1002,1005 'creat':84,796 'cte':34,420 'ctes':82 'currenc':158,184,667 'current':343,659 'dat':649,652 'data':167,196,257,308,414,505,537,557,580,614,708,744,778,870,973,979 'databas':15,54,751,782,887,981 'database-level':781 'date':217,289,295,323,337,362,393,408 'date/time':648 'datn':661 'datum':741 'day':651,653 'dcl':589,600 'dec':246 'decfloat34':243 'declar':19,144 'defin':597,938 'definit':793,930 'depend':851,934,1040,1044 'desc':918 'descend':193,363,377,502 'descript':155,181,608 'determin':65 'develop':108,1067 'div':646 'divis':647 'duplic':517,520 'els':235 'empti':835 'end':237,822 'endclass':877 'endmethod':919,1028 'environment.systemfield':946 'et':854,900 'except':512,560,572,582 'exist':712,725 'explan':1094 'export':852 'express':30,33,203,419 'f':475,479,484,493,1016 'f.carrier':1020 'featur':10,79,1096 'field':152,178,211,269,317,428,449,486,498,524,532,547,553,570,576,605,692,719 'final':795 'first':291,563 'flight':216,253,259,274,288,292,294,298,322,336,361,392,407,443,472,963,1004 'floor':643 'format':1069 'free':224 'full':234 'function':21,28,81,88,92,261,312,624,626,773,861,865,873,922,926,929,940,982 'get':840,869,883,972,978 'github.com':1112,1118 'github.com/sap-samples/abap-cheat-sheets':1111 'github.com/sap-samples/abap-cheat-sheets/blob/main/12_amdp.md':1117 'goal':69 'group':22,263,299,457,911,1022 'guid':44,128 'guidanc':1085 'hdb':804,890,984 'help':5,1071 'help.sap.com':1124 'help.sap.com/doc/abapdocu_cp_index_htm/cloud/en-us/index.htm?file=abenabap_sql.htm':1123 'id':154,162,165,180,213,215,249,251,271,302,319,321,333,358,373,389,404,430,432,439,451,453,460,462,607,694,814,818,904,914,955,958,999,1019,1021,1025 'identifi':102 'implement':89,129,880,966,975,1093 'includ':17 'independ':1050,1055 'inlin':18,143 'inner':470,1011 'interfac':800 'intern':173 'intersect':511,540,549,559 'join':471,1012 'justifi':121 'keep':516 'key':254,836,953,1095 'lag':382 'lang':950 'languag':891,948,985 'last':297 'lead':397 'lead/lag':381 'left':631 'length':630 'level':783 'list':206 'lower':636 'lpad':638 'ls':168 'lt':197,258,309,415,441,506,538,558,581,615,709,745 'ltrim':633 'lv':163 'manag':14,53,750 'mandatori':805,1062 'markdown':1081 'marker':803 'mass':777 'max':219,230,293,682 'method':839,868,882,968,977 'min':287,681 'mod':645 'modern':7,47,131,135 'month':663 'multipl':170 'name':960,1001,1027 'need':117,775 'next':410 'note':1101 'null':671,676 'num':366 'number':349,353 'numer':640 'o':188,611 'object':1056 'occupi':221,228,278 'oper':509 'optim':72,116,784,1102 'option':844,893,987,1058 'order':189,334,359,374,390,405,499,915 'output':1068 'p':949 'paramet':945 'partit':330,351,355,370,386,401 'passeng':281 'perform':115,1100 'possibl':126 'preced':341 'prefer':122,757 'prev':395 'price':157,183,192,241,245,283,286,324,328,376,379,383,396,398,411,696,700,705 'privileg':25,96,583,618 'procedur':16,38,55,86,752,838,879,888 'process':779 'public':794,797,798 'queri':75,1086 'quick':138 'rank':367,368,380 'read':846,895,989,1060 'read-on':845,894,988,1059 'record':147,171 'refer':139,1106,1122 'relev':1105 'remov':519 'replac':637 'requir':780 'respons':1079 'result':463,497,507,812,825,827,833,855,858,901 'return':952,996 'right':632 'round':644 'row':202,338,344,348,352,365,541,561 'rpad':639 'rtrim':634 'run':325,346 'safeti':1031 'scalar':687 'scenario':762 'seat':218,220,223,227,229,277 'second':566,657 'section':799 'select':141,148,175,205,208,266,314,425,446,465,495,521,529,544,550,567,573,601,689,697,714,726,902,997 'session':849,936,1038 'set':508 'sheet':1110,1116 'singl':146,149 'skill' 'skill-abap-sql-amdp' 'skip':594 'source-likweitan' 'sql':3,9,42,49,74,78,124,133,137,623,759,1074,1083,1089,1108,1121 'sqlscript':770,892,986 'standard':61,110,829 'stat':310,842,885 'statement':50,1090 'status':187,610 'string':27,627,683 'structur':787,1078 'subqueri':685,688,713 'substr':629 'sum':276,327,679 'sy':740 'sy-datum':739 'syntax':114,134 'system':947 'tabl':32,40,91,174,195,256,307,413,418,504,536,556,579,613,707,743,772,830,860,864,872,921,925,928,939 'tf':876,943 'topic':1077 'topic-abap' 'topic-agent-skills' 'topic-sap' 'total':156,182,191,280,326,347,695,699,819,907,917 'travel':153,161,164,169,179,198,604,606,617,693 'tstmp':655,658 'tt':826,857 'ty':811,824,832 'type':808,815,820,828,856 'unbound':340 'understand':95 'union':510,514,518,527,539 'unit':669 'upper':635 'use':76,130,590,755,763,897,991,1035,1041,1051,1097 'user':67 'utctimestamp':660 'valu':853 'variabl':937 've':899,910,993,995,1008,1014 'via':93 'view':1046 'vs':109 'whether':118 'window':20,80,311,416 'within':350 'workflow':63 'write':46,70 'zbook':728 'zcarrier':992,1007 'zcl':790,969 'zdemo':874,941 'zflight':210,268,316,448,898,909,994,1013 'zflsch':427 'zi':603 'ztable1':523,546,569 'ztable2':531,552,575 'ztravel':151,177,691,702,716 '~carrier_id':478,480,488 '~city_from':490 '~city_to':492 '~cnt':494 '~connection_id':483,485 '~description':723 '~flight_date':738 '~travel_id':721,733,735","prices":[{"id":"5883aa0f-035a-4903-b474-14df8f5455c6","listingId":"01055008-b5d5-4489-ae7c-796c51119c48","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"likweitan","category":"abap-skills","install_from":"skills.sh"},"createdAt":"2026-04-23T13:03:44.381Z"}],"sources":[{"listingId":"01055008-b5d5-4489-ae7c-796c51119c48","source":"github","sourceId":"likweitan/abap-skills/abap-sql-amdp","sourceUrl":"https://github.com/likweitan/abap-skills/tree/main/skills/abap-sql-amdp","isPrimary":false,"firstSeenAt":"2026-04-23T13:03:44.381Z","lastSeenAt":"2026-04-24T01:03:15.770Z"}],"details":{"listingId":"01055008-b5d5-4489-ae7c-796c51119c48","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"likweitan","slug":"abap-sql-amdp","github":{"repo":"likweitan/abap-skills","stars":12,"topics":["abap","agent-skills","sap"],"license":"mit","html_url":"https://github.com/likweitan/abap-skills","pushed_at":"2026-04-17T13:44:41Z","description":"Advance Agent Skills for ABAP Developers","skill_md_sha":"c56a6b134d817594451d570dfaabdc44c526e7ca","skill_md_path":"skills/abap-sql-amdp/SKILL.md","default_branch":"main","skill_tree_url":"https://github.com/likweitan/abap-skills/tree/main/skills/abap-sql-amdp"},"layout":"multi","source":"github","category":"abap-skills","frontmatter":{"name":"abap-sql-amdp","description":"Help with modern ABAP SQL features and AMDP (ABAP Managed Database Procedures) including inline declarations, window functions, GROUP BY, HAVING, PRIVILEGED ACCESS, string functions, aggregate expressions, common table expressions (CTE), AMDP classes, AMDP procedures, AMDP table functions, CDS table functions, and AMDP scalar functions. Use when users ask about ABAP SQL, modern SQL, SELECT, window functions, CTE, common table expression, AMDP, SQLScript, AMDP table function, CDS table function, aggregate, GROUP BY, HAVING, UNION, INTERSECT, EXCEPT, PRIVILEGED ACCESS, ABAP SQL expressions, built-in SQL functions, or database procedures. Triggers include \"ABAP SQL query\", \"window function\", \"CTE\", \"AMDP\", \"table function\", \"GROUP BY\", \"aggregate\", \"PRIVILEGED ACCESS\", \"inline SELECT\", or \"SQLScript\"."},"skills_sh_url":"https://skills.sh/likweitan/abap-skills/abap-sql-amdp"},"updatedAt":"2026-04-24T01:03:15.770Z"}}