1 1．CSD查询。
2 @AbapCatalog.sqlViewName: 'ZS_FLIGHT'
3 @AbapCatalog.compiler.compareFilter: true
4 @AccessControl.authorizationCheck: #CHECK
5 @EndUserText.label: 'DEMO FOR S4'
6 define view ZS4H_DEMO_1 as select from sflight as s {
7 key s.mandt,
8 key s.carrid,
9 key s.connid,
10 key s.fldate,
11 s.price,
12 s.currency,
13 s.planetype,
14 s.paymentsum,
15 ( s.seatsmax + s.seatsmax_b + s.seatsmax_f ) as seatsmax,
16 ( s.seatsocc + s.seatsocc_b + s.seatsocc_f ) as seatsocc
17 }
18
19 2.cds内链接
20 @AbapCatalog.sqlViewName: 'ZS_FLIGHT_JOIN'
21 @AbapCatalog.compiler.compareFilter: true
22 @AccessControl.authorizationCheck: #CHECK
23 @EndUserText.label: 'DEMO FOR S4'
24 define view ZS4H_DEMO_2 as select from sflight as s
25 inner join spfli as p
26 on p.carrid = s.carrid
27 and p.connid = s.connid
28 {
29 key s.mandt,
30 key s.carrid,
31 key s.connid,
32 key s.fldate,
33 @Semantics.currencyCode: true s.currency,
34 @Semantics.amount.currencyCode: 'CURRENCY's.price,
35 s.planetype,
36 s.seatsmax,
37 s.seatsocc,
38 @Semantics.amount.currencyCode: 'CURRENCY's.paymentsum,
39 s.seatsmax_b,
40 s.seatsocc_b,
41 s.seatsmax_f,
42 s.seatsocc_f,
43 p.countryfr,
44 p.cityfrom,
45 p.airpfrom,
46 p.countryto,
47 p.cityto,
48 p.airpto,
49 p.fltime,
50 p.deptime,
51 p.arrtime,
52 p.distance,
53 p.distid,
54 p.fltype,
55 p.period
56 }
57 where ( s.carrid = 'AA' and s.fldate like '2018%')
58 or ( s.carrid = 'AZ' and s.fldate like '2019%')
59
60 3.cds外连接
61 @AbapCatalog.sqlViewName: 'ZS_FLIGHT_OUT'
62 @AbapCatalog.compiler.compareFilter: true
63 @AccessControl.authorizationCheck: #CHECK
64 @EndUserText.label: 'DEMO FOR S4'
65 define view ZS4H_DEMO_3 as select from scarr as S
66 left outer join spfli as P
67 on S.carrid = P.carrid{
68 key S.mandt,
69 key S.carrid,
70 key P.connid,
71 S.carrname,
72 S.currcode,
73 S.url,
74 P.countryfr,
75 P.cityfrom,
76 P.airpfrom,
77 P.countryto,
78 P.cityto,
79 P.airpto,
80 P.fltime,
81 P.deptime,
82 P.arrtime,
83 P.distance,
84 P.distid,
85 P.fltype,
86 P.period
87
88 } where P.connid is null
89 4.cds并集
90 @AbapCatalog.sqlViewName: 'ZS_FLIGHT_UN'
91 @AbapCatalog.compiler.compareFilter: true
92 @AccessControl.authorizationCheck: #CHECK
93 @EndUserText.label: 'DEMO FOR S4'
94 define view ZS4H_DEMO_4 as
95 select
96 mandt,
97 carrid,
98 connid,
99 fldate,
100 loccurkey,
101 sum ( loccuram ) as LOCCURAM_Y,
102 cast( '0' as s_l_cur_pr ) as LOCCURAM_B,
103 cast( '0' as s_l_cur_pr ) as LOCCURAM_F
104 from sbook
105 where
106 class = 'Y'
107 group by
108 mandt,
109 carrid,
110 connid,
111 fldate,
112 loccurkey
113
114 union all
115
116 select
117 mandt,
118 carrid,
119 connid,
120 fldate,
121 loccurkey,
122 cast( '0' as s_l_cur_pr ) as LOCCURAM_Y,
123 sum ( loccuram ) as LOCCURAM_B,
124 cast( '0' as s_l_cur_pr ) as LOCCURAM_F
125 from sbook
126 where
127 class = 'C'
128 group by
129 mandt,
130 carrid,
131 connid,
132 fldate,
133 loccurkey
134
135 union all
136
137 select
138 mandt,
139 carrid,
140 connid,
141 fldate,
142 loccurkey,
143 cast( '0' as s_l_cur_pr ) as LOCCURAM_Y,
144 cast( '0' as s_l_cur_pr ) as LOCCURAM_B,
145 sum ( loccuram ) as LOCCURAM_F
146 from sbook
147 where
148 class = 'F'
149 group by
150 mandt,
151 carrid,
152 connid,
153 fldate,
154 loccurkey
155
156 cds不支持子查询，查询并集新建cds,查视图
157 @AbapCatalog.sqlViewName: 'ZS_FLIGHT_UN2'
158 @AbapCatalog.compiler.compareFilter: true
159 @AccessControl.authorizationCheck: #CHECK
160 @EndUserText.label: 'DEMO FOR S4'
161 define view ZS4H_DEMO_5 as select from zs_flight_un as UN {
162 UN.mandt,
163 UN.carrid,
164 UN.connid,
165 UN.fldate,
166 @Semantics.currencyCode: true UN.loccurkey,
167 @Semantics.amount.currencyCode: 'loccurkey' sum( UN.loccuram_y ) as Y,
168 @Semantics.amount.currencyCode: 'loccurkey' sum( UN.loccuram_b ) as B,
169 @Semantics.amount.currencyCode: 'loccurkey' sum( UN.loccuram_f ) as F
170 }group by
171 UN.mandt,
172 UN.carrid,
173 UN.connid,
174 UN.fldate,
175 UN.loccurkey
176
177 5.cds聚合
178 @AbapCatalog.sqlViewName: 'ZS_FLIGHT_AGG'
179 @AbapCatalog.compiler.compareFilter: true
180 @AccessControl.authorizationCheck: #CHECK
181 @EndUserText.label: 'DEMO FOR S4'
182 define view ZS4H_DEMO_6
183 as select from sflight as S
184 {
185 key S.mandt,
186 key S.carrid,
187 key S.connid,
188 key S.planetype,
189 @Semantics.currencyCode: true
190 S.currency,
191 @Semantics.amount.currencyCode: 'currency'
192 sum( S.paymentsum ) as PAYMENTSUM
193 }
194 group by
195 S.mandt,
196 S.carrid,
197 S.connid,
198 S.planetype,
199 S.currency
200 6.关联
201 @AbapCatalog.sqlViewName: 'ZS_FLIGHT_ASS'
202 @AbapCatalog.compiler.compareFilter: true
203 @AccessControl.authorizationCheck: #CHECK
204 @EndUserText.label: 'DEMO FOR S4'
205 define view ZS4H_DEMO_7
206 as select from spfli
207 association [1..1] to scarr as airline on airline.carrid = spfli.carrid
208 {
209 spfli.mandt,
210 spfli.carrid,
211 spfli.connid,
212 spfli.countryfr,
213 spfli.cityfrom,
214 spfli.airpfrom,
215 spfli.countryto,
216 spfli.cityto,
217 spfli.airpto,
218 spfli.fltime,
219 spfli.deptime,
220 spfli.arrtime,
221 spfli.distance,
222 spfli.distid,
223 spfli.fltype,
224 spfli.period,
225 airline
226 }
227 关联中无法查，要新建一个cds ,如下。
228 @AbapCatalog.sqlViewName: 'ZS_FLIGHT_ASS1'
229 @AbapCatalog.compiler.compareFilter: true
230 @AccessControl.authorizationCheck: #CHECK
231 @EndUserText.label: 'DEMO FOR S4'
232 define view ZS4H_DEMO_8 as select from ZS4H_DEMO_7 as S {
233 S.mandt,
234 S.carrid,
235 S.connid,
236 S.countryfr,
237 S.cityfrom,
238 S.airpfrom,
239 S.countryto,
240 S.cityto,
241 S.airpto,
242 S.fltime,
243 S.deptime,
244 S.arrtime,
245 S.distance,
246 S.distid,
247 S.fltype,
248 S.period,
249 S.airline.carrname,
250 S.airline.currcode,
251 S.airline.url,
252 S.airline.zcol1
253 }
254 Cds继承，然后据徐往下读。
255 @AbapCatalog.sqlViewName: 'ZS_FLIGHT_ASSO2'
256 @AbapCatalog.compiler.compareFilter: true
257 @AccessControl.authorizationCheck: #CHECK
258 @EndUserText.label: 'DEMO FOR S4'
259 define view ZS4H_DEMO_9 as select from sflight
260 association [1..1] to ZS4H_DEMO_7 as FLIGHT
261 on SFLIGHT.carrid = FLIGHT.carrid
262 and SFLIGHT.connid = FLIGHT.connid {
263 sflight.mandt,
264 sflight.carrid,
265 sflight.connid,
266 sflight.fldate,
267 sflight.price,
268 sflight.currency,
269 sflight.planetype,
270 sflight.seatsmax,
271 sflight.seatsocc,
272 sflight.paymentsum,
273 sflight.seatsmax_b,
274 sflight.seatsocc_b,
275 sflight.seatsmax_f,
276 sflight.seatsocc_f,
277 FLIGHT.countryfr,
278 FLIGHT.cityfrom,
279 FLIGHT.airpfrom,
280 FLIGHT.countryto,
281 FLIGHT.cityto,
282 FLIGHT.airpto,
283 FLIGHT.fltime,
284 FLIGHT.deptime,
285 FLIGHT.arrtime,
286 FLIGHT.distance,
287 FLIGHT.distid,
288 FLIGHT.fltype,
289 FLIGHT.period,
290 FLIGHT.airline.carrname,
291 FLIGHT.airline.currcode,
292 FLIGHT.airline.url,
293 FLIGHT.airline.zcol1
294 }
295
296 7.CDS带参数
297 @AbapCatalog.sqlViewName: 'ZS_FLIGHT_P'
298 @AbapCatalog.compiler.compareFilter: true
299 @AccessControl.authorizationCheck: #CHECK
300 @EndUserText.label: 'DEMO FOR S4'
301 define view ZS4H_DEMO_10
302 with parameters i_carrid : s_carr_id
303 as select from sflight as s {
304 s.mandt,
305 s.carrid,
306 s.connid,
307 s.fldate,
308 s.price,
309 s.currency,
310 s.planetype,
311 s.seatsmax,
312 s.seatsocc,
313 s.paymentsum,
314 s.seatsmax_b,
315 s.seatsocc_b,
316 s.seatsmax_f,
317 s.seatsocc_f
318 } where carrid = :i_carrid
319
320 8.cds扩展
321 @AbapCatalog.sqlViewAppendName: 'ZS_FLIGHT_EX'
322 @EndUserText.label: 'DEMO FOR S4'
323 extend view ZS4H_DEMO_3 with ZS4H_DEMO_11 {
324 P.countryto,
325 P.cityto,
326 P.airpto,
327 replace(S.url ,'http://' , '') as WITHOUT_URL“替代
328 }
329 9.CDS注解
330 @AbapCatalog.sqlViewName: 'ZS_FLIGHT_ANN'
331 @AbapCatalog.compiler.compareFilter: true
332 @AccessControl.authorizationCheck: #CHECK
333 @EndUserText.label: 'DEMO FOR S4'
334 define view ZS4H_DEMO_12 as select from sflight as S {
335 S.mandt,
336 @EndUserText.label: 'FLIGHT NUMBER'
337 S.carrid,
338 S.fldate,
339 S.connid,
340 @Semantics.amount.currencyCode: 'currency'
341 S.price,
342 @Semantics.currencyCode: true
343 S.currency
344 }
345 10.open sql 访问cds数据库。
346 REPORT zs4h_cds_demo.
347 TYPES: ty_result TYPE zs4h_demo_10.
348 DATA: gt_result TYPE TABLE OF ty_result.
349 FIELD-SYMBOLS:<fs> TYPE ty_result.
350 PARAMETERS:
351 p_carrid TYPE zs4h_demo_10-carrid,
352 p_connid TYPE zs4h_demo_10-connid.
353
354 SELECT
355 *
356 FROM zs4h_demo_10( i_carrid = @p_carrid , i_connid = @p_connid )
357 INTO CORRESPONDING FIELDS OF TABLE @gt_result.
358 LOOP AT gt_result ASSIGNING <fs>.
359 WRITE: / <fs>-carrid,
360 <fs>-connid.
361 ENDLOOP.
362
363 @AbapCatalog.sqlViewName: 'ZS_DEMO'
364 @AbapCatalog.compiler.compareFilter: true
365 @AccessControl.authorizationCheck: #CHECK
366 @EndUserText.label: 'DEMO FOR S4'
367 define view ZCDS_DEMO as select from vbap
368 inner join vbak on vbap.vbeln = vbak.vbeln
369 inner join makt on vbap.matnr = makt.matnr
370 inner join lips on vbap.vbeln = lips.vgbel and vbap.posnr = lips.vgpos
371 inner join likp on lips.vbeln = likp.vbeln {
372 key vbak.vbeln,
373 key vbap.posnr,
374 vbak.vkorg,
375 vbap.matnr,
376 makt.maktx,
377 vbap.netwr,
378 vbap.kwmeng,
379 likp.vbeln as VBELN1,
380 lips.posnr as POSNR1,
381 lips.lfimg,
382 ( cast(vbap.netwr as float) * cast(lips.lfimg as float) / cast(vbap.kwmeng as float) ) as ZSUM
383 } where spras = '1'
384
385 @AbapCatalog.sqlViewAppendName: 'ZS_DEMO_EX'
386 @EndUserText.label: 'DEMO FOR S4'
387 extend view ZCDS_DEMO with ZCDS_DEMO_EX {
388 likp.wadat_ist,
389 ( case likp.wadat_ist
390 when '00000000' then '未交货'
391 else '已交货'
392 end
393 ) as zst
394 }
浙公网安备 33010602011771号