Hi,

The only way I can think of to do this is to create a cursor and
somehow loop through the rows, comparing each column, and sum the
totals. Does anyone know a more straightforward, easier way to
accomplish?

I have a table where I have compare each column to see if something has
changed for the same part. If so, the changes must be counted before
or after a certain level - thereby, splitting the counts into 2
categories. I am using Oracle 9.2.0.3.0 on AIX platform.

The data is as follows. I need to determine how many iterations the
part has gone through. A change made on any layer below 8 goes into
category cnt 1, above 8 is catg2 cnt.

Part# Lvl# Layer Rev
----- ---- ----- ---
7239 9032 1 A 1st entry of part
7239 9032 2 A
7239 9032 3 A
7239 9032 3.9 A
7239 9032 4 A
7239 9032 4.5 A
7239 9032 5 A
7239 9032 6 A
7239 9032 6.05 A
7239 9032 7.1 A
7239 9032 8 A
7239 9032 9 A
7239 9032 10 A
7239 9032 11 A
7239 9032 12 A
7239 9032 13 A
7239 9032 15 A
7239 9032 30 A

7239 9199 1 A compare to previous row for
7239 9199 2 A same part, so this is rev 1
7239 9199 3 A
7239 9199 3.9 A
7239 9199 4 A
7239 9199 4.5 A
7239 9199 5 A
7239 9199 6 A
7239 9199 6.05 A
7239 9199 7.1 B Change occurred below layer 8
7239 9199 8 B Catg1 cnt = 1; catg2 cnt=0;
7239 9199 9 B
7239 9199 10 B
7239 9199 11 B
7239 9199 12 B
7239 9199 13 A
7239 9199 15 B
7239 9199 30 A

7239 9517 1 A
7239 9517 2 A
7239 9517 3 A
7239 9517 3.9 A
7239 9517 4 A
7239 9517 4.5 A
7239 9517 5 A
7239 9517 6 A
7239 9517 6.05 A
7239 9517 7.1 B
7239 9517 8 C Change occurred above 8+ layer
7239 9517 9 C catg1 cnt=1; catg2 cnt=1;
7239 9517 10 C
7239 9517 11 A
7239 9517 12 C
7239 9517 13 A
7239 9517 15 A
7239 9517 30 B

7239 10008 1 A
7239 10008 2 A
7239 10008 3 A
7239 10008 3.9 A
7239 10008 4 A
7239 10008 4.5 A
7239 10008 5 A
7239 10008 6 A
7239 10008 6.05 A
7239 10008 7.1 B
7239 10008 8 C Change occurred above 8+ layer
7239 10008 9 C Catg1 cnt = 1; catg2 cnt=2;
7239 10008 10 D
7239 10008 11 B
7239 10008 12 C
7239 10008 13 A
7239 10008 15 B
7239 10008 30 B

google "Comparing the Contents of Two Tables" at oracle.com

Additionally, don't just print the data with questions like this...
Take the time to write out:

CREATE TABLE A ...
CREATE TABLE B ...
INSERT INTO TABLE A...
INSERT INTO TABLE A...
INSERT INTO TABLE B...
INSERT INTO TABLE B...

that way we can just copy, pastes, and run your script, and send you a
query that works.

Ok, sorry about that. Below is the query to create the table and data.
I have to count the number of iterations of a product per category
(above and below layer 8).

