0

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)&nbsp;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)"

:(

2
Contributors
3
Replies
4
Views
5 Years
Discussion Span
Last Post by BitBlt
0

My guess is that your final SQLString has an extra "AND" after all the selection criteria. So, when you concatenate the " order by" to the end, you wind up with "...myCriterion1 = 'A' and myCriterion2 = 'B' and order by Policy_No". You'll need to strip off the last "and" before concatenating the order-by clause.

You can verify this by trapping and displaying the SqlString.

Good luck!

0

:( Did not work....
If I take out the java script in the Html section everything works fine....

NET.DATA Error: Quoted string at line 139 of file /www/webserver/macros/PremiumPayments.ndm can not span multiple lines.NET.DATA Error: An incorrect symbol (%) was encountered at line 141 of file /www/webserver/macros/PremiumPayments.ndm.NET.DATA Error: Quoted string at line 149 of file /www/webserver/macros/PremiumPayments.ndm can not span multiple lines.NET.DATA Error: Quoted string at line 159 of file /www/webserver/macros/PremiumPayments.ndm can not span multiple lines.NET.DATA Error: Quoted string at line 169 of file /www/webserver/macros/PremiumPayments.ndm can not span multiple lines.NET.DATA Error: Quoted string at line 179 of file /www/webserver/macros/PremiumPayments.ndm can not span multiple lines.NET.DATA Error: Quoted string at line 189 of file /www/webserver/macros/PremiumPayments.ndm can not span multiple lines.NET.DATA Error: Unexpected end-of-file occurred while parsing object /www/webserver/macros/PremiumPayments.ndm at line 331.

0

Did you trap the SQL statement that is being passed to the server? Sometimes just eyeballing the statement will make obvious what the problem is. If you haven't trapped it, please do so. If you can't figure it out from that, post it and let us have a look.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.