Hi everyone,

I want a little help to pull some records from the tables. i am working on Oracle 11g DB and i am using plsql allround automation tool

I want to provide with example so that it will be very easy to understand

let's say i got 5 tables.

Table 1

CREATE TABLE CONTRACT_CUSTOMERS

(contract  number(10) not null,

customer  number(10),

delivery_type VARCHAR2(10));

insert into CONTRACT_CUSTOMERS values (248, 1, 'BUY');

insert into CONTRACT_CUSTOMERS values (248, 2, 'ADMIN');

insert into CONTRACT_CUSTOMERS values (249, 3, 'BUY');

insert into CONTRACT_CUSTOMERS values (249, 4, 'ADMIN');

insert into CONTRACT_CUSTOMERS values (1065, 5, 'BUY');

insert into CONTRACT_CUSTOMERS values (1066, 6, 'BUY');

insert into CONTRACT_CUSTOMERS values (1068, 7, 'BUY');

insert into CONTRACT_CUSTOMERS values (1068, 8, 'ADMIN');

insert into CONTRACT_CUSTOMERS values (1068, 9, 'ADMIN');

insert into CONTRACT_CUSTOMERS values (1068, 10, 'ADMIN');

insert into CONTRACT_CUSTOMERS values (1072, 11, 'BUY');

insert into CONTRACT_CUSTOMERS values (1072, 12, 'ADMIN');

insert into CONTRACT_CUSTOMERS values (1529, 13, 'BUY');

insert into CONTRACT_CUSTOMERS values (1529, 14, 'ADMIN');

insert into CONTRACT_CUSTOMERS values (1529, 15, 'ADMIN');

select * from CONTRACT_CUSTOMERS;

Table2:

create table CUSTOMER_DETAIL

(customer  number(10),

type_code  varchar2(10),

last_modified date);

insert into CUSTOMER_DETAIL values (1,'EMAIL',(SYSDATE-1));

insert into CUSTOMER_DETAIL values (2,'EMAIL',SYSDATE);

--insert into CUSTOMER_DETAIL values (3,'EMAIL');

--insert into CUSTOMER_DETAIL values (4,'EMAIL');

--insert into CUSTOMER_DETAIL values (5,'EMAIL');

insert into CUSTOMER_DETAIL values (6,'EMAIL',SYSDATE);

  --insert into CUSTOMER_DETAIL values (7,'EMAIL');

insert into CUSTOMER_DETAIL values (8,'EMAIL',SYSDATE);

--insert into CUSTOMER_DETAIL values (9,'EMAIL');

  --insert into CUSTOMER_DETAIL values (10,'EMAIL');

insert into CUSTOMER_DETAIL values (11,'EMAIL',SYSDATE);

insert into CUSTOMER_DETAIL values (12,'EMAIL',(SYSDATE-1));

  --insert into CUSTOMER_DETAIL values (13,'EMAIL');

insert into CUSTOMER_DETAIL values (14,'EMAIL',(SYSDATE-1));

insert into CUSTOMER_DETAIL values (15,'EMAIL',(SYSDATE-2));

select * from CUSTOMER_DETAIL;

Table 3:

create table contract_schedule

(contract number(10),

fulfillment_type number(10));

insert into contract_schedule values (248, 1);

insert into contract_schedule values (248, 1);

insert into contract_schedule values (249, 1);

insert into contract_schedule values (249,1);

insert into contract_schedule values (1065, 1);

insert into contract_schedule values (1066, 1);

insert into contract_schedule values (1068, 1);

insert into contract_schedule values (1068, 1);

insert into contract_schedule values (1068, 1);

insert into contract_schedule values (1068, 1);

insert into contract_schedule values (1072, 1);

insert into contract_schedule values (1072, 1);

insert into contract_schedule values (1529, 110282);

insert into contract_schedule values (1529, 1);

insert into contract_schedule values (1529, 1);

select * from contract_schedule;

Table 4:

create table EMAIL_ADDRESS

(

  CUSTOMER          NUMBER(20) not null,

  EMAIL_ADDRESS   VARCHAR2(100) not null);

  --truncate table email_address

  --drop table email_address;

  insert into EMAIL_ADDRESS values (1,'abc@gmail.com');

insert into EMAIL_ADDRESS values (2,'cde@gmail.com');

--insert into EMAIL_ADDRESS values (3,'aaa@gmail.com');

--insert into EMAIL_ADDRESS values (4,'bbb@gmail.com');

--insert into EMAIL_ADDRESS values (5,'def@gmail.com');

--insert into EMAIL_ADDRESS values (6,'ghi@gmail.com');

  insert into EMAIL_ADDRESS values (7,'aaa@gmail.com');

insert into EMAIL_ADDRESS values (8,'bbb@gmail.com');

