Re: SQL: How to display one record from a file without affecting the others

"Which finished Item do you want to chose? They both appear 3 times."
I always need to have only one item.


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 Ä 7 727-815-3120 Ä þ WWW.PALL.COM

?Imagination is more important than knowledge...?
Albert Einstein (1879 - 1955)



Charles Wilt <charles.wilt@xxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
04/20/2010 10:20 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
Re: SQL: How to display one record from a file without affecting the
others






Here's a first pass....
with useCount as
(select CompItem, finishItem, count(*) as NbrOfUses
from mytable group by CompItem, finishItem), maxUse as (select CompItem,
max(nbrOfUses) as maxUse from useCount group by CompItem)

select CompItem, finishItem, sum(qty) as TotQty
from myTable A
join useCount B on a.compItem = b.compItem and a.finishItem =
b.finishItem
join maxUse C on b.compItem = c.compItem and b.nbrOfUses =
c.maxUse


The problem with the above....if for instance you have:

Component Warehouse Finished
Item Id Quantity Item
ABC123 N1 10 7777
ABC123 N1 15 7777
ABC123 N1 18 7777
ABC123 N1 25 7777A
ABC123 N1 7 7777A
ABC123 N1 13 7777A

Which finished Item do you want to chose? They both appear 3 times.

There are a few ways to get SQL to choose one finished item...
MIN(), MAX() or if at v5r4 and higher ROW_NUMBER()

row_number() over (partition by
CompItem order by Compitem) as seqNbr

HTH,
Charles

On Mon, Apr 19, 2010 at 4:19 PM, <Peter_Vidal@xxxxxxxx> wrote:

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.