Help Writing Query Please!

[shar]

Please help rewriting the following SQL query for MS Access.

Thank you.

SELECT
O.ONE_ID,
O.TWO_ID,
O.THREE_ID,
O.SEQ_NO,
O.STAT,
O.RES_ID,
MIN(O2.SEQ_NO) N_OP,
MAX(O3.SEQ_NO) P_OP

FROM OP O,OP O2,OP O3
WHERE O.TYPE='X'
AND O.ONE_ID='32558'
AND O.TWO_ID='1'
AND O.TYPE=O2.WK_TYPE(+)
AND O.ONE_ID=O2.ONE_ID(+)
AND O.TWO_ID=O2.TWO_ID(+)
AND O.THREE_ID=O2.THREE_ID(+)
AND O.FOUR_ID=O2.FOUR_ID(+)
AND O.SEQ_NO<O2.SEQ_NO(+)
AND O.TYPE=O3.TYPE(+)
AND O.ONE_ID=O3.ONE_ID(+)
AND O.TWO_ID=O3.TWO_ID(+)
AND O.THREE_ID=O3.THREE_ID(+)
AND O.FOUR_ID=O3.FOUR_ID(+)
AND O.SEQ_NO>O3.SEQ_NO(+)
GROUP BY O.ONE_ID, O.TWO_ID, O.FOUR_ID, O.SEQ_NO, O.STAT, O.RES_ID
ORDER BY O.ONE_ID, TO_NUMBER(O.TWO_ID), O.FOUR_ID, O.SEQ_NO;

OUTPUT LOOKS LIKE:
ONE_ID TWO_ID THREE_ID SEQ_NO STAT RES_ID N_OP P_OP

32558 1 0 10 C S38 20
32558 1 0 20 C GRD 30 10
32558 1 0 30 C P01 50 20
32558 1 0 50 C M01 70 30
32558 1 0 70 C M01 80 50
32558 1 0 80 C E01 90 70
32558 1 0 90 C HT6 100 80
32558 1 0 100 C HT64 105 90
32558 1 0 105 C OST 110 100
32558 1 0 110 C INS 120 105
32558 1 0 120 C OSM 130 110
32558 1 0 130 C INS 140 120
32558 1 0 140 C INS 150 130
32558 1 0 150 C INS 160 140
32558 1 0 160 C INS 150

DATA TABLE LOOKS LIKE:
32558 1 0 10 C S38
32558 1 0 20 C GRD
32558 1 0 30 C P01
32558 1 0 50 C M01
32558 1 0 70 C M01
32558 1 0 80 C E01
32558 1 0 90 C HT6
32558 1 0 100 C HT64
32558 1 0 105 C OST
32558 1 0 110 C INS
32558 1 0 120 C OSM
32558 1 0 130 C INS
32558 1 0 140 C INS
32558 1 0 150 C INS
32558 1 0 160 C INS

 

[Dale Fye]

I don't see any reference to TYPE, WK_TYPE, FOUR_ID in the raw data shown
below your desired output. What do these fields look like?

I'm assuming these are Oracle queries but I'm not familiar with the (+)
notation following some of the comparisons in the WHERE clause, what does
this notation imply?

Dale

 

[shar]

TYPE holds X, R or F; I'm only interested in X
FOUR_ID is usually 0
WK_TYPE is an alias for TYPE
yes, it's Oracle

The data is unique based on TYPE, ONE_ID, TWO_ID, THREE_ID, FOUR_ID, SEQ_NO
combined, there is no row_id identifier.

Example of two reords in the table:
TYPE ONE_ID TWO_ID THREE_ID FOUR_ID SEQ_NO
X 32558 1 0 0 10
X 32558 1 0 0 20

O, O1 AND O2 are the same table like an inline view.

(+) is a left outer join.

I hope this makes sense. I'm very weak with joins in Access.
Thank you,

 

[Dale Fye]

Shar,

As I understand it, what you want to do is group on X, One_ID, Two_ID, Three_ID, Four_ID, and get the minimum sequence number greater than that records seq number and the maximum sequence number that is less than that records seq number. Did you really mean to leave Four_ID out of the SELECT clause and Three_ID out of the GROUP BY clause? Actually, the data at the bottom of this post is confusing, it doesn't have the Type, and it appears to be missing the Four_ID value as well. I'm going to assume that you don't really have a Four_ID value.

You will have to write this in the SQL view, since the Query grid will not display unequal joins (T1.Seq_No < T2.Seq_No). Although you could change this to and equality in both cases and then view it in the query grid. Then when you have all the right fields and stuff, switch to the SQL view and change those two joins as appropriate. I'm using Access 2000, and it is demanding about putting parenthesis around the joins when you have multiple joins. I've heard that 2003 will interpret it without the ( ) around the joins, but don't quote me on that.

HTH

Try:

Select T1.One_ID, T1.Two_ID, T1.Three_ID, T1.Seq_No, T1.Stat, T1.Res_ID, Min(T2.Seq_No) as N_OP, Max(T3.Seq_No) as P_OP
FROM (OP T1
LEFT JOIN OP T2
ON T1.TYPE = T2.TYPE
AND T1.One_ID = T2.One_ID
AND T1.Two_ID = T2.Two_ID
AND T1.Three_ID = T2.Three_ID
AND T1.Seq_NO < T2.Seq_No)
LEFT JOIN OP T3
ON T1.TYPE = T2.TYPE
AND T1.One_ID = T3.One_ID
AND T1.Two_ID = T3.Two_ID
AND T1.Three_ID = T3.Three_ID
AND T1.Seq_NO > T3.Seq_No
WHERE T1.TYPE = "X"
AND T1.ONE_ID = "32558"
AND T1.TWO_ID = "1"
GROUP BY T1.One_ID, T1.Two_ID, T1.Three_ID, T1.Seq_No, T1.Stat, T1.Res_ID
ORDER BY T1.One_ID, VAL(T1.Two_ID), T1.Three_ID, T1.Seq_No