insert into EMAIL_ADDRESS values (9,'def@gmail.com');

  insert into EMAIL_ADDRESS values (10,'ghi@gmail.com');

--insert into EMAIL_ADDRESS values (11,'EMAIL');

--insert into EMAIL_ADDRESS values (12,'EMAIL'));

  --insert into EMAIL_ADDRESS values (13,'EMAIL');

insert into EMAIL_ADDRESS values (14,'jjj@gmail.com');

insert into EMAIL_ADDRESS values (15,'kkk@gmail.com');

select * from email_address;

Table 5 :

create table customers

(customer NUMBER(20) not null,

first_name           varchar2(50),

last_name            varchar2(50));

  insert into customers values (1,'sam','mark');

insert into customers values (2,'paul','marsh');

insert into customers values (3,'dan','daniel');

insert into customers values (4,'abc','def');

insert into customers values (5,'lynn','finn');

insert into customers values (6,'matt','demon');

  insert into customers values (7,'aaa','ccc');

insert into customers values (8,'crystal','lee');

insert into customers values (9,'katie','holme');

insert into customers values (10,'alpha','bravo');

insert into customers values (11,'ruth','stewart');

insert into customers values (12,'josie','vidalee');

insert into customers values (13,'cheyrl','corona');

insert into customers values (14,'sandra','swagner');

insert into customers values (15,'ryan','amy');

select * from customers;

Requirement:

Okay, let's us consider the table 1 (contract_customers)  is the main table, initially i used extract the contracts only that has a delivery type of 'BUY'. so in that case i used to get only one contract (Delivery_type= 'BUY') for each record. After that the business has mentioned that there may be a chance that the contract can have only one 'ADMIN'  record (Instead of BUY and ADMIN) so in my condition when i include the delivery_type in ('BUY','ADMIN') there is a possibility that i will be getting the duplicate contracts.

so in those cases i have one more business rule which says that " pull only those records where the customer_id you have in the table 1 matches to the table 2(customer_details) customer_id which has the 'EMAIL' type_code", and i have been using TOP-N-QUERY model

so later on my query used to be like this

WITH    got_r_num    AS

(

    SELECT  t1.*  -- or list whatever columns you want

    ,       DENSE_RANK () OVER ( PARTITION BY  t1.contract

                                 ORDER BY      NVL2 (t2.customer, 'A', 'B')

                               )  AS r_num

    FROM             CONTRACT_CUSTOMERS  t1

    LEFT OUTER JOIN  CUSTOMER_DETAIL  t2  ON  t2.customer  = t1.customer

                                 AND t2.type_code = 'EMAIL'

)

SELECT    *    -- or list all columns except r_num

FROM      got_r_num

WHERE     r_num  = 1

ORDER BY  customer;

well since as we can see that we are getting multiple contracts (it's because of the different customer id's) so i used one more column from the table 2 (customer_detail) in my condition

so the query_final is looking like this

WITH got_r_num AS

(

    select t1.contract,t1.customer,t1.delivery_type

    --SELECT t1.* -- or list whatever columns you want

          , row_number () OVER ( PARTITION BY t1.contract ORDER BY NVL2 (

           t2.customer, 'A', 'B'), t2.last_modified DESC ) AS r_num

           FROM CONTRACT_CUSTOMERS t1

    LEFT OUTER JOIN CUSTOMER_DETAIL t2

      ON t2.customer = t1.customer

      AND t2.type_code = 'EMAIL'

)

SELECT * -- or list all columns except r_num

FROM got_r_num

WHERE r_num = 1

ORDER BY customer;

And now everything looks perfect and i am getting the best rows , i though it is done so i put my final query in cursor and i started some testing

TEST SCRIPT

declare

  ln_contracts_id           contract.contract_id%type;

  ln_customers_id           customer_details.customer_id%type;

  ls_customer_id            customer_details.customer_id%type;

  ln_customerdetails_id     customer_details.customer_id%type;

  ls_delivery_method        varchar2(1) := '1';

  ls_customer_name          VARCHAR2(40) := ' ';

  ls_customer_email_address internet_address.internet_address%TYPE;

  CURSOR c_cur IS

    WITH got_r_num AS

     (select t1.contract ln_contract_id,

             t1.customer ln_customer_id,

             t1.delivery_type,

             row_number() OVER(PARTITION BY t1.contract ORDER BY NVL2(t2.customer, 'A', 'B'), t2.last_modified DESC) AS r_num

        FROM CONTRACT_CUSTOMERS t1

        LEFT OUTER JOIN CUSTOMER_DETAIL t2

          ON t2.customer = t1.customer

         AND t2.type_code = 'EMAIL')

    SELECT * -- or list all columns except r_num

      FROM got_r_num

     WHERE r_num = 1

     ORDER BY ln_customer_id;

  c_rec c_cur%ROWTYPE;

