RE: SQL Question

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

I'd try:
SELECT cusno,sum(ordtot)
  FROM oeordh a
  WHERE
    cusno = 304100 and
    EXISTS (SELECT * FROM Orhlact b
        WHERE a.ordno = b.ordno and
              a.ordlv = b.ordlv and
              rmvdate = 0)
  GROUP BY cusno


Eric DeLong
Sally Beauty Company
MIS-Sr. Programmer/Analyst
940-898-7863 or ext. 1863



-----Original Message-----
From: MWalter@hanoverwire.com [mailto:MWalter@hanoverwire.com]
Sent: Tuesday, October 23, 2001 12:08 PM
To: rpg400-l@midrange.com
Subject: SQL Question


Hello all,

I'm having a problem getting an SQL statement to work properly. Here is my
scenario:

I have two tables:

Order Header
Order Hold Detail.

The requirement is to get one row per customer showing the customer and a
total for all orders. Easy enough. Now here's the kicker. I need to check
the Order Hold Detail for Open Order Hold records (rmvdate = 0).
Here is the statement I'm using:

SELECT cusno,sum(ordtot) FROM oeordh a left outer join Orhlact b on
a.ordno = b.ordno and a.ordlv = b.ordlv and a.ordlv = b.ordlv WHERE
cusno = 304100 and rmvdate = 0 GROUP BY cusno

Customer Number 304100 has one open order totaling 45.00. However there are
5 open hold codes for this order. Hence, the sum(ordtot) is returning
225.00.

Any thoughts would be appreciated.


Thanks,

Mark


Mark Walter
Sr. Programmer/Analyst
Hanover Wire Cloth a div of CCX, Inc.
mwalter@hanoverwire.com
http://www.hanoverwire.com
717.637.3795 Ext.3040

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.