CREATE TABLE t1 (prod_num NUMBER, device_num NUMBER, layer_name
VARCHAR2(5), rev VARCHAR2(1));
INSERT INTO t1 VALUES(7239, 9032, '1' , 'A');
INSERT INTO t1 VALUES(7239, 9032, '2' , 'A');
INSERT INTO t1 VALUES(7239, 9032, '3' , 'A');
INSERT INTO t1 VALUES(7239, 9032, '3.9', 'A');
INSERT INTO t1 VALUES(7239, 9032, '4' , 'A');
INSERT INTO t1 VALUES(7239, 9032, '4.5', 'A');
INSERT INTO t1 VALUES(7239, 9032, '5' , 'A');
INSERT INTO t1 VALUES(7239, 9032, '6' , 'A');
INSERT INTO t1 VALUES(7239, 9032, '6.05', 'A');
INSERT INTO t1 VALUES(7239, 9032, '7.1', 'A');
INSERT INTO t1 VALUES(7239, 9032, '8' , 'A');
INSERT INTO t1 VALUES(7239, 9032, '9' , 'A');
INSERT INTO t1 VALUES(7239, 9032, '10' , 'A');
INSERT INTO t1 VALUES(7239, 9032, '11' , 'A');
INSERT INTO t1 VALUES(7239, 9032, '12' , 'A');
INSERT INTO t1 VALUES(7239, 9032, '13' , 'A');
INSERT INTO t1 VALUES(7239, 9032, '15' , 'A');
INSERT INTO t1 VALUES(7239, 9032, '30' , 'A');
INSERT INTO t1 VALUES(7239, 9199, '1' , 'A');
INSERT INTO t1 VALUES(7239, 9199, '2' , 'A');
INSERT INTO t1 VALUES(7239, 9199, '3' , 'A');
INSERT INTO t1 VALUES(7239, 9199, '3.9', 'A');
INSERT INTO t1 VALUES(7239, 9199, '4' , 'A');
INSERT INTO t1 VALUES(7239, 9199, '4.5', 'A');
INSERT INTO t1 VALUES(7239, 9199, '5' , 'A');
INSERT INTO t1 VALUES(7239, 9199, '6' , 'A');
INSERT INTO t1 VALUES(7239, 9199, '6.05', 'A');
INSERT INTO t1 VALUES(7239, 9199, '7.1' , 'B');
INSERT INTO t1 VALUES(7239, 9199, '8' , 'B');
INSERT INTO t1 VALUES(7239, 9199, '9' , 'B');
INSERT INTO t1 VALUES(7239, 9199, '10' , 'B');
INSERT INTO t1 VALUES(7239, 9199, '11' , 'B');
INSERT INTO t1 VALUES(7239, 9199, '12' , 'B');
INSERT INTO t1 VALUES(7239, 9199, '13' , 'A');
INSERT INTO t1 VALUES(7239, 9199, '15' , 'B');
INSERT INTO t1 VALUES(7239, 9199, '30' , 'A');
INSERT INTO t1 VALUES(7239, 9517, '1' , 'A');
INSERT INTO t1 VALUES(7239, 9517, '2' , 'A');
INSERT INTO t1 VALUES(7239, 9517, '3' , 'A');
INSERT INTO t1 VALUES(7239, 9517, '3.9' , 'A');
INSERT INTO t1 VALUES(7239, 9517, '4' , 'A');
INSERT INTO t1 VALUES(7239, 9517, '4.5' , 'A');
INSERT INTO t1 VALUES(7239, 9517, '5' , 'A');
INSERT INTO t1 VALUES(7239, 9517, '6' , 'A');
INSERT INTO t1 VALUES(7239, 9517, '6.05', 'A');
INSERT INTO t1 VALUES(7239, 9517, '7.1' , 'B');
INSERT INTO t1 VALUES(7239, 9517, '8' , 'C');
INSERT INTO t1 VALUES(7239, 9517, '9' , 'C');
INSERT INTO t1 VALUES(7239, 9517, '10' , 'C');
INSERT INTO t1 VALUES(7239, 9517, '11' , 'A');
INSERT INTO t1 VALUES(7239, 9517, '12' , 'C');
INSERT INTO t1 VALUES(7239, 9517, '13' , 'A');
INSERT INTO t1 VALUES(7239, 9517, '15' , 'A');
INSERT INTO t1 VALUES(7239, 9517, '30' , 'B');
INSERT INTO t1 VALUES(7239, 10008, '1' , 'A');
INSERT INTO t1 VALUES(7239, 10008, '2' , 'A');
INSERT INTO t1 VALUES(7239, 10008, '3' , 'A');
INSERT INTO t1 VALUES(7239, 10008, '3.9' , 'A');
INSERT INTO t1 VALUES(7239, 10008, '4' , 'A');
INSERT INTO t1 VALUES(7239, 10008, '4.5' , 'A');
INSERT INTO t1 VALUES(7239, 10008, '5' , 'A');
INSERT INTO t1 VALUES(7239, 10008, '6' , 'A');
INSERT INTO t1 VALUES(7239, 10008, '6.05', 'A');
INSERT INTO t1 VALUES(7239, 10008, '7.1' , 'B');
INSERT INTO t1 VALUES(7239, 10008, '8' , 'C');
INSERT INTO t1 VALUES(7239, 10008, '9' , 'C');
INSERT INTO t1 VALUES(7239, 10008, '10' , 'D');
INSERT INTO t1 VALUES(7239, 10008, '11' , 'B');
INSERT INTO t1 VALUES(7239, 10008, '12' , 'C');
INSERT INTO t1 VALUES(7239, 10008, '13' , 'A');
INSERT INTO t1 VALUES(7239, 10008, '15' , 'B');
INSERT INTO t1 VALUES(7239, 10008, '30' , 'B');

