PB心得4-动态改变DW的WHERE子句
在DW的FILTER的使用一文中提到如果检索条件因素不固定的话，可以使用动态改变DW的WHERE子句的方法。
这个方法的实现是这样的，做一个没有参数的DW，在程序中，先通过DW.OBJECT.DATAWINDOW.TABLE.SELECT来取得DW对象的SQL语法，可以对SQL语法拆分出SELECT部分，WHRER部分，GROUP BY部分，HAVING部分。如果是UNION ALL 那么应该需要用数组变量来接收对应的部分，这些变量最好是用环境变量。
再做个用于查询条件的DW，里面是你可以让用户输入查询条件的字段项目。然后在查询按钮的脚本下做如下面的步骤，读取查询条件的字段对应的条件值，对每个条件值做判断，如果是不为空的才需要生成对应的条件语法，根据字段的不同，合成条件语法时注意引号和类型转换。条件之间的逻辑，运算优先级等。
合成后的整个新的条件语法与原来的SQL语法的WHRER部分合并，再与其他的SQL的部分合并成完整的新的条件的SQL语法。
把新的SQL语法通过DW.OBJECT.DATAWINDOW.TABLE.SELECT传回给DW，并运行检索函数。
string ls_oddstring ls_providerstring ls_receiverstring ls_oddtypedatetime ldt_start,ldt_endstring ls_stockcodestring ls_stocknamestring ls_sqlstring ls_where,ls_where1string ls_type1string ls_type2string ls_ciddatawindowchild ldwc_1dw_inquire.accepttext()string ls_sql1ls_sql = mid(is_sql,1,pos(is_sql,'union all') - 1)ls_sql1 = mid(is_sql,pos(is_sql,'union all') + 9,len(is_sql) - pos(is_sql,'union all') +1)
ls_odd = dw_inquire.object.oddno[1]
ls_provider = dw_inquire.object.provider[1]ls_receiver = dw_inquire.object.receiver[1]ls_oddtype = dw_inquire.object.oddtype[1]ls_stockcode = dw_inquire.object.stockcode[1]ls_stockname = dw_inquire.object.stockname[1]ldt_start = dw_inquire.object.dstart[1]ldt_end = dw_inquire.object.dend[1]ls_type1 = dw_inquire.object.type1[1]ls_type2 = dw_inquire.object.type2[1]ls_cid = dw_inquire.object.cid[1]if pos(lower(is_sql),'where')>0 then ls_where = ' ' ls_where += " and out_main.auditing = '"+ls_type2+"' "else ls_where = ' where ' ls_where += " out_main.auditing = '"+ls_type2+"' "end ifif pos(lower(ls_sql1),'where')>0 then ls_where1 = ' ' ls_where1 += " and group_out.auditing = '"+ls_type2+"' "else ls_where1 = ' where ' ls_where1 += " group_out.auditing = '"+ls_type2+"' "end if
if not(isnull(ls_odd) or len(trim(ls_odd)) = 0) then if len(ls_where) > 5 then ls_where += ' and ' ls_where += " out_main.odd_no like '%" + ls_odd + "%' " if len(ls_where1) > 5 then ls_where1 += ' and ' ls_where1 += " group_out.odd_no like '%" + ls_odd + "%' "end ifif not(isnull(ls_provider) or len(trim(ls_provider)) = 0) then
if len(ls_where) > 5 then ls_where += ' and ' ls_where += " out_main.provider = '" + ls_provider + "' " if len(ls_where1) > 5 then ls_where1 += ' and ' ls_where1 += " group_out.provider = '" + ls_provider + "' "end ifif not(isnull(ls_receiver) or len(trim(ls_receiver)) = 0) then
if len(ls_where) > 5 then ls_where += ' and ' ls_where += " out_main.receiver= '" + ls_receiver + "' " if len(ls_where1) > 5 then ls_where1 += ' and ' ls_where1 += " group_out.receiver = '" + ls_receiver + "' "end ifif not(isnull(ls_oddtype) or len(trim(ls_oddtype)) = 0) then
if len(ls_where) > 5 then ls_where += ' and ' ls_where += " out_main.receiver_provider_type= '" + ls_oddtype + "' " if len(ls_where1) > 5 then ls_where1 += ' and ' ls_where1 += " group_out.receiver_provider_type= '" + ls_oddtype + "' " end if if not(isnull(ls_stockcode) or len(trim(ls_stockcode)) = 0) then if len(ls_where) > 5 then ls_where += ' and ' //if len(ls_stockcode) = 5 then ls_where += " out_child.pid = " + ls_stockcode + " " // else // ls_where += " out_child.stock_code like '" + ls_stockcode + "%' " //end if if len(ls_where1) > 5 then ls_where1 += ' and ' ls_where1 += " 1 = 2 "end ifif not(isnull(ls_stockname) or len(trim(ls_stockname)) = 0) then
if len(ls_where) > 5 then ls_where += ' and ' ls_where += " out_child.stock_name like '%" + ls_stockname + "%' " if len(ls_where1) > 5 then ls_where1 += ' and ' ls_where1 += " 1=2 "end ifif not(isnull(ls_cid) or len(trim(ls_cid)) = 0) then
if len(ls_where) > 5 then ls_where += ' and ' ls_where += " out_child.cid = " + ls_cid + " " if len(ls_where1) > 5 then ls_where1 += ' and ' ls_where1 += " 1=2 "end ifif ls_type2 = '1' then if not(isnull(ldt_start)) then if len(ls_where) > 5 then ls_where += ' and ' ls_where += " out_main.auditdate >= '" + string(ldt_start,'yyyy-mm-dd') +" 00:00:00' " end if if not(isnull(ldt_end)) then if len(ls_where) > 5 then ls_where += ' and ' ls_where += " out_main.auditdate <= '" + string(ldt_end,'yyyy-mm-dd') +" 23:59:59' " end ifelse if not(isnull(ldt_start)) then if len(ls_where) > 5 then ls_where += ' and ' ls_where += " out_main.odd_date >= '" + string(ldt_start,'yyyy-mm-dd') +" 00:00:00' " end if if not(isnull(ldt_end)) then if len(ls_where) > 5 then ls_where += ' and ' ls_where += " out_main.odd_date <= '" + string(ldt_end,'yyyy-mm-dd') +" 23:59:59' " end ifend ifif not(isnull(ldt_start)) then if len(ls_where1) > 5 then ls_where1 += ' and ' ls_where1 += " group_out.odd_date >= '" + string(ldt_start,'yyyy-mm-dd') +" 00:00:00' "end ifif not(isnull(ldt_end)) then
if len(ls_where1) > 5 then ls_where1 += ' and ' ls_where1 += " group_out.odd_date <= '" + string(ldt_end,'yyyy-mm-dd') +" 23:59:59' "end ifif not(isnull(ls_type1) or len(trim(ls_type1)) = 0) then
if len(ls_where) > 5 then ls_where += ' and ' ls_where += " out_main.relate_no like '%" + ls_type1 + "%' " if len(ls_where1) > 5 then ls_where1 += ' and ' ls_where1 += " 1=2 "end if//messagebox('',is_sql + ls_where)dw_print.object.datawindow.table.select = ls_sql + ls_where + ' union all ' + ls_sql1 + ls_where1dw_print.settransobject(sqlca)dw_print.retrieve() 这种方法最好就是在合成后检查一下合成的语法是否正确，要考虑原SQL语法是否有WHERE条件，如果没有新合成的要加上WHERE关键字。
=========================实践
SQL：
select bill_id,bill_date,org_id,b.memb_id,memb_nm,decode(sex,'M','男','F','女',sex) sex,bill_money,cash_money,card_money,accm_money,coupon_money from
(select bill_id,bill_date,org_id,memb_id,bill_money,cash_money,card_money,accm_money,coupon_money from TB_AX006 t)s, (select memb_id,memb_nm,sex from tb_ax001 a) bwhere s.memb_id = b.memb_id(+)
PB：
String ls_value,ls_sql,ls_where
String ls_dt_str,ls_dt_endString ls_ddlb_1,ls_sle_1string ls_ddlb_2 ls_sql=dw_1.OBJECT.DATAWINDOW.TABLE.SELECT//messagebox("提示",ls_sql) if pos(lower(ls_sql),'+')>0 then ls_sql=mid(ls_sql,1,pos(ls_sql,'+') + 1) //messagebox("截取后sql",ls_sql)end if//时间
ls_dt_str = string(dp_1.value,'yyyy/mm/dd')ls_dt_end = string(dp_2.value,'yyyy/mm/dd')if dp_1.value > dp_2.value then messagebox("提示","开始时间不能小于结束时间") returnend ifls_where += " and s.bill_date between to_date('"+ls_dt_str+"','yyyy-mm-dd') and to_date('"+ls_dt_end+"','yyyy-mm-dd') "//会员
ls_ddlb_1 = trim(ddlb_1.text)ls_sle_1 = upper(trim(sle_1.text))IF ls_ddlb_1 = '会员姓名' THEN if ls_sle_1 = '' then messagebox("提示","请输入会员姓名") return else ls_where += " and upper(b.memb_nm) like '%"+ls_sle_1+"%' " end ifELSEIF ls_ddlb_1 = '会员ID' THEN IF ls_sle_1 = '' THEN MessageBox("确认", "请输会员ID.") Return elseif IsNumber(ls_sle_1) = false then MessageBox("确认", "输入的不是数字.") Return else ls_where +=" and b.memb_id = '"+ls_sle_1+"' " END IFEND IF//门店
ls_ddlb_2 = trim(ddlb_2.text)if ls_ddlb_2 = '全部' then ls_where += ""else ls_where += " and s.org_id ='"+ls_ddlb_2+"'"end if//messagebox('查询sql',ls_sql + ls_where)dw_1.object.datawindow.table.select = ls_sql + ls_wheredw_1.settransobject(sqlca)dw_1.retrieve()