wjh7554
asked on
Pseudocode Enquiry: pls correct.
This is my DB schema...
P.Code Qty. Amt.
=====================
ABC 3 100
ABC 6 2
ABC 12 12
MNB 4 23
PLM 2 12
I need to do some sort like summary page to sum all the same p.code together to see the Qty and Amt.
This is my pseudocode,
-- select the data from DB order by P.COde (to make sure it's in sequence)
-- String ori_pcode = rs.getString("PCode");
int counter = 1;
public Integer getQty() {
if (Integer.parseInt(counter) == 1) {
amt = add amt;
} else {
..
...
????????
Actually I don;t know how to do....
P.Code Qty. Amt.
=====================
ABC 3 100
ABC 6 2
ABC 12 12
MNB 4 23
PLM 2 12
I need to do some sort like summary page to sum all the same p.code together to see the Qty and Amt.
This is my pseudocode,
-- select the data from DB order by P.COde (to make sure it's in sequence)
-- String ori_pcode = rs.getString("PCode");
int counter = 1;
public Integer getQty() {
if (Integer.parseInt(counter)
amt = add amt;
} else {
..
...
????????
Actually I don;t know how to do....
-- You can directly get this from table.
select sum(Amt) from DB group by Code;
-- group by will order for you also
select sum(Amt) from DB group by Code;
-- group by will order for you also
ASKER
Yes did that but got some problem...
SQL Statement : Select prdcode, prddesp, sum(qty) from DB group by prdcode, prddesp order by prdcode
But how am I going to retrieve the "SUM*QTY) from the statement?
Normal I will do this..
public String prddesp = "";
.
..
prddesp = rs.getSring("prddesp");
..
..
public String getPrddesp() {
return prddesp;
}
But how can I retirieve the total of QTY ( sum(qty) ) ??
SQL Statement : Select prdcode, prddesp, sum(qty) from DB group by prdcode, prddesp order by prdcode
But how am I going to retrieve the "SUM*QTY) from the statement?
Normal I will do this..
public String prddesp = "";
.
..
prddesp = rs.getSring("prddesp");
..
..
public String getPrddesp() {
return prddesp;
}
But how can I retirieve the total of QTY ( sum(qty) ) ??
ASKER
allahabad,
seems that I need to do some sort like programming already.
SQL statement = "Select pCode, InvNum SUM(qty) as a, SUM(InvAmt) as b from DB group by pCode, InvNum order by pCode "
//No problems.
But my results is liket his
PCode InvNum InvAmt
=======================
DDU DD8956 369
ABC BA1001 442
ABC PK8968 333
PKL LMO233 100
PKL LM0244 312
I need to make s sub-total at beside. How could I do that?
Still need programming right?? Can suggest the psedocode, in JSP or JavaBean?
PCode InvNum InvAmt sub-total
========================== ========== =
DDU DD8956 369 369
ABC BA1001 442
ABC PK8968 333 776
PKL LM0233 100
PKL LM0244 312 412
Grand Total : 1557
What do you think??
seems that I need to do some sort like programming already.
SQL statement = "Select pCode, InvNum SUM(qty) as a, SUM(InvAmt) as b from DB group by pCode, InvNum order by pCode "
//No problems.
But my results is liket his
PCode InvNum InvAmt
=======================
DDU DD8956 369
ABC BA1001 442
ABC PK8968 333
PKL LMO233 100
PKL LM0244 312
I need to make s sub-total at beside. How could I do that?
Still need programming right?? Can suggest the psedocode, in JSP or JavaBean?
PCode InvNum InvAmt sub-total
==========================
DDU DD8956 369 369
ABC BA1001 442
ABC PK8968 333 776
PKL LM0233 100
PKL LM0244 312 412
Grand Total : 1557
What do you think??
I am not sure how to do this via sql but in java just do this:
create an object called Product that has 3 properties:
class Product implements Serializable {
String pCode;
int qty = 0;
int amt = 0;
Product (String newPCode) { this.pCode = newPCode;}
public String getPCode() { return this.pCode; }
public void setPCode(String newPCode) { this.pCode = newPCode; }
public int getQty() { return this.qty; }
pulbic void setQty (int newQty) { this.qty = newQty; }
public int getAmt() { return this.amt; }
pulbic void setAmt (int newAmt) { this.amt = newAmt; }
}
// now you can add that to an arrayList
SQL statement = "Select pCode, SUM(qty) as qty_sum, SUM(amt) as amt_sum from table group by pCode order by pCode "
java.util.List products = new java.util.ArrayList();
while (rs.next()) {
Product currentProduct = new Product(rs.getString("pcod e"));
currentProduct.setQty(rs.g etInt("qty _sum");
currentProduct.setAmt(rs.g etInt("amt _sum");
products.add(currentProduc t);
}
Now you can iterate over products
java.util.Iterator prodIter = products.iterator();
while (prodIter.hasNext()) {
Product currentProduct = (Product) prodIter.next();
out.print(currentProduct.g etPCode()) ;
out.print(currentProduct.g etQty());
out.print(currentProduct.g etAmt());
}
HTH,
CJ
create an object called Product that has 3 properties:
class Product implements Serializable {
String pCode;
int qty = 0;
int amt = 0;
Product (String newPCode) { this.pCode = newPCode;}
public String getPCode() { return this.pCode; }
public void setPCode(String newPCode) { this.pCode = newPCode; }
public int getQty() { return this.qty; }
pulbic void setQty (int newQty) { this.qty = newQty; }
public int getAmt() { return this.amt; }
pulbic void setAmt (int newAmt) { this.amt = newAmt; }
}
// now you can add that to an arrayList
SQL statement = "Select pCode, SUM(qty) as qty_sum, SUM(amt) as amt_sum from table group by pCode order by pCode "
java.util.List products = new java.util.ArrayList();
while (rs.next()) {
Product currentProduct = new Product(rs.getString("pcod
currentProduct.setQty(rs.g
currentProduct.setAmt(rs.g
products.add(currentProduc
}
Now you can iterate over products
java.util.Iterator prodIter = products.iterator();
while (prodIter.hasNext()) {
Product currentProduct = (Product) prodIter.next();
out.print(currentProduct.g
out.print(currentProduct.g
out.print(currentProduct.g
}
HTH,
CJ
SQL statement = "Select pCode, InvNum, SUM(qty) as Qty, SUM(InvAmt) as Amt from DB group by pCode, InvNum order by pCode, InvNum"
int qtyTotal = 0, qtySubTotal = 0;
double amtTotal = 0, amtSubTotal = 0;
String lastPCode = "";
while( rs.next() ) {
String pCode = rs.getString(1);
String invNum = rs.getString(2);
int qty = rs.getInt(3);
double amt = rs.getDouble(4);
if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
qtySubTotal = 0; amtSubTotal = 0;
lastPCode = pCode
}
System.out.println( pCode + " " + invNum + " " + qty + " " + amt );
qtySubTotal += qty; amtSubTotal += amt;
}
System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
System.out.println( "Grand Total " + qtyTotal + " " + amtTotal );
int qtyTotal = 0, qtySubTotal = 0;
double amtTotal = 0, amtSubTotal = 0;
String lastPCode = "";
while( rs.next() ) {
String pCode = rs.getString(1);
String invNum = rs.getString(2);
int qty = rs.getInt(3);
double amt = rs.getDouble(4);
if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
qtySubTotal = 0; amtSubTotal = 0;
lastPCode = pCode
}
System.out.println( pCode + " " + invNum + " " + qty + " " + amt );
qtySubTotal += qty; amtSubTotal += amt;
}
System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
System.out.println( "Grand Total " + qtyTotal + " " + amtTotal );
sorry, should be:
if( ! lastPCode.equals( pCode ) && qtySubTotal != 0 ) {
if( ! lastPCode.equals( pCode ) && qtySubTotal != 0 ) {
ASKER
Friend, how can I set my bigdecimal to zero ??
I mean the syntax, found out I can't do this.
sub_total = 0;
error;
unless i do this
sub_total= new BigDecimal("0.00");
is it the correct way to zero my sub_total?
I mean the syntax, found out I can't do this.
sub_total = 0;
error;
unless i do this
sub_total= new BigDecimal("0.00");
is it the correct way to zero my sub_total?
>> is it the correct way to zero my sub_total?
yes, bigdecimal is immutable, you must always create a new object.
yes, bigdecimal is immutable, you must always create a new object.
ASKER
kennethxu, I think your logic got little bit wrong....
I change
if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
qtySubTotal = 0; amtSubTotal = 0;
lastPCode = pCode
}
to
if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
lastPCode = pCode;
System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
qtySubTotal = 0; amtSubTotal = 0;
}
:-)
And... I am have way sovling another prblem. See either you are faster or me.
I want this,
PCode InvNum InvAmt sub-total
========================== ========== =
DDU DD8956 369 369
ABC BA1001 442
ABC PK8968 333 776
PKL LM0233 100
PKL LM0234 22
PKL LM0244 312 434
Grand Total : 1557
BUt your code give me this,
PCode InvNum InvAmt sub-total
========================== ========== =
DDU DD8956 369 369
ABC BA1001 442 442
ABC PK8968 333 776
PKL LM0233 100 100
PKL LM0234 22 122
PKL LM0244 312 412
Grand Total : 1557
And I am using JSP and JavaBean method... so quite some problem...
Planning to add in more code in the getSub_total method...
Is ithat the way??
I change
if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
qtySubTotal = 0; amtSubTotal = 0;
lastPCode = pCode
}
to
if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
lastPCode = pCode;
System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
qtySubTotal = 0; amtSubTotal = 0;
}
:-)
And... I am have way sovling another prblem. See either you are faster or me.
I want this,
PCode InvNum InvAmt sub-total
==========================
DDU DD8956 369 369
ABC BA1001 442
ABC PK8968 333 776
PKL LM0233 100
PKL LM0234 22
PKL LM0244 312 434
Grand Total : 1557
BUt your code give me this,
PCode InvNum InvAmt sub-total
==========================
DDU DD8956 369 369
ABC BA1001 442 442
ABC PK8968 333 776
PKL LM0233 100 100
PKL LM0234 22 122
PKL LM0244 312 412
Grand Total : 1557
And I am using JSP and JavaBean method... so quite some problem...
Planning to add in more code in the getSub_total method...
Is ithat the way??
>> kennethxu, I think your logic got little bit wrong....
>> if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
that's not right, i have corrected in my comment right after the code, it should be:
if( ! lastPCode.equals( pCode ) && qtySubTotal != 0 ) {
my code is just to illustrate the idea, I have not time to design the beans and etc. you of cause can abstract code into beans.
>> if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
that's not right, i have corrected in my comment right after the code, it should be:
if( ! lastPCode.equals( pCode ) && qtySubTotal != 0 ) {
my code is just to illustrate the idea, I have not time to design the beans and etc. you of cause can abstract code into beans.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
did u try my code?
CJ
CJ
ASKER
CJ,
I am so sorry....
I don't understand this fews things, but I knw it's good for me to learn. Arraylist and java.util.Iterator prodIter.
BUt I understand your logic and ideas.
:-)
I am so sorry....
I don't understand this fews things, but I knw it's good for me to learn. Arraylist and java.util.Iterator prodIter.
BUt I understand your logic and ideas.
:-)
ASKER
TQ kennethux
my pleasure
No need to apologize, if you understand the logic.. that is good. the APIs come with time :-)
CJ
CJ
ASKER
CJ, go out sid to grab your pooints..
TQ.
TQ.
select sum(Amt) from DB group by Code;
-- group by will order for you also