I have read your posting several times and it's still unclear to me
what you want to achive.

What exactly is a change?

What's supposed to happen when Part# changes (your sample has only
7239).

Rene

On 2005-06-08, tammy_gutter@yahoo.com <tammy_gutter@yahoo.com> wrote:
> Hi,
>
> The only way I can think of to do this is to create a cursor and
> somehow loop through the rows, comparing each column, and sum the
> totals. Does anyone know a more straightforward, easier way to
> accomplish?
>
> I have a table where I have compare each column to see if something has
> changed for the same part. If so, the changes must be counted before
> or after a certain level - thereby, splitting the counts into 2
> categories. I am using Oracle 9.2.0.3.0 on AIX platform.
>
> The data is as follows. I need to determine how many iterations the
> part has gone through. A change made on any layer below 8 goes into
> category cnt 1, above 8 is catg2 cnt.
>
> Part# Lvl# Layer Rev
> ----- ---- ----- ---
> 7239 9032 1 A 1st entry of part
> 7239 9032 2 A
> 7239 9032 3 A
> 7239 9032 3.9 A
> 7239 9032 4 A
> 7239 9032 4.5 A
> 7239 9032 5 A
> 7239 9032 6 A
> 7239 9032 6.05 A
> 7239 9032 7.1 A
> 7239 9032 8 A
> 7239 9032 9 A
> 7239 9032 10 A
> 7239 9032 11 A
> 7239 9032 12 A
> 7239 9032 13 A
> 7239 9032 15 A
> 7239 9032 30 A
>
> 7239 9199 1 A compare to previous row for
> 7239 9199 2 A same part, so this is rev 1
> 7239 9199 3 A
> 7239 9199 3.9 A
> 7239 9199 4 A
> 7239 9199 4.5 A
> 7239 9199 5 A
> 7239 9199 6 A
> 7239 9199 6.05 A
> 7239 9199 7.1 B Change occurred below layer 8
> 7239 9199 8 B Catg1 cnt = 1; catg2 cnt=0;
> 7239 9199 9 B
> 7239 9199 10 B
> 7239 9199 11 B
> 7239 9199 12 B
> 7239 9199 13 A
> 7239 9199 15 B
> 7239 9199 30 A
>
> 7239 9517 1 A
> 7239 9517 2 A
> 7239 9517 3 A
> 7239 9517 3.9 A
> 7239 9517 4 A
> 7239 9517 4.5 A
> 7239 9517 5 A
> 7239 9517 6 A
> 7239 9517 6.05 A
> 7239 9517 7.1 B
> 7239 9517 8 C Change occurred above 8+ layer
> 7239 9517 9 C catg1 cnt=1; catg2 cnt=1;
> 7239 9517 10 C
> 7239 9517 11 A
> 7239 9517 12 C
> 7239 9517 13 A
> 7239 9517 15 A
> 7239 9517 30 B
>
> 7239 10008 1 A
> 7239 10008 2 A
> 7239 10008 3 A
> 7239 10008 3.9 A
> 7239 10008 4 A
> 7239 10008 4.5 A
> 7239 10008 5 A
> 7239 10008 6 A
> 7239 10008 6.05 A
> 7239 10008 7.1 B
> 7239 10008 8 C Change occurred above 8+ layer
> 7239 10008 9 C Catg1 cnt = 1; catg2 cnt=2;
> 7239 10008 10 D
> 7239 10008 11 B
> 7239 10008 12 C
> 7239 10008 13 A
> 7239 10008 15 B
> 7239 10008 30 B
>


--
Rene Nyffenegger
http://www.adp-gmbh.ch/

can you give an example of what you EXPECT the results to be based on
the sample data?

What I need to do is count the number of iterations for a particular
part-level combo. In addition, I need to count the iterations by layer
category - so if a change occurs below layer 8 anywhere, I should
increment cnt1; changes >= 8 increments cnt2.

