I think something like this might work for you
WITH REQ as (SELECT CINBR, PINBR, QTYPR,
rank() over (partition by cinbr order
by qtypr desc) qtyrank
FROM pstruc
)
select * from REQ where qtyrank = 1
This is using the product structure file to display the component and parent combination with the highest quantity per of a component. You could substitute qtypr for a SUM(QUANTITY) and add a group by clause in the Common table expression to get what you want.
-Tom Stieger
Engineer
California Fine Wire
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Peter_Vidal@xxxxxxxx
Sent: Monday, April 19, 2010 1:19 PM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL: How to display one record from a file without affecting the others
Hi list!
Another SQL challenge (at least for me): I have an SQL SELECT Statement with 3 files. I need to add a 4th one (REQMTS); however, in this file I may have more than one record that will match the selection criteria. I just want to have one record and not all of the records that match the selection criteria.
For example, I have a component item called ABC123 and it may be used in more than one finished item, let's day 7777 and 7777A. It will look something like this:
Component Warehouse Finished
Item Id Quantity Item
ABC123 N1 10 7777
ABC123 N1 15 7777
ABC123 N1 25 7777A
ABC123 N1 7 7777A
ABC123 N1 13 7777A
What I want is (by using the component item and the warehouse id) to pull in the FINISHED ITEM that is most often associated with a component item (in this case, 7777A) and the total QUANTITY for that FINISHED ITEM (in
this case, 45 (25 + 7 + 13)). In addition, I want to include the file
and this new logic in this SQL Statement (that is currently running):
SELECT I.HOUSE, I.ITNBR, I.PLANIB,
R.ITCLS, R.ITDSC, R.ENGNO, R.ITTYP,
P.PODUD, P.POSTD, P.POQTY, P.PORID,
I.ACREC
FROM
ITEMBL I, ITMRVA R, PLNORD P
WHERE
I.ITNBR = P.POITM AND
I.HOUSE = P.POPLWH AND
I.ITNBR = R.ITNBR AND
I.ACREC = 'S' AND
R.STID = 'N1P' AND
(I.HOUSE LIKE 'N%')
Any ideas? Any help is greatly appreciated.
PETER VIDAL
PALL CORPORATION | SR SYSTEM ANALYST @ WH APPLICATION DEVELOPMENT GROUP 10540 RIDGE RD., SUITE 203, NEW PORT RICHEY, FL 34654-5111
727-815-3104 ||| FAX: 727-815-3120 ||| WWW.PALL.COM
"Imagination is more important than knowledge..."
Albert Einstein (1879 - 1955)
--------------------------------------------------------------------------------
Attention:
This communication may contain information that is confidential, privileged and/or exempt from disclosure under applicable law.
If you are not the intended recipient, please notify the sender immediately and delete the original, all attachments, and all copies of this communication.
--------------------------------------------------------------------------------
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
