Re: Joining Fields

Ok, the only change I made was separate the values (, instead of ||). For the rest of it I did this:

CREATE OR REPLACE
PROCEDURE CountPP AS
BEGIN
   FOR rec IN (SELECT code1 x,code2 y FROM pdTable) LOOP

     UPDATE a set master_count =
        (SELECT COUNT(DISTINCT a.customer_id) master_count
         FROM a a1,a a2
         WHERE a1.customer_id = a2.customer_id
             AND a1.product_id = emp_rec.x
             AND a2.product_id = emp_rec.y
        GROUP BY a1.product_id)
     WHERE pdTable.code1 = emp_rec.x AND pdTable.code2 = emp_rec.y;

I'm still wondering if this is the best solution though.

Thanks!

• Scott Brian Tkatch wrote:

> On Wed, 29 Nov 2000 16:25:57 GMT, sw_at_weinerfamily.org wrote:
>
> >This may be interesting:
> >
> >I have a table that looks like this
> >
> >Table A
> >TYPE CODE
> >--------------
> >P 1
> >P 2
> >P 3
> >D 1
> >D 2
> >
> >I have another table that looks like this:
> >
> >Table B
> >ID PCODE DCODE MASTER
> >----------------------------
> >1 1 1 AAA
> >2 1 1 XXX
> >3 1 5 AAA
> >4 2 1 XXX
> >5 3 2 AAA
> >6 1 1 AAA
> >
> >What I need to do is iterate through Table A and create combinations
> >such as:
> >P1P2
> >P1P3
> >P1D1
> >P1D2
> >P2P1
> >P2P3
> >...
>
> Just quickly on this first part, to create the combined codes, just
> join the table to itself with no real join information.
>
> SELECT
> a1.Type || a1.Code || a2.Type || a2.Code
> FROM
> a a1, a a2,
> WHERE NOT a1.rowid = a2.rowid;
>
> >
> >Then I need to count how many unique MASTERS in Table B have each
> >particular combination
> >
> >So the result might be:
> >
> >CODE MASTER_COUNT
> >-------------------------
> >P1P2 0
> >P1P3 2
> >P1D1 2
> >P1D2 0
> >P2P1 1
> >P2P3 0
> >...
> >
> >
> >I realize this is probably multiple queries but what is the best way to
> >do this?
> >
> >Thank you!!!!
> >
> >
Received on Wed Nov 29 2000 - 19:32:52 CET