# Problems in while with if to accumulate

2

I have a problem I want to do an accumulation of parts and payments within a `while` and then accumulate based on the employee and then paint the data on screen.

But you skip the information related to the name by a column.

``````2939    DELIA.MORENO    13170   \$299.84 //este es el dato correcto si solo busco
``````

this employee.

If I look for all the employees, that's where the detail comes from.

`````` 2912   ASIGNACION1 3941    \$106.41

2913   ASIGNACION2 1358    \$38.67

2914   CAPTURISTA1 2208    \$66.31

2936   JORGE.DIAZ  10544   \$327.09

2938   APOLINARIA.AGUSTIN  20  \$0.52

2939   DELIA.MORENO    17168   \$384.93

2940   DIANA.HERNANDEZ 13170   \$299.84

2941   GUILLERMO.BELLO 16507   \$374.72

2944   ELENA.ZAVALA    16380   \$370.13

2945   ALEJANDRA.ESCALANTE 20198   \$453.7

``````

And Delia Moreno has other pieces.

Here is the code for this calculation.

``````\$_Query = "SELECT CR.CORTE_NUM,count(RL.codigo_qp) cant,RL.USUARIO_ID,US.USER_NAME "
+ " from XXQP.XXQP_REMESA_CORTE_LN_TBL rl left join XXQP.XXQP_REMESA_CORTE_HD_TBL cr on RL.CORTE_ID = CR.CORTE_ID "
+ " inner join APPLSYS.FND_USER US on US.USER_ID=RL.USUARIO_ID "
+ " where 1 = 1";

if(!StRemesa.equals("")){
\$_Query = \$_Query +" and cr.remesa_id="+StRemesa; //Remesa
}
if(!StCapturista.equals("")){
\$_Query = \$_Query +" and rl.usuario_id="+StCapturista;//Capturista
}
if(!StFIni.equals("")){ //Fechas
\$_Query = \$_Query +" and rl.fecha>=to_date('"+StFIni+" 00:00:00','yyyy-mm-dd hh24:mi:ss') and rl.fecha<=to_date('"+StFFin+" 23:59:00','yyyy-mm-dd hh24:mi:ss')";
}

\$_Query = \$_Query +" and rl.origen=3 and rl.usuario_id not in (2731,2912,2913,2914,2936) and rl.estatus!='CANCELADO' group by CR.CORTE_NUM,RL.USUARIO_ID,US.USER_NAME "
+ "order by RL.USUARIO_ID,CR.CORTE_NUM";
rs1 = st1.executeQuery(\$_Query);

while (rs1.next()) {
StNCap = rs1.getString("USUARIO_ID");
StCap = rs1.getString("USER_NAME");

\$_Query = "SELECT HP.PARTY_NAME,LK.MEANING, COALESCE(IT.ATTRIBUTE19,'0.00') comision from XXQP.XXQP_REMESA_CORTE_HD_TBL cr inner join "
+ "XXQP.XXQP_REMESA_TBL r on CR.REMESA_ID = R.REMESA_ID inner join APPLSYS.FND_LOOKUP_VALUES lk "
+ "on CR.PLAZA_ID = LK.LOOKUP_CODE and LK.LOOKUP_TYPE='XXQP_PLAZAS' and LK.LANGUAGE='ESA' inner join AR.HZ_CUST_ACCOUNTS hz "
+ "on  r.cliente_id = HZ.CUST_ACCOUNT_ID inner join AR.HZ_PARTIES hp on  HZ.PARTY_ID = HP.PARTY_ID inner join INV.MTL_SYSTEM_ITEMS_B "
+ "it on r.producto_id = IT.INVENTORY_ITEM_ID and r.almacen_id = IT.ORGANIZATION_ID where  CR.CORTE_NUM='"+rs1.getString("corte_num")+"'";
rs2 = Gdr.consultar(\$_Query, 2);

if (rs2.next()){
StComm = rs2.getString("comision");
Pago=Double.parseDouble(rs2.getString("comision")) * Integer.parseInt(rs1.getString("cant"));

out.print("<tr bgcolor='lightgray' ALIGN='CENTER'>");
if (cont >= 1) {
out.print(tr bgcolor='lightgray' ALIGN='CENTER');
out.print(td + StNCap);
out.print(/td);
out.print(td + StCap);
out.print(/td);
out.print(td + piezas);
out.print(/td);
out.print(td + format.format(PagoTotal));
out.print(/td);
out.print(/tr);
piezas = 0;
PagoTotal = 0.0;
}
}
PagoTotal +=Pago;
piezas += rs1.getInt("cant");
cont = cont+1;
}
rs2.close();
if (r == 0) {
%>
<tr bgcolor='white' ALIGN='CENTER'>
<%
r = 1;
} else {
r = 0;
%>
<tr bgcolor='#E6E6E6' ALIGN='CENTER'>
<% } %>
</tr>
<%
}
out.print(tr bgcolor='lightgray' ALIGN='CENTER');
out.print(td + StNCap);
out.print(/td);
out.print(td + StCap );
out.print(/td);
out.print(td + piezas );
out.print(/td);
out.print(td> + format.format(PagoTotal));
out.print(/td);
out.print(/tr);
st1.close();
Gdr.cerrar();
``````

asked by Jairo Ordaz Moreno 26.10.2016 в 20:10
source

1

Your problem is in the `if (rs2.next())` that you have within the `while` since you are calling the `next()` method without knowing if there will be another record or not.

Assuming that you are using a `Iterator` , one way to check it is by using the `.hasNext()` method, leaving the code such that:

``````while (rs1.hasNext()) {
rs1.next();
//Parte del código que tu tenias
if(rs2.hasNext()){
rs2.next();
//resto del código
}
}
``````

You can also evaluate changing the type of loop by a `for` , with which you can surely have more control

source
0

The problem is that it does not show the data of the last employee, try something like this:

``````if (rs2.next()) {
StComm = rs2.getString("comision");
Pago = Double.parseDouble(rs2.getString("comision")) * Integer.parseInt(rs1.getString("cant"));

if (cont >= 1) {
out.print("<tr bgcolor='lightgray' ALIGN='CENTER'>");
out.print("<td>" + StNCap);
out.print("</td>");
out.print("<td>" + StCap);
out.print("</td>");
out.print("<td>" + piezas);
out.print("</td>");
out.print("<td>" + format.format(PagoTotal));
out.print("</td>");
out.print("</tr>");
piezas = 0;
PagoTotal = 0.0;
}
}

PagoTotal += Pago;
piezas += rs1.getInt("cant");
cont = cont + 1;

}

if (cont >= 1) {
out.print("<tr bgcolor='lightgray' ALIGN='CENTER'>");
out.print("<td>" + StNCap);
out.print("</td>");
out.print("<td>" + StCap);
out.print("</td>");
out.print("<td>" + piezas);
out.print("</td>");
out.print("<td>" + format.format(PagoTotal));
out.print("</td>");
out.print("</tr>");
}
``````

Good luck!