Solved

# Pseudocode Enquiry: pls correct.

Posted on 2003-02-18
Medium Priority
304 Views
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) {
} else {
..
...

????????

Actually I don;t know how to do....
0
Question by:wjh7554
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 6
• 3
• +1

LVL 3

Expert Comment

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

0

LVL 3

Expert Comment

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

0

Author Comment

ID: 7980110
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

Author Comment

ID: 7980455

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

LVL 19

Expert Comment

ID: 7981929
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");
}

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

LVL 14

Expert Comment

ID: 7982262
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

LVL 14

Expert Comment

ID: 7982266
sorry, should be:
if( ! lastPCode.equals( pCode ) && qtySubTotal != 0 ) {
0

Author Comment

ID: 7985018
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

LVL 14

Expert Comment

ID: 7985372
>> is it the correct way to zero my sub_total?
yes, bigdecimal is immutable, you must always create a new object.
0

Author Comment

ID: 7985475
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

LVL 14

Expert Comment

ID: 7987522
>> 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

LVL 14

Accepted Solution

kennethxu earned 200 total points
ID: 7987528
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

LVL 19

Expert Comment

ID: 7988522
did u try my code?

CJ
0

Author Comment

ID: 7990370
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

Author Comment

ID: 7990401
TQ kennethux
0

LVL 14

Expert Comment

ID: 7990935
my pleasure
0

LVL 19

Expert Comment

ID: 7996564
No need to apologize, if you understand the logic.. that is good.  the APIs come with time :-)

CJ
0

Author Comment

ID: 8005299
CJ, go out sid to grab your pooints..

TQ.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An introduction to the wonderful sport of Scam Baiting.  Learn how to help fight scammers by beating them at their own game. This great pass time helps the world, while providing an endless source of entertainment. Enjoy!
In this modest contribution, I want to share with the IT community (especially system administrators, IT Support Engineers and IT Help Desks) about Windows crashes/hangs and how to deal with these particular problems.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized â€¦
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediafâ€¦
###### Suggested Courses
Course of the Month9 days, 23 hours left to enroll