Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

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....
0
wjh7554
Asked:
wjh7554
  • 7
  • 6
  • 3
  • +1
1 Solution
 
allahabadCommented:
-- You can directly get this from table.
select sum(Amt) from DB group by Code;
-- group by will order for you also

 
0
 
allahabadCommented:
-- You can directly get this from table.
select sum(Amt) from DB group by Code;
-- group by will order for you also

 
0
 
wjh7554Author Commented:
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) ) ??


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
wjh7554Author Commented:
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??
0
 
cheekycjCommented:
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("pcode"));
  currentProduct.setQty(rs.getInt("qty_sum");
  currentProduct.setAmt(rs.getInt("amt_sum");
  products.add(currentProduct);
}


Now you can iterate over products

java.util.Iterator prodIter = products.iterator();
while (prodIter.hasNext()) {
  Product currentProduct = (Product) prodIter.next();
  out.print(currentProduct.getPCode());
  out.print(currentProduct.getQty());
  out.print(currentProduct.getAmt());
}

HTH,
CJ
0
 
kennethxuCommented:
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 );
0
 
kennethxuCommented:
sorry, should be:
if( ! lastPCode.equals( pCode ) && qtySubTotal != 0 ) {
0
 
wjh7554Author Commented:
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?
0
 
kennethxuCommented:
>> is it the correct way to zero my sub_total?
yes, bigdecimal is immutable, you must always create a new object.
0
 
wjh7554Author Commented:
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??


0
 
kennethxuCommented:
>> 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.
0
 
kennethxuCommented:
just be sure:

if( ! lastPCode.equals( pCode ) && qtySubTotal != 0 ) {
  System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
  qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
  qtySubTotal = 0; amtSubTotal = 0;
  lastPCode = pCode
}
0
 
cheekycjCommented:
did u try my code?

CJ
0
 
wjh7554Author Commented:
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.
:-)
0
 
wjh7554Author Commented:
TQ kennethux
0
 
kennethxuCommented:
my pleasure
0
 
cheekycjCommented:
No need to apologize, if you understand the logic.. that is good.  the APIs come with time :-)

CJ
0
 
wjh7554Author Commented:
CJ, go out sid to grab your pooints..

TQ.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now