当前位置：首页 > news >正文

ABAP RFC SQL 模糊查询和多个区间条件

news 2024/10/31 17:53:45

对于非选择屏幕的情况，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;}}}

查看全文

http://www.mrgr.cn/news/62611.html