内表SQL
select single *
from @lt_matnr as lt_matnr
where matnr like @lv_matnr_str
into @data(ls_matnr2).
截取字段
SELECT SINGLE
char1 AS text1,
char2 AS text2,
CONCAT( char1,char2 ) AS concat,
CONCAT_WITH_SPACE( char1,char2, 1 ) AS concat_with_space,
INSTR( char1,'12' ) AS instr,
LEFT( char1,3 ) AS left,
LENGTH( char1 ) AS length,
LOWER( char2 ) AS lower,
LPAD( char1,10,'x' ) AS lpad,
LTRIM( char1,' ' ) AS ltrim,
REPLACE( char1,'12','__' ) AS replace,
RIGHT( char1,3 ) as right,
RPAD( char1,10,'x' ) AS rpad,
RTRIM( char1,'3' ) AS rtrim,
SUBSTRING( char1,3,3 ) AS substring,
UPPER( char2 ) AS upper
FROM demo_expressions
INTO @DATA(result).
处理内表数据再连接
*批次库存
data(lt_alv) = gt_alv[].
with +data as ( select distinct werks,matnr,lgort from @lt_alv as lt_alv_db )
select lfgja,lfmon,mchb~werks,mchb~matnr,mchb~lgort,sum( clabs ) as clabs
from mchb
inner join +data as lt_alv_db on mchb~werks = lt_alv_db~werks and mchb~matnr = lt_alv_db~matnr and mchb~lgort = lt_alv_db~lgort
group by lfgja,lfmon,mchb~werks,mchb~matnr,mchb~lgort
into table @data(lt_mchb).
展示内表数据
cl_demo_output=>display( lt_ztssf_wo_log ).
内表处理
gt_fieldcat2[ fieldname = 'DEL' ]-checkbox = 'X'.
sql join 内表
select
prcd_elements~knumh,prcd_elements~kposn,prcd_elements~kbetr,prcd_elements~kpein
from prcd_elements
* for all entries in @lt_lips
join @lt_lips as lt_lips
on prcd_elements~knumh = lt_lips~knumv
and cast( prcd_elements~kposn as numc( 5 ) ) = lt_lips~ebelp
and prcd_elements~kschl in ( 'PO00','POXX' ) and prcd_elements~kinak = ''
into table @data(lt_prcd_elements).
SQL
case mara~extwg
when '600020' "贴牌
then tvlvt~bezei && twewt~ewbez
else tvlvt~bezei
end as note
case @space
when b~loekz then b~lkenz
when b~lkenz then b~loekz
else b~loekz
end as loekz,
SELECT b~*
FROM prcd_elements AS a
JOIN ekpo AS b ON CAST( a~kposn AS NUMC( 5 ) ) EQ b~ebeln
INTO TABLE @DATA(lt_data).
SELECT id, num1, num2,
cast( num1 AS fltp ) / cast( num2 AS fltp ) AS ratio,
div( num1, num2 ) AS div,
mod( num1, num2 ) AS mod,
@offset + abs( num1 – num2 ) AS sum
FROM demo_expressions
INTO CORRESPONDING FIELDS OF TABLE @results
ORDER BY SUM DESCENDING.
SELECT id, CASE char1
WHEN ‘aaaaa’ THEN ( char1 && char2 )
WHEN ‘xxxxx’ THEN ( char2 && char1 )
ELSE @else
END AS text
FROM demo_expressions
INTO CORRESPONDING FIELDS OF TABLE @results.
""解决长度不一致的关联问题
SUBSTRING:注意是从1开始的,从开始位置(起算位置,不是偏移位置),数n个字符
SELECT a~ebeln,a~ebelp,b~vbeln,b~posnr FROM ekpo AS a
JOIN lips AS b ON a~ebeln EQ b~vgbel
AND SUBSTRING( b~vgpos,2,5 ) EQ a~ebelp
WHERE SUBSTRING( b~vgpos,2,5 ) EQ '1'
INTO TABLE @DATA(lt_results).
转类型(inner join也可以这么写,如果fae 报错,直接inner join内表即可)
SELECT SINGLE prt_no
FROM ztfi001
WHERE substring( prt_no,1,4 ) = '1906'
AND CAST( CAST( substring( prt_no,5,4 ) AS NUMC( 4 ) ) AS INT4 ) =
( SELECT MAX( CAST( CAST( substring( prt_no,5,4 ) AS NUMC( 4 ) ) AS INT4 ) )
FROM ztfi001 WHERE substring( prt_no,1,4 ) = '1906' )
INTO @DATA(lv_prt_no).