ABAP RFC SQL 模糊查询和多个区间条件
对于非选择屏幕的情况,RFC接口输入数据后,如何处理字符串模糊查询、日期区间查询、数字区间查询:
一、所有字符支持模糊查询,在SAP SQL中,使用 %S%来实现。
二、区间查询有3种情况:
1、没有值输入,程序需要填入最大上下限
BETWEEN 20200101 TO 99991231
2、输入单个值,如20240930,代码中在BETWEEN,TO两个字段填入
BETWEEN 20240930 TO 20240930
3、输入两个值,如20240901-20240930代码中在BETWEEN,TO两个字段填入
BETWEEN 20240901 TO 20240930
FUNCTION ZSAP_LIGHT_ADS_GET.
*"----------------------------------------------------------------------
*"*"本地接口:
*" IMPORTING
*" VALUE(ZSAVEDATE) TYPE CHAR8
*" VALUE(ZSAVETIME) TYPE CHAR6
*" VALUE(ZTYPE) TYPE CHAR10
*" VALUE(ZSPNUMBER) TYPE CHAR20
*" VALUE(ZDATE_FROM) TYPE CHAR8
*" VALUE(ZDATE_TO) TYPE CHAR8
*" VALUE(ZFPNUMBER) TYPE CHAR50
*" VALUE(VIN) TYPE CHAR20
*" VALUE(ZFPTYPE) TYPE CHAR50
*" VALUE(ZMONEY_FROM) TYPE J_1BEXBASE
*" VALUE(ZMONEY_TO) TYPE J_1BEXBASE
*" VALUE(TTNAME) TYPE CHAR50
*" VALUE(SHNUMBER) TYPE CHAR30
*" VALUE(ZEMAIL) TYPE CHAR50
*" VALUE(ZBZ) TYPE CHAR50
*" TABLES
*" OT_LIST STRUCTURE ZTLIGHT_ADS
*"----------------------------------------------------------------------DATA:lv_ZTYPE TYPE string,lv_ZSPNUMBER TYPE string,lv_ZFPNUMBER TYPE string,lv_VIN TYPE string,lv_ZFPTYPE TYPE string,lv_TTNAME TYPE string,lv_SHNUMBER TYPE string,lv_ZEMAIL TYPE string,lv_ZBZ TYPE string.CONCATENATE '%' ZTYPE '%' INTO lv_ZTYPE.CONCATENATE '%' ZSPNUMBER '%' INTO lv_ZSPNUMBER.CONCATENATE '%' ZFPNUMBER '%' INTO lv_ZFPNUMBER.CONCATENATE '%' VIN '%' INTO lv_VIN.CONCATENATE '%' ZFPTYPE '%' INTO lv_ZFPTYPE.CONCATENATE '%' TTNAME '%' INTO lv_TTNAME.CONCATENATE '%' SHNUMBER '%' INTO lv_SHNUMBER.CONCATENATE '%' ZEMAIL '%' INTO lv_ZEMAIL.CONCATENATE '%' ZBZ '%' INTO lv_ZBZ.IF ( ZDATE_from is INITIAL AND ZDATE_to is INITIAL ) .ZDATE_from = '20200101'.ZDATE_to = '99991231'.ENDIF.IF ( ZDATE_from is not INITIAL AND ZDATE_to is INITIAL ) .ZDATE_to = ZDATE_from.ENDIF.IF ( ZDATE_from is INITIAL AND ZDATE_to is not INITIAL ) .ZDATE_from = ZDATE_to.ENDIF.IF ( ZMONEY_from = 0 AND ZMONEY_to = 0 ) .ZMONEY_from = '-99999999'.ZMONEY_to = '99999999999'.ENDIF.IF ( ZMONEY_from <> 0 AND ZMONEY_to = 0 ) .ZMONEY_to = ZMONEY_from.ENDIF.IF ( ZMONEY_from = 0 AND ZMONEY_to <> 0 ) .ZMONEY_from = ZMONEY_to.ENDIF.SELECT * FROM ZTLIGHT_ADSWHERE ZTYPE like @lv_ZTYPEAND ZSPNUMBER like @lv_ZSPNUMBERAND ZFPNUMBER like @lv_ZFPNUMBERAND VIN like @lv_VINAND ZFPTYPE like @lv_ZFPTYPEAND TTNAME like @lv_TTNAMEAND SHNUMBER like @lv_SHNUMBERAND ZEMAIL like @lv_ZEMAILAND ZBZ like @lv_ZBZAND ZDATE BETWEEN @ZDATE_from AND @ZDATE_toAND ZMONEY BETWEEN @ZMONEY_from AND @ZMONEY_toINTO TABLE @OT_LIST.ENDFUNCTION.
注意,在UI端要检查一下日期是否合法,数字区间要规范成0
Get_InputParameters inputpara = new Get_InputParameters(){ZTYPE = toolStripTextBox1.Text,ZSPNUMBER = toolStripTextBox2.Text,VIN = toolStripTextBox3.Text,ZFPNUMBER = toolStripTextBox4.Text,ZDATE_FROM = MT.DATE_CHECK(toolStripTextBox5.Text),ZDATE_TO = MT.DATE_CHECK(toolStripTextBox6.Text),ZMONEY_FROM = MT.num_from(toolStripTextBox7.Text),ZMONEY_TO = MT.num_to(toolStripTextBox8.Text)};
using ClosedXML.Excel;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Drawing.Diagrams;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;//MyTools MT
namespace Light
{internal class MT{public static double num_from(string s){double tmp = 0;if (s.Count() > 0) { tmp = double.Parse(s); }return tmp;}public static double num_to(string s){double tmp = 0;if (s.Count() > 0) { tmp = double.Parse(s); }return tmp;}public static string DATE_CHECK (string s){string tmp = "";if (s.Count()>0){ if (IsValidDate(s)){tmp = s;}else {MessageBox.Show("注意:这个日期是错误的!" + s );tmp = "20240930";}}return tmp;}public static string PickBox(ClosedXML.Excel.IXLCell cell){string tmp = "";if (!cell.IsEmpty() & cell.Value.IsText) { tmp = cell.Value.GetText(); } else if (!cell.IsEmpty() & cell.Value.IsNumber) { tmp = cell.Value.GetNumber().ToString(); }return tmp;}//ClosedXML可能会把日期认成字符串public static string PickDate(ClosedXML.Excel.IXLCell cell){string tmp = "";if (cell.Value.IsDateTime){System.DateTime dateValue = cell.GetValue<System.DateTime>();tmp = dateValue.ToString("yyyyMMdd");}else{tmp = System.DateTime.Parse(cell.Value.GetText()).ToString("yyyyMMdd");}/*if (cell.DataType == XLDataType.DateTime){System.DateTime dateValue = cell.GetValue<System.DateTime>();tmp = dateValue.ToString("yyyyMMdd");}else{tmp = System.DateTime.Parse(cell.GetString()).ToString("yyyyMMdd");}*/return tmp;}public static bool IsValidDate(string dateString){if (dateString == null || dateString.Length != 8)return false;int year = int.Parse(dateString.Substring(0, 4));int month = int.Parse(dateString.Substring(4, 2));int day = int.Parse(dateString.Substring(6, 2));// 检查年份是否在1-9999范围内if (year < 1 || year > 9999)return false;// 检查月份是否在1-12范围内if (month < 1 || month > 12)return false;// 根据年份和月份判断天数的合法范围int daysInMonth = DateTime.DaysInMonth(year, month);// 检查日期是否在1-最大日数范围内if (day < 1 || day > daysInMonth)return false;return true;}}}