BEGIN

  OPEN c_cur;

  LOOP

    FETCH c_cur

      INTO c_rec;

    EXIT WHEN c_cur%NOTFOUND;

    begin

      select CONTRACT_CUSTOMERS.contract

        into ln_contracts_id

        from CONTRACT_CUSTOMERS

       where CONTRACT_CUSTOMERS.contract = c_rec.ln_contract_id;

      dbms_output.put_line(' 1. contractid        ' || ln_contracts_id);

    exception

      when others then

        ln_contracts_id := '';

    end;

  -- get customer

  begin

      select CUSTOMER_DETAIL.customer

        into ln_customers_id

        from CUSTOMER_DETAIL

       where CUSTOMER_DETAIL.customer = c_rec.ln_customer_id;

    exception

      when others then

        ln_contracts_id := '';

    end;

    -- get customer_name                                                                           

    BEGIN

      SELECT decode(nvl(customers.first_name, ' '),

                    ' ',

                    nvl(customers.last_name, ' '),

                    customers.first_name || ' ' || customers.last_name)

        INTO ls_customer_name

        FROM customers

       WHERE customers.customer = c_rec.ln_customer_id;

    EXCEPTION

      WHEN OTHERS THEN

        ls_customer_name := ' ';

    END;

    dbms_output.put_line(' 2. customer name        ' || ls_customer_name);

    --EMAIL Address                                                                               

    ls_customer_email_address := '';

    BEGIN

      SELECT CUSTOMER,email_address

        INTO ls_customer_id,ls_customer_email_address

        FROM EMAIL_ADDRESS

       WHERE email_address.customer = c_rec.ln_customer_id;

    EXCEPTION

      WHEN OTHERS THEN

        ls_customer_email_address := ' ';

    END;

    -- since the internet_address field is varchar(500) trim it to 100 for the file

    ls_customer_email_address := substr(ls_customer_email_address, 1, 100);

    dbms_output.put_line(' 3. email address        ' ||

                         ls_customer_email_address);

    --get Delivery Method                                                                                                  

    BEGIN

      IF ls_customer_id = c_rec.ln_customer_id THEN

        ls_delivery_method := '4';

      ELSE

        ls_delivery_method := '1';

      END IF;

    EXCEPTION

      WHEN OTHERS THEN

        ls_delivery_method := '1';

    END;

    dbms_output.put_line(' 4. deliveryid        ' || ls_delivery_method);

  END LOOP;

  CLOSE c_cur;

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    ln_customerdetails_id := 1234;

END;

Okay.. I kept DBMS_OUTPUT on 4 places so when i ran my test script my output results are like this

1. contract id        248

2. customer name        paul marsh

3. email address        cde@gmail.com

4. deliveryid        4

1. contractid        249

2. customer name        dan daniel

3. email address        

4. deliveryid        1

1. contractid        1065

2. customer name        lynn finn

3. email address        

4. deliveryid        1

1. contractid        1066

2. customer name        matt demon

3. email address        

4. deliveryid        1

1. contractid        1068

2. customer name        crystal lee

3. email address        bbb@gmail.com

4. deliveryid        4

1. contractid        1072

2. customer name        ruth stewart

3. email address        

4. deliveryid        1

1. contractid        1529

2. customer name        sandra swagner

3. email address        jjj@gmail.com

4. deliveryid        4

PROBLEM:

so when we run that final_query which i mentioned in the cursor we got the result set and if you look at the result set

Example:

Contract : 248 it picked the customer 2 (Which is admin) so for that customer id in the table 5 (customers) we have a name and in the table 4 we have the email address

But

business wants only the email address needs to be changed not the customer name so

Final result should be like this for that contract:

1. contract id        248

2. customer name        sam mark

3. email address        cde@gmail.com

4. deliveryid        4

so in my result set after running the test script for the contracts 248,1068,1529 i need to get the BUY type_code info not the Admin. So how can i modify my cursor or do i need to create one more cursor?????

In my work i have 30 different fields (just like customer_name) that gets the output info based on the customer id.

Scenario 1:

If a contract has 'BUY' and 'ADMIN' and if only 'BUY' has 'EMAIL' preference then it should pick all the information related to that customer ('BUY').

scenario 2:

If a contract has 'BUY' and 'ADMIN' and only 'ADMIN' has EMAIL preference then in the email field get only the admin email address and customer name should be 'BUYER'

scenario 3:

If a contract has BUY and 2 ADMINS

     1. Pick one which has EMAIL preference

     2. If both ADMINS has EMAIL preference pick the one which is recent modified

I hope you understood my question.

@"Frank Kulash"

get the unique values@"Etbin"