query help

From: <samuels_at_blue.seas.upenn.edu>
Date: Mon, 22 Mar 2004 19:12:04 +0000 (UTC)
Message-ID: <c3ndq4$629t$1@netnews.upenn.edu>

okay, i have a problem and would like some help.

table:
TESTING_TABLE

COST_CODE               NUMBER,
REGION                  VARCHAR2(5),
ACCOUNT_NUMBER          NUMBER,
CUSTOMER_ID             VARCHAR2(10)
RELATIONSHIP_CODE       VARCHAR2(4)


for each cost_code , region , and account_number combination, there can be many customer_id/relationship_code combinations:
COST_CODE REGION  ACCOUNT_NUMBER                 CUSTOMER_ID                    RELATIONSHIP_CODE
--------- ------- ------------------------------ ------------------------------ -----------------
12345     PAW     1234567890123456               0100046556ABCD                 TEW
12345     PAW     1234567890123456               0101194150ABCD                 TEW
12345     PAW     1234567890123456               0102191700ABCD                 RET
12345     PAW     1234567890123456               0102191701ABCD                 BUI
12345     PAW     1234567890123456               0102191702ABCD                 BUI
12345     PAW     1234567890123456               0102195380ABCD                 TEW
12345     PAW     1234567890123456               0102195448ABCD                 TRE
12345     PAW     1234567890123456               0102412870ABCD                 COS
12345     PAW     1234567890123456               0102413067ABCD                 LPO
12345     PAW     1234567890123456               0102413566ABCD                 COS

what i need to do is be able to get the minimum customer_id for similar cost_code,region,account_number combinations based on the relationship_code, b
ut the relationship code will determine which customer_id i get the minimum for. for example, if there is a mix of relationship_codes and the following relationship_codes are present (BUI,COD,LPO,TRE) then i want the minimum custom
er_id , for those relationship_codes (for data set #1, that would be 0102191701ABCD). if there is a mix of relationship_codes and any of them = PAR (but not BUI, COD,LPO, and TRE), then i want the minimum customer_id. if there is a mix of relationship_codes and none of them = PAR,BUI,COD,LPO,and TRE, then i want the minimum customer_id. currently what i am doing is making multiple passes through the table: 1st pass -> show me the minimum customer_id for (cost_code,region,account_number) where relationship_code in ('BUI','COD','LPO','TRE') 2nd pass -> show me the minimum customer_id for (cost_code,region,account_number) where relationship_code = 'PAR' and relationship_code not in ('BUI',
'COD','LPO','TRE')
3rd pass --> show me the minimum customer_id fro (cost_code,region,account_number) where relationship_code not in ('BUI','COD','LPO','TRE','PAR')

basically, i take this customer_id, store it to a variable and use it to update another table whose primary key is (cost_code,region,account_number).

i know this is very inefficient but i'm not sure what else to do. i'm hoping there is a way to make one pass through the table and get the data i want.
if anyone has any ideas, i'd be highly appreciative. thanks in advance
-maurice
samuels_at_seas.upenn.edu Received on Mon Mar 22 2004 - 13:12:04 CST