PB dynamically changes the WHERE clause of the DW

PB Experience 4-dynamically change the WHERE clause of the DW

In the use of the filter for DW, it is mentioned that if the retrieval condition factor is not fixed, you can use the method of dynamically changing the WHERE clause of the DW.

The implementation of this method is this, do a no parameter DW, in the program, The SQL syntax of the DW object is obtained first through DW.OBJECT.DATAWINDOW.TABLE.SELECT, and the SQL syntax can be split into the select part, the Whrer part, the GROUP by section, and the having part. If it is union all then it should be necessary to use an array variable to receive the corresponding part, these variables are best used environment variables.

Then make a query for the condition of the DW, which is you can let the user input query criteria field items. Then in the Query button under the script to do as follows, read the query criteria of the field corresponding to the condition value, to judge each condition value, if it is not empty to generate the corresponding conditional syntax, according to the different fields, the synthesis of conditional syntax attention to quotation marks and type conversion. The logic between the conditions, the priority of operations, and so on.

After synthesizing the entire new conditional syntax with the whrer part of the original SQL syntax, merge the SQL syntax with the rest of the SQL section into the complete new condition.

Pass the new SQL syntax back to the DW via DW.OBJECT.DATAWINDOW.TABLE.SELECT and run the retrieve function.

String ls_odd
String Ls_provider
String Ls_receiver
String Ls_oddtype
DateTime ldt_start,ldt_end
String Ls_stockcode
String Ls_stockname
String Ls_sql
String Ls_where,ls_where1
String Ls_type1
String ls_type2
String Ls_cid
Datawindowchild ldwc_1
Dw_inquire.accepttext ()
String LS_SQL1
Ls_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 If
If 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 If

If 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 If

If 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 If

If 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 If

If 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 If

If 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 If
If 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 If
Else
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 If
End If
If 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 If

If 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 If

If 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_where1
Dw_print.settransobject (SQLCA)
Dw_print.retrieve ()
This method is best to check the composition after the composition of the correct syntax, to consider whether the original SQL syntax has a WHERE condition, if there is no new composition to add the WHERE keyword.

========================= Practice

Sql:

Select Bill_id,bill_date,org_id,b.memb_id,memb_nm,decode (Sex, ' M ', ' Male ', ' F ', ' female ', 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) b
where s.memb_id = b.memb_id (+)

PB:

String Ls_value,ls_sql,ls_where
String Ls_dt_str,ls_dt_end
String Ls_ddlb_1,ls_sle_1
String ls_ddlb_2

Ls_sql=dw_1.object. DataWindow. TABLE. SELECT
MessageBox ("Hint", ls_sql)
If POS (lower (Ls_sql), ' + ') >0 Then
Ls_sql=mid (Ls_sql,1,pos (ls_sql, ' + ') + 1)
MessageBox ("post-intercept SQL", Ls_sql)
End If

Time
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 ("Hint", "start time cannot be less than end time")
Return
End If
Ls_where + = "and s.bill_date between To_date ('" +ls_dt_str+ "', ' Yyyy-mm-dd ') and To_date ('" +ls_dt_end+ "', ' yyyy-mm-dd ')"

Member
Ls_ddlb_1 = Trim (ddlb_1.text)
Ls_sle_1 = Upper (Trim (sle_1.text))
IF ls_ddlb_1 = ' member name ' then
If ls_sle_1 = "Then
MessageBox ("Prompt", "Please enter member name")
Return
Else
Ls_where + = "and upper (b.memb_nm) like '%" +ls_sle_1+ "% '"
End If
ELSEIF ls_ddlb_1 = ' Member id ' then
IF ls_sle_1 = "Then
MessageBox ("Confirm", "Please lose member ID.")
Return
ElseIf isnumber (ls_sle_1) = False Then
MessageBox ("Confirm", "input is not a number.")
Return
Else
Ls_where + = "and b.memb_id = '" "+ls_sle_1+" "
END IF
END IF

Stores
ls_ddlb_2 = Trim (ddlb_2.text)
If ls_ddlb_2 = ' all ' Then
Ls_where + = ""
Else
Ls_where + = "and s.org_id = '" "+ls_ddlb_2+" "
End If

MessageBox (' Query SQL ', Ls_sql + ls_where)
Dw_1.object.datawindow.table.select = Ls_sql + ls_where
Dw_1.settransobject (SQLCA)
Dw_1.retrieve ()

PB dynamically changes the WHERE clause of the DW