动态SQL屏幕条件选择(里面还有赋值的新语法)
有时候屏幕条件中使用PARAMETERS时候,如果你为空的话,会查不出数据,但是可能你的想法是不想限制而已,但是系统默认理解为了空值,这个时候,如果取判断一下条件是不是空,在SQL里决定写不写的话,会导致很多的代码,当然理论上是可以的,现在介绍一种可以随机应变的屏幕条件选择的写法,可以省下很多的代码,例子如下。
第一种写法,代码如下:
SELECT-OPTIONS:S_ERZET FOR VBAK-ERZET, S_ERNAM FOR VBAK-ERNAM. PARAMETERS:P_VBELN TYPE VBAK-VBELN, P_ERDAT TYPE VBAK-ERDAT. DATA: LT_WHERE TYPE STRING. IF P_VBELN IS NOT INITIAL. LT_WHERE = 'VBELN = @P_VBELN AND' . ENDIF. IF P_ERDAT IS NOT INITIAL. LT_WHERE = |{ LT_WHERE } ERDAT = @P_ERDAT AND|. ENDIF. LT_WHERE = |{ LT_WHERE } ERZET IN @S_ERZET AND ERNAM IN @S_ERNAM|. *---如果里面有固定值也可以写进去------ "LT_WHERE = |{ LT_WHERE } ERDAT = '20180202' AND ERZET IN @S_ERZET AND ERNAM IN @S_ERNAM|. *当然如果使用CONCATENATE拼字符串的老语法的话,需要如下写法,将固定值需要由3个引号包起来 "CONCATENATE LT_WHERE 'ERDAT = ' '''20180202''' 'AND ERZET IN @S_ERZET AND ERNAM IN @S_ERNAM' INTO LT_WHERE SEPARATED BY SPACE. *-----有时候不知道需要多少个AND连接时候,保险做法可以合理使用去除字符串右边或者左边的and------- "SHIFT LT_WHERE LEFT DELETING LEADING 'AND'. "SHIFT LT_WHERE RIGHT DELETING TRAILING 'AND'. IF LT_WHERE IS NOT INITIAL."判断where条件是不是为空。 SELECT VBELN,ERDAT,ERZET,ERNAM FROM VBAK UP TO 10 ROWS INTO TABLE @DATA(LT_VBAK) WHERE (LT_WHERE) . ELSE. SELECT VBELN,ERDAT,ERZET,ERNAM FROM VBAK UP TO 10 ROWS INTO TABLE @LT_VBAK. ENDIF. CL_DEMO_OUTPUT=>DISPLAY( LT_VBAK ).
第二种写法代码如下(里面还有赋值的新语法):
DATA:S_VBELN TYPE RANGE OF VBAK-VBELN, "相当于select-option:S_WERKS FOR MARD-WERKS,不过这样写就没有屏幕展示了 S_ERDAT TYPE RANGE OF VBAK-ERDAT. DATA:S_ERZET TYPE RANGE OF VBAK-ERZET."相当于select-option:S_WERKS FOR MARD-ERZET DATA:S_ERNAM TYPE RANGE OF VBAK-ERNAM."相当于select-option:S_WERKS FOR MARD-ERNAM S_VBELN = VALUE #( SIGN ='I' OPTION = 'BT' ( LOW = 50000000 HIGH = 60000000 ) ( LOW = 7000 HIGH = 8000 ) OPTION = 'NB' ( LOW = 9000 ) ). DATA: LT_WHERE TYPE STRINGTAB."这个是内表 INSERT CONV #( 'VBELN IN @S_VBELN ' ) INTO TABLE LT_WHERE. INSERT CONV #( 'AND ERDAT IN @S_ERDAT ' ) INTO TABLE LT_WHERE. INSERT CONV #( 'AND ERZET IN @S_ERZET AND ERNAM IN @S_ERNAM' ) INTO TABLE LT_WHERE. IF LT_WHERE IS NOT INITIAL."判断where条件是不是为空。 SELECT VBELN,ERDAT,ERZET,ERNAM FROM VBAK UP TO 10 ROWS INTO TABLE @DATA(LT_VBAK) WHERE (LT_WHERE) . ELSE. SELECT VBELN,ERDAT,ERZET,ERNAM FROM VBAK UP TO 10 ROWS INTO TABLE @LT_VBAK. ENDIF. CL_DEMO_OUTPUT=>DISPLAY( LT_VBAK ).