I need to have a text field in a search form that pulls from our DB, to default to uppercase for the search.
I tried to use java script in the html part of the form for that field but it bombs out after you submit the search.
Here is what I am using.... that bombs out.
%define {
DATABASE = "*local"
DTW_SET_TOTAL_ROWS = "YES"
NPos = "0"
currentRow = "0"
reportTotal = "0.00"
SQLString=""
SQLCondition=""
LogicalConnector=""
%}
%function(DTW_SQL) LastDate(){
select max(BATCH_DATE) from NJDATA.LBXHST
%report {
%row {
$(V1)
%}
%}
%}
%function(DTW_SQL) ReportDetail(SQLString) {
$(SQLString)
%report {
%if (TOTAL_ROWS == "0")
No matching data found.
@dtw_exit()
%endif
%{ <h5>$(SQLString)</h5> %}
<table border="1"
cellpadding="2"
cellspacing="0"
bgcolor="white"
width="500">
<tr bgcolor="lightblue">
<th>Policy No.</th>
<th>Check Amount</th>
<th>Batch Date</th>
<th>Batch No.</th>
<th>Seq. No.</th>
</tr>
%row{
%if (@dtw_rdivrem(currentRow,"2") != "0")
<tr bgcolor="lightblue">
%else
<tr bgcolor="white">
%endif
<td align="right">$(V1)</td>
<td align="right">$(V2)</td>
<td align="right">$(V3)</td>
<td align="right">$(V4)</td>
<td align="right">$(V5)</td>
</tr>
@dtw_add(currentRow,"1",currentRow)
@dtw_add(reportTotal, $(V2), reportTotal)
%}
<tr bgcolor="white">
<td align="right"><b>Total</b></td>
<td align="right"><b>$(reportTotal)</b></td>
<td align="right"><b>$(TOTAL_ROWS) Checks</b></td>
</tr>
</table>
%}
%}
%HTML(ProcessReport){
%{ %if ($(processDate)=="") %}
%{ @ReportDetail("select B01,H53DC1,B03,B04,A00 from njdata.pdk50 where A00='@dtw_ruppercase($(policyNumber))' and F58DC1='LBX' order by B04 DESC") %}
%{ %else %}
%{ @ReportDetail("select B01,H53DC1,B03,B04,A00 from njdata.pdk50 where H53DC1 = $(processDate) and F58DC1='LBX' order by A00") %}
%{ %endif %}
%if ($(policyNumber) != "")
@dtw_assign(SQLCondition," POLICY_NO='$(policyNumber)'")
@dtw_assign(SQLConnector," and ")
%endif
%if ($(mellonBatch) != "")
@dtw_assign(SQLCondition,@dtw_rconcat(SQLCondition,SQLConnector))
@dtw_assign(SQLCondition,@dtw_rconcat(SQLCondition, " BATCH_NO ='$(mellonBatch)'"))
@dtw_assign(SQLConnector," and ")
%endif
%if ($(processDate) != "")
@dtw_assign(SQLCondition,@dtw_rconcat(SQLCondition,SQLConnector))
@dtw_assign(SQLCondition,@dtw_rconcat(SQLCondition, " BATCH_DATE >='$(processDate)'"))
@dtw_assign(SQLConnector," and ")
%endif
%if ($(processDate2) != "")
@dtw_assign(SQLCondition,@dtw_rconcat(SQLCondition,SQLConnector))
@dtw_assign(SQLCondition,@dtw_rconcat(SQLCondition, " BATCH_DATE <='$(processDate2)'"))
@dtw_assign(SQLConnector," and ")
%endif
%if ($(amount1) != "")
@dtw_assign(SQLCondition,@dtw_rconcat(SQLCondition,SQLConnector))
@dtw_assign(SQLCondition,@dtw_rconcat(SQLCondition," CHECK_AMNT >= $(amount1)"))
@dtw_assign(SQLConnector," and ")
%endif
%if ($(amount2) != "")
@dtw_assign(SQLCondition,@dtw_rconcat(SQLCondition,SQLConnector))
@dtw_assign(SQLCondition,@dtw_rconcat(SQLCondition," CHECK_AMNT <= $(amount2)"))
@dtw_assign(SQLConnector," and ")
%endif
@ReportDetail(@dtw_rconcat("select POLICY_NO, CHECK_AMNT, BATCH_DATE, BATCH_NO, SEQ_NO from NJDATA.LBXHST where ",@dtw_rconcat(SQLCondition," order by POLICY_NO")))
%}
%HTML(PaymentReport) {
<html>
<head>
<style type="text/css">
body,td{font-family:arial,sans-serif;}
</style>
<title>NJCURE Check Reporting</title>
</head>
<body>
<h3>Premium Payments Received From Mellon Lockbox As Of @LastDate()</h3>
<form method="post" action="ProcessReport">
<table border="0">
<tr>
<td>Mellon Bank Batch No.</td>
<td align="left">
<input type="text"
name="mellonBatch"
size="4"
maxlength="4"></td>
</tr>
<tr>
<td>Payments Received From Policy</td>
<td align="right">
<input type="text"
name=policyNumber
size="10"
maxlength="10"></td>
<td colspan="2">(Full policy #, including leading 'N')</td>
</tr>
<tr>
<td>Payments Received Between</td>
<td align="right">
<input type="text"
name="processDate"
size="10"
maxlength="10"></td>
<td align="center">and</td>
<td align="left">
<input type="text"
name="processDate2"
size="10"
maxlength="10"></td>
<td>(YYYY-MM-DD format)</td>
</tr>
<tr>
<td>Payment Amounts Between</td>
<td align="right">
<input type="text"
name="amount1"
size="10"
maxlength="10"></td>
<td align="center">and</td>
<td alight="right">
<input type="text"
name="amount2"
size="10"
maxlength="10"></td>
</tr>
</table>
<br>
<br>
<input type="submit"
name="buttonSubmit"
value="Display Payments">
</form>
</html>
%}
I get a "NET.DATA DTW_SQL Error: Function ReportDetail: The statement failed. Keyword BY not expected. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> ¬= IN NOT. (SQLSTATE 42601, SQLCODE -199)"
:(
