filtering data

[sonny1974]

oracle 9i, crystal xi

i have these two rcvr with 3 records a piece, one has a rank of 1,2,3 and other has a rank of 1,3,3 . tran code '11' means qty checked in. 23 means reject, 28 is approved. 00189 had 2 qty and 1 passed and 1 failed, i want to keep that, since the time is the same the rank were both 3,3. but on rcvr 00188 , the rank is 1,2,3 and qty is 4,4,4 , well if my qty of rank 1 matches my qty of rank 2, i dont want to see rank 3. i want to see rank 1, 2, since both are 4 and 4. i want on the next set, i want to see all 3 because qty goes 2,1,1. is there a where statement i can put to fix this?

QTY RCVR TRAN_CODE DATE_TIME1 DATE_TIME2 RANK

4 188 11 12/29/2005 12:49 12/29/2005 12:49 1
4 188 23 2/21/2006 16:41 12/29/2005 12:49 2
4 188 28 3/6/2006 18:11 12/29/2005 12:49 3

2 189 11 12/29/2005 13:29 12/29/2005 13:29 1
1 189 23 2/28/2006 12:59 12/29/2005 13:29 3
1 189 28 2/28/2006 12:59 12/29/2005 13:29 3

 

[SantaMufasa]

is there a where statement i can put to fix this?

I'm sure there is. Can you post the code, especially the WHERE clause, that you have tried already that produced unsatisfactory results?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sonny1974]

i havent put it in my where statement since im not sure how to do it



SELECT A.PART,a.QTY, a.RCVR, a.TRAN_CODE,rcv.DATE_RCV,a.date_ref,rcv.rcvr,rcv.dt_stamp,
TO_DATE(to_char(a.date_add,'MMDDYYYY')||TO_CHAR(FLOOR(a.TIME_ADD/100),'FM000000'),'MMDDYYYYHH24MISS') as date_time1
,min (TO_DATE(to_char(b.date_add,'MMDDYYYY')||TO_CHAR(FLOOR(b.TIME_ADD/100),'FM000000'),'MMDDYYYYHH24MISS') ) as date_time2

, COUNT(*) Rank
FROM int_table a INNER JOIN int_table B ON A.RCVR=B.RCVR and

tO_DATE(to_char(a.date_add,'MMDDYYYY')||TO_CHAR(FLOOR(a.TIME_ADD/100),'FM000000'),'MMDDYYYYHH24MISS') >=
tO_DATE(to_char(b.date_add,'MMDDYYYY')||TO_CHAR(FLOOR(b.TIME_ADD/100),'FM000000'),'MMDDYYYYHH24MISS')
LEFT JOIN RCV_TABLE RCV ON A.RCVR = RCV.RCVR AND A.LOT_NBR = RCV.LOT_NBR
--left join rcv_table c on a.part = c.part and a.rcvr=c.rcvr
where
to_number (b.tran_code) in (11,28,23) and to_number (a.tran_code) in (11,28,23)
GROUP BY A.PART, a.QTY, a.RCVR, a.TRAN_CODE,rcv.DATE_RCV,a.date_ref,rcv.rcvr,rcv.dt_stamp,
TO_DATE(to_char(a.date_add,'MMDDYYYY')||TO_CHAR(FLOOR(a.TIME_ADD/100),'FM000000'),'MMDDYYYYHH24MISS')

 

[sonny1974]

also , if rank 1 is larger then rank 2, i want to see rank 3. if rank 1 and rank 2 are equal ( rank 2 should never be larger then rank 1, either smaller or same), i dont want to see rank 3

 

[SantaMufasa]

Sonny,

After re-reading your specifications (which require conditional processing based upon other rows that you are not currently processing), then I would opt for PL/SQL instead of SQL to produce the results that you want. (Any SQL-only method of producing the results that you want, I anticipate, would be very complex, and would cause entanglement in one's knickers.)

Are you familiar with how to code PL/SQL scripts?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sonny1974]

no i dont sir, can i do a formula in crytal?

 

[sonny1974]

any ideas? the ranking is based on date_time1. can i do a where on the date_time1 to get the data i need?

 

[sonny1974]

so you dont believe i can do this in my where statement?

 

[SantaMufasa]

So, Sonny, for the delay in getting back to you (...I had to go out and throw about 17.5 cm (7 inches) of snow that fell here in about 2.5 hours this morning.)

Yes, you can do it in a WHERE statement, but anytime you need to query rows based upon information in other rows in the same table, it requires techniques such as:

1) (In-line) views
2) Subqueries
3) Self-joins
4) et cetera

So, certainly you can do what you want with a SQL-only query, but it is typically much more complex than a standard query.

If I wasn't both blowing snow and trying to get out of town for my youngest daughter's wedding, I'd be trying to help you more. (Sorry.)


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sonny1974]

no problem at all
do you know a way i can do it in crystal xi , and not on the sql side

 

[SantaMufasa]

Sorry, the only thing I know about Crystal is that the best is made in Ireland.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sonny1974]

any suggestions?