Re: query help

From: VC <boston103_at_hotmail.com>
Date: Tue, 23 Mar 2004 12:15:05 GMT
Message-ID: <dTV7c.71850$po.625119@attbi_s52>

Hello,

<samuels_at_blue.seas.upenn.edu> wrote in message news:c3ndq4$629t$1_at_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
>

If I understand your task correctly, then this will produce the hree minimums:

select cost_code, region, account_number,   min(case when relationship_code in ('BUI','COD','LPO','TRE') then customer_id end) min1,
  min(case when relationship_code not in ('BUI','COD','LPO','TRE') and relationship_code='PAR' then customer_id end) min2,   min(case when relationship_code not in ('BUI','COD','LPO','TRE','PAR') then customer_id end) min3
from testing_table
group by cost_code, region, account_number

VC Received on Tue Mar 23 2004 - 06:15:05 CST