abap-sql-amdp

Name: abap-sql-amdp
Author: likweitan

28stars11forks

How to install abap-sql-amdp?

likweitan/abap-skills/abap-sql-amdp

$npx -y skills add likweitan/abap-skills --skill abap-sql-amdp

Installs into the current project.

Summary

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

Files · 1

View on GitHub

SKILL.md

1	# ABAP SQL & AMDP
2
3	Guide for writing modern ABAP SQL statements and ABAP Managed Database Procedures (AMDP) in ABAP Cloud and Standard ABAP.
4
5	## Workflow
6
7	1. Determine the user's goal:
8	- Writing or optimizing ABAP SQL queries
9	- Using advanced SQL features (window functions, CTEs, aggregates)
10	- Creating AMDP procedures or functions
11	- Implementing CDS table functions via AMDP
12	- Understanding PRIVILEGED ACCESS for authorization bypass
13
14	2. Identify the context:
15	- ABAP for Cloud Development vs. Standard ABAP (affects available syntax)
16	- Performance optimization needs
17	- Whether AMDP is justified (prefer ABAP SQL when possible)
18
19	3. Guide implementation using modern ABAP SQL syntax
20
21	## Modern ABAP SQL Quick Reference
22
23	### Basic SELECT with Inline Declaration
24
25	```abap
26	"Single record
27	SELECT SINGLE FROM ztravel
28	FIELDS travel_id, description, total_price, currency_code
29	WHERE travel_id = @lv_travel_id
30	INTO @DATA(ls_travel).
31
32	"Multiple records into internal table
33	SELECT FROM ztravel
34	FIELDS travel_id, description, total_price, currency_code
35	WHERE status = 'O'
36	ORDER BY total_price DESCENDING
37	INTO TABLE @DATA(lt_travels)
38	UP TO 100 ROWS.
39	```
40
41	### Expressions in SELECT List
42
43	```abap
44	SELECT FROM zflight
45	FIELDS carrier_id,
46	connection_id,
47	flight_date,
48	seats_max - seats_occupied AS seats_free,
49	CASE WHEN seats_occupied > seats_max * 80 / 100
50	THEN 'FULL'
51	ELSE 'AVAILABLE'
52	END AS availability,
53	CAST( price AS DECFLOAT34 ) AS price_dec,
54	CONCAT( carrier_id, connection_id ) AS flight_key
55	INTO TABLE @DATA(lt_flights).
56	```
57
58	### Aggregate Functions and GROUP BY
59
60	```abap
61	SELECT FROM zflight
62	FIELDS carrier_id,
63	COUNT(*) AS flight_count,
64	SUM( seats_occupied ) AS total_passengers,
65	AVG( price ) AS avg_price,
66	MIN( flight_date ) AS first_flight,
67	MAX( flight_date ) AS last_flight
68	GROUP BY carrier_id
69	HAVING COUNT(*) > 10
70	INTO TABLE @DATA(lt_stats).
71	```
72
73	### Window Functions
74
75	```abap
76	SELECT FROM zflight
77	FIELDS carrier_id,
78	connection_id,
79	flight_date,
80	price,
81	"Running total
82	SUM( price ) OVER( PARTITION BY carrier_id
83	ORDER BY flight_date
84	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total,
85	"Row number within partition
86	ROW_NUMBER( ) OVER( PARTITION BY carrier_id
87	ORDER BY flight_date DESCENDING ) AS row_num,
88	"Ranking
89	RANK( ) OVER( PARTITION BY carrier_id ORDER BY price DESCENDING ) AS price_rank,
90	"Lead/Lag
91	LAG( price, 1 ) OVER( PARTITION BY carrier_id ORDER BY flight_date ) AS prev_price,
92	LEAD( price, 1 ) OVER( PARTITION BY carrier_id ORDER BY flight_date ) AS next_price
93	INTO TABLE @DATA(lt_window).
94	```
95
96	### Common Table Expressions (CTE)
97
98	```abap
99	WITH
100	+connections AS (
101	SELECT FROM zflsch
102	FIELDS carrier_id, connection_id, city_from, city_to
103	WHERE carrier_id IN @lt_carriers ),
104	+flight_counts AS (
105	SELECT FROM zflight
106	FIELDS carrier_id, connection_id,
107	COUNT(*) AS cnt
108	GROUP BY carrier_id, connection_id ),
109	+result AS (
110	SELECT FROM +connections AS c
111	INNER JOIN +flight_counts AS f
112	ON c~carrier_id = f~carrier_id AND c~connection_id = f~connection_id
113	FIELDS c~carrier_id, c~city_from, c~city_to, f~cnt )
114	SELECT FROM +result
115	FIELDS *
116	ORDER BY cnt DESCENDING
117	INTO TABLE @DATA(lt_result).
118	```
119
120	### Set Operations (UNION, INTERSECT, EXCEPT)
121
122	```abap
123	"UNION ALL (keeps duplicates) / UNION (removes duplicates)
124	SELECT FROM ztable1 FIELDS col1, col2
125	UNION ALL
126	SELECT FROM ztable2 FIELDS col1, col2
127	INTO TABLE @DATA(lt_union).
128
129	"INTERSECT — rows in both
130	SELECT FROM ztable1 FIELDS col1
131	INTERSECT
132	SELECT FROM ztable2 FIELDS col1
133	INTO TABLE @DATA(lt_intersect).
134
135	"EXCEPT — rows in first but not second
136	SELECT FROM ztable1 FIELDS col1
137	EXCEPT
138	SELECT FROM ztable2 FIELDS col1
139	INTO TABLE @DATA(lt_except).
140	```
141
142	### PRIVILEGED ACCESS
143
144	Bypasses CDS access control (DCL

People who liked this also viewed

6 picks

Type

abap-sql-amdp

28stars11forks

View on GitHub

How to install abap-sql-amdp?

likweitan/abap-skills/abap-sql-amdp

$npx -y skills add likweitan/abap-skills --skill abap-sql-amdp

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# ABAP SQL & AMDP
2
3	Guide for writing modern ABAP SQL statements and ABAP Managed Database Procedures (AMDP) in ABAP Cloud and Standard ABAP.
4
5	## Workflow
6
7	1. Determine the user's goal:
8	- Writing or optimizing ABAP SQL queries
9	- Using advanced SQL features (window functions, CTEs, aggregates)
10	- Creating AMDP procedures or functions
11	- Implementing CDS table functions via AMDP
12	- Understanding PRIVILEGED ACCESS for authorization bypass
13
14	2. Identify the context:
15	- ABAP for Cloud Development vs. Standard ABAP (affects available syntax)
16	- Performance optimization needs
17	- Whether AMDP is justified (prefer ABAP SQL when possible)
18
19	3. Guide implementation using modern ABAP SQL syntax
20
21	## Modern ABAP SQL Quick Reference
22
23	### Basic SELECT with Inline Declaration
24
25	```abap
26	"Single record
27	SELECT SINGLE FROM ztravel
28	FIELDS travel_id, description, total_price, currency_code
29	WHERE travel_id = @lv_travel_id
30	INTO @DATA(ls_travel).
31
32	"Multiple records into internal table
33	SELECT FROM ztravel
34	FIELDS travel_id, description, total_price, currency_code
35	WHERE status = 'O'
36	ORDER BY total_price DESCENDING
37	INTO TABLE @DATA(lt_travels)
38	UP TO 100 ROWS.
39	```
40
41	### Expressions in SELECT List
42
43	```abap
44	SELECT FROM zflight
45	FIELDS carrier_id,
46	connection_id,
47	flight_date,
48	seats_max - seats_occupied AS seats_free,
49	CASE WHEN seats_occupied > seats_max * 80 / 100
50	THEN 'FULL'
51	ELSE 'AVAILABLE'
52	END AS availability,
53	CAST( price AS DECFLOAT34 ) AS price_dec,
54	CONCAT( carrier_id, connection_id ) AS flight_key
55	INTO TABLE @DATA(lt_flights).
56	```
57
58	### Aggregate Functions and GROUP BY
59
60	```abap
61	SELECT FROM zflight
62	FIELDS carrier_id,
63	COUNT(*) AS flight_count,
64	SUM( seats_occupied ) AS total_passengers,
65	AVG( price ) AS avg_price,
66	MIN( flight_date ) AS first_flight,
67	MAX( flight_date ) AS last_flight
68	GROUP BY carrier_id
69	HAVING COUNT(*) > 10
70	INTO TABLE @DATA(lt_stats).
71	```
72
73	### Window Functions
74
75	```abap
76	SELECT FROM zflight
77	FIELDS carrier_id,
78	connection_id,
79	flight_date,
80	price,
81	"Running total
82	SUM( price ) OVER( PARTITION BY carrier_id
83	ORDER BY flight_date
84	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total,
85	"Row number within partition
86	ROW_NUMBER( ) OVER( PARTITION BY carrier_id
87	ORDER BY flight_date DESCENDING ) AS row_num,
88	"Ranking
89	RANK( ) OVER( PARTITION BY carrier_id ORDER BY price DESCENDING ) AS price_rank,
90	"Lead/Lag
91	LAG( price, 1 ) OVER( PARTITION BY carrier_id ORDER BY flight_date ) AS prev_price,
92	LEAD( price, 1 ) OVER( PARTITION BY carrier_id ORDER BY flight_date ) AS next_price
93	INTO TABLE @DATA(lt_window).
94	```
95
96	### Common Table Expressions (CTE)
97
98	```abap
99	WITH
100	+connections AS (
101	SELECT FROM zflsch
102	FIELDS carrier_id, connection_id, city_from, city_to
103	WHERE carrier_id IN @lt_carriers ),
104	+flight_counts AS (
105	SELECT FROM zflight
106	FIELDS carrier_id, connection_id,
107	COUNT(*) AS cnt
108	GROUP BY carrier_id, connection_id ),
109	+result AS (
110	SELECT FROM +connections AS c
111	INNER JOIN +flight_counts AS f
112	ON c~carrier_id = f~carrier_id AND c~connection_id = f~connection_id
113	FIELDS c~carrier_id, c~city_from, c~city_to, f~cnt )
114	SELECT FROM +result
115	FIELDS *
116	ORDER BY cnt DESCENDING
117	INTO TABLE @DATA(lt_result).
118	```
119
120	### Set Operations (UNION, INTERSECT, EXCEPT)
121
122	```abap
123	"UNION ALL (keeps duplicates) / UNION (removes duplicates)
124	SELECT FROM ztable1 FIELDS col1, col2
125	UNION ALL
126	SELECT FROM ztable2 FIELDS col1, col2
127	INTO TABLE @DATA(lt_union).
128
129	"INTERSECT — rows in both
130	SELECT FROM ztable1 FIELDS col1
131	INTERSECT
132	SELECT FROM ztable2 FIELDS col1
133	INTO TABLE @DATA(lt_intersect).
134
135	"EXCEPT — rows in first but not second
136	SELECT FROM ztable1 FIELDS col1
137	EXCEPT
138	SELECT FROM ztable2 FIELDS col1
139	INTO TABLE @DATA(lt_except).
140	```
141
142	### PRIVILEGED ACCESS
143
144	Bypasses CDS access control (DCL

People who liked this also viewed

6 picks

Type