So.. for the example above, there are 3 total iterations, 1 < 8 and 2
iterations >= 8. This is what I need to systematically determine. For
now, I am trying to transpose the rows horizontally so that I can
compare part-level1-layer1 to part-level2-layer1 (i.e., I need to
compare level part7239-lvl9032-layer1 to part7239-lvl9199-layer1, if
change occurred, increment cnt 1 and move to next lvl (comparing
7239-lvl9199-layer1 to 7239-lvl9517-layer1) and so on.

It is ver complicated for me to explain. I have over 5000 of these
records in my tables to compare. I didn't want to add too much data to
make it even more complicated. If I can determine some type of
algorithm to do what I've tried to explain so far, I can probably
figure out the rest (comparing the other parts).

Also, the layer numbers are not consistent across parts - this is why
they are varchar2. Some have layers like AB1.0, B2.0, etc. So it is
hard to systematically transpose the records horizontally for
comparison.

I hope I have not confused you more.

A change is a "revision change in any layer" of a part-level key. I
have to do the same for the next part. I have to determne if ANY layer
change occured between part7239-layer9032 and 7239-9199 and then any
change between 7239-9199 and 7239-9517, and then any change between
7239-9517 and 7239-10008 and so on. For this particular part, there
are 3 changes after the initial part was created.

Hope this helps to explain.

Rene Nyffenegger wrote:
> I have read your posting several times and it's still unclear to me
> what you want to achive.
>
> What exactly is a change?
>
> What's supposed to happen when Part# changes (your sample has only
> 7239).
>
> Rene
>
> On 2005-06-08, tammy_gutter@yahoo.com <tammy_gutter@yahoo.com> wrote:
> > Hi,
> >
> > The only way I can think of to do this is to create a cursor and
> > somehow loop through the rows, comparing each column, and sum the
> > totals. Does anyone know a more straightforward, easier way to
> > accomplish?
> >
> > I have a table where I have compare each column to see if something has
> > changed for the same part. If so, the changes must be counted before
> > or after a certain level - thereby, splitting the counts into 2
> > categories. I am using Oracle 9.2.0.3.0 on AIX platform.
> >
> > The data is as follows. I need to determine how many iterations the
> > part has gone through. A change made on any layer below 8 goes into
> > category cnt 1, above 8 is catg2 cnt.
> >
> > Part# Lvl# Layer Rev
> > ----- ---- ----- ---
> > 7239 9032 1 A 1st entry of part
> > 7239 9032 2 A
> > 7239 9032 3 A
> > 7239 9032 3.9 A
> > 7239 9032 4 A
> > 7239 9032 4.5 A
> > 7239 9032 5 A
> > 7239 9032 6 A
> > 7239 9032 6.05 A
> > 7239 9032 7.1 A
> > 7239 9032 8 A
> > 7239 9032 9 A
> > 7239 9032 10 A
> > 7239 9032 11 A
> > 7239 9032 12 A
> > 7239 9032 13 A
> > 7239 9032 15 A
> > 7239 9032 30 A
> >
> > 7239 9199 1 A compare to previous row for
> > 7239 9199 2 A same part, so this is rev 1
> > 7239 9199 3 A
> > 7239 9199 3.9 A
> > 7239 9199 4 A
> > 7239 9199 4.5 A
> > 7239 9199 5 A
> > 7239 9199 6 A
> > 7239 9199 6.05 A
> > 7239 9199 7.1 B Change occurred below layer 8
> > 7239 9199 8 B Catg1 cnt = 1; catg2 cnt=0;
> > 7239 9199 9 B
> > 7239 9199 10 B
> > 7239 9199 11 B
> > 7239 9199 12 B
> > 7239 9199 13 A
> > 7239 9199 15 B
> > 7239 9199 30 A
> >
> > 7239 9517 1 A
> > 7239 9517 2 A
> > 7239 9517 3 A
> > 7239 9517 3.9 A
> > 7239 9517 4 A
> > 7239 9517 4.5 A
> > 7239 9517 5 A
> > 7239 9517 6 A
> > 7239 9517 6.05 A
> > 7239 9517 7.1 B
> > 7239 9517 8 C Change occurred above 8+ layer
> > 7239 9517 9 C catg1 cnt=1; catg2 cnt=1;
> > 7239 9517 10 C
> > 7239 9517 11 A
> > 7239 9517 12 C
> > 7239 9517 13 A
> > 7239 9517 15 A
> > 7239 9517 30 B
> >
> > 7239 10008 1 A
> > 7239 10008 2 A
> > 7239 10008 3 A
> > 7239 10008 3.9 A
> > 7239 10008 4 A
> > 7239 10008 4.5 A
> > 7239 10008 5 A
> > 7239 10008 6 A
> > 7239 10008 6.05 A
> > 7239 10008 7.1 B
> > 7239 10008 8 C Change occurred above 8+ layer
> > 7239 10008 9 C Catg1 cnt = 1; catg2 cnt=2;
> > 7239 10008 10 D
> > 7239 10008 11 B
> > 7239 10008 12 C
> > 7239 10008 13 A
> > 7239 10008 15 B
> > 7239 10008 30 B
> >
>
>
> --
> Rene Nyffenegger
> http://www.adp-gmbh.ch/