This discussion is archived
9 Replies Latest reply: Feb 28, 2012 6:51 AM by 788917 RSS

Query help

788917 Newbie
Currently Being Moderated
Hi,

Could someone please help me with my report? This is the current query but it's giving duplicates and wrong data results.
select d.id,
         c.name.,
         c.payer_id,
         c.payer_accountnumber,
         decode(a.cid,NULL,payer.line1,a.address_line1) Payer_Address1,
         decode(a.cid,NULL,payer.line2,a.address_line2) Payer_Address2,
         c.line1 as customer_address1,
         c.line2 as customer_address2
from del_history d,
      customer c,
      payer_account a,
      customer payer
where d.id=c.id and payer.cid=a.cid
and c.payer_accountnumber=payer.payer_accountnumber
I'm aiming to show the payer address of the customer.. The customer table contains the customer address details as well as the payer address details. There are instances that the customer is also the payer, meaning the customer id value is same as the payer_id so the customer address and the payer address are the same.
For the case of customer with different payer, there is a table payer_account that has the payer details but not all payers are there. In the case that the payer_id is not in the table payer_account then it is in the customer table by looking for that payer_id to the customer_id field.


Customer id 1 has payer id of 2 which is Michael Jones, so customer address of that customer is the payer address of the customer Ann Jane..

Sample data from customer table
Id     Name     Payer_id     Payer_accountnumber     Customer_address1        Customer_address2
1     Ann Jane     2     12     12th Ave.     Bldg 45
2     Michael Jones     2     12     5th Ave.     Gate 6
Sample results:
Id     Name     Payer_id     Payer_accountnumber     Payer_address1     Payer_address2     Customer_address1     Customer_address2
1     Ann Jane     2     12     5th Ave.     Gate 6     12th Ave.     Bldg 45
2     Michael Jones     2     12     5th Ave.     Gate 6     5th Ave.     Gate 6
It would be greatly appreciated for any help.

Thanks a lot!

Edited by: BluShadow on 23-Feb-2012 14:12
added {noformat}
{noformat} tags to help readability (as far as possible based on what was posted).  Please read {message:id=9360002} and learn to do this yourself.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 1. Re: Query help
    L-MachineGun Pro
    Currently Being Moderated
    1) You need to provide us with create table and insert commands to be able to duplicate your results.
    2) Also provide enough test data to cover all the possibilities in your requirements.
    :p
  • 2. Re: Query help
    sb92075 Guru
    Currently Being Moderated
    .

    Edited by: sb92075 on Feb 23, 2012 9:54 AM
  • 3. Re: Query help
    788917 Newbie
    Currently Being Moderated
    Hi,

    Actually I have no rights to access the create table, I can just view them.

    The customer table holds the customer address at the same time the payer address incase that customer id is the payer of the other customer.

    I wanted for the sql to get the payer address from the payer_account table if there is one, otherwise it gets the payer address from the customer table. Both payer and customer addresses are on the customer table, if the customer is also the payer, there will only be on row on the customer table, if the payer is a different id, that will be an additional row on the customer table.

    I have here provided much clearer sample data..

    Sample data from customer table:
    Id        Name              Payer_id    Payer_accountnumber  
    A1       Ann Jane         123          DH123                        
    123      Michael Jones   123          DH123
    BC12    Margareth Fin    DELI        DGO45
    CED     Fred Smith        PENN       DG189
    PENN   Francois Martin   SFO2       DG189 
    
    Customer _address1     Customer_address2
    12th Ave.                      Gate 5
    13th Ave.                      Bldg 45
    248 St.                      Blk 5
    AV st.                      Blk 6
    6th Flr.                     14th Ave.
                    
                           
    Sample data from Payer_account table:
    Payer_id     Payer_address1     Payer_address2
    DELI               Blk 4 Lot 9     Green St.
    SF02               ADF Ave            AS Bldg
    Result data:
    Id        Name              Payer_id    Payer_accountnumber  
    A1       Ann Jane         123          DH123                        
    123      Michael Jones   123          DH123
    BC12    Margareth Fin    DELI        DGO45
    CED     Fred Smith        PENN       DG189
    PENN   Francois Martin   SFO2       DG189
    
    
    Customer _address1     Customer_address2
    12th Ave.                      Gate 5
    13th Ave.                      Bldg 45
    248 St.                      Blk 5
    AV st.                      Blk 6
    6th Flr.                     14th Ave.
    
    Payer_address1     Payer_address2
    13th Ave.             Bldg 45
    13th Ave.             Bldg 45
    Blk 4 Lot 9             Green St.
    AV st.             Blk 6
    ADF Ave             AS Bldg
    IN the sample data, there are 3 scenarios:
    Customer Ann Jane (Id-A1), her payer id is 123 which can't be found in the payer_account table but on the customer table. So the address of that customer will be the payer address of customer Ann Jane.


    Customers Michael Jones (Id-123) and Fred Smith (Id-CED), their payers can't also be found on the payer_account table but on the customer table. And, it's the same customer id, so the customer address will also be the payer address..

    Customers Margareth Fin (Id-BC12) and Francois Martin (Id-PENN), their payers id can be found on the payer account table, so the payer address will be from another table which is payer_account table..



    I hope it is now clearer. And hoping also for your help.

    Much thanks in advance!

    Edited by: 785914 on Feb 24, 2012 2:36 AM
  • 4. Re: Query help
    Etbin Guru
    Currently Being Moderated
    with
    del_history as
    (select 1 id from dual union all
     select 2 from dual union all
     select 3 from dual
    ),
    customer as
    (select 1 id,'Ann Jane' name,2 payer_id,12 payer_accountnumber,'12th Ave.' address_line1,'Bldg 45' address_line2 from dual union all
     select 2,'Michael Jones',2,12,'5th Ave.','Gate 6' from dual union all
     select 3,'John Doe',4,99,'10th Ave.','Gate 7' from dual
    ),
    payer_account as
    (select 4 cid,99 accountnumber,'7th Ave.' address_address_line1,'p.box 123' address_address_line2 from dual
    )
    select d.id,
           c.name,
           c.payer_id,
           c.payer_accountnumber,
           c.address_line1,
           c.address_line2,
           case when payer.id is not null
                then case when c.id = c.payer_id
                          then c.address_line1
                          else payer.address_line1
                     end
                else pa.address_address_line1
           end payer_address1,
           case when payer.id is not null
                then case when c.id = c.payer_id
                          then c.address_line2
                          else payer.address_line2
                     end
                else pa.address_address_line2
           end payer_address2
      from del_history d
           inner join
           customer c
        on d.id = c.id
           left outer join
           customer payer
        on c.payer_id = payer.id
           left outer join
           payer_account pa
        on c.payer_id = pa.cid
       and c.payer_accountnumber = pa.accountnumber
    Regards

    Etbin
  • 5. Re: Query help
    EdStevens Guru
    Currently Being Moderated
    785914 wrote:
    Hi,

    Actually I have no rights to access the create table, I can just view them.
    Can you do
    sql> desc mytable
    >
    The customer table holds the customer address at the same time the payer address incase that customer id is the payer of the other customer.

    I wanted for the sql to get the payer address from the payer_account table if there is one, otherwise it gets the payer address from the customer table. Both payer and customer addresses are on the customer table, if the customer is also the payer, there will only be on row on the customer table, if the payer is a different id, that will be an additional row on the customer table.

    I have here provided much clearer sample data..

    Sample data from customer table:
    Id        Name              Payer_id    Payer_accountnumber  
    A1       Ann Jane         123          DH123                        
    123      Michael Jones   123          DH123
    BC12    Margareth Fin    DELI        DGO45
    CED     Fred Smith        PENN       DG189
    PENN   Francois Martin   SFO2       DG189 
    
    Customer _address1     Customer_address2
    12th Ave.                      Gate 5
    13th Ave.                      Bldg 45
    248 St.                      Blk 5
    AV st.                      Blk 6
    6th Flr.                     14th Ave.
    Sample data from Payer_account table:
    Payer_id     Payer_address1     Payer_address2
    DELI               Blk 4 Lot 9     Green St.
    SF02               ADF Ave            AS Bldg
    Result data:
    Id        Name              Payer_id    Payer_accountnumber  
    A1       Ann Jane         123          DH123                        
    123      Michael Jones   123          DH123
    BC12    Margareth Fin    DELI        DGO45
    CED     Fred Smith        PENN       DG189
    PENN   Francois Martin   SFO2       DG189
    
    
    Customer _address1     Customer_address2
    12th Ave.                      Gate 5
    13th Ave.                      Bldg 45
    248 St.                      Blk 5
    AV st.                      Blk 6
    6th Flr.                     14th Ave.
    
    Payer_address1     Payer_address2
    13th Ave.             Bldg 45
    13th Ave.             Bldg 45
    Blk 4 Lot 9             Green St.
    AV st.             Blk 6
    ADF Ave             AS Bldg
    IN the sample data, there are 3 scenarios:
    Customer Ann Jane (Id-A1), her payer id is 123 which can't be found in the payer_account table but on the customer table. So the address of that customer will be the payer address of customer Ann Jane.


    Customers Michael Jones (Id-123) and Fred Smith (Id-CED), their payers can't also be found on the payer_account table but on the customer table. And, it's the same customer id, so the customer address will also be the payer address..

    Customers Margareth Fin (Id-BC12) and Francois Martin (Id-PENN), their payers id can be found on the payer account table, so the payer address will be from another table which is payer_account table..



    I hope it is now clearer. And hoping also for your help.

    Much thanks in advance!

    Edited by: 785914 on Feb 24, 2012 2:36 AM
  • 6. Re: Query help
    788917 Newbie
    Currently Being Moderated
    Hi,

    It doesn't work. I'm using query reporter Oracle HTML reporting utility in running the query. The database is oracle 10g. Sorry I'm not too technical person. Just starting to learn this stuff..
  • 7. Re: Query help
    788917 Newbie
    Currently Being Moderated
    Hi Etbin,

    Thank you so much!It works, however I just noticed now that there are customer with blank payer id so they were left behind, the query is not getting those records.

    For these customer where the payer id is blank, there is one field on customer table called account_data which has values either 1 or 0. 1 means that the customer is also the payer and 0 means someone is the payer.

    These field should be included for the records with no payer id.
    I need to add that on the case condition, so if the payer id is blank those records will also show.

    In your code, I added the field and add one customer,which the payer id is null or blank..The result should also get customer 5A with the same customer address as payer address..

    Would you please help me again? Sorry for causing you trouble, I'm really just struggling with this. Thanks much in advance..
    with
    del_history as
    (select 1 id from dual union all
     select 2 from dual union all
     select 3 from dual unionl all
     select 5A from dual
    ),
    customer as
    (select 1 id,'Ann Jane' name,2 payer_id,12 payer_accountnumber,0 account_data,
    '12th Ave.' address_line1,'Bldg 45' address_line2 from dual union all
    select 2,'Michael Jones',2,12,1,'5th Ave.','Gate 6' from dual union all
    select 3,'John Doe',4,99,0,'10th Ave.','Gate 7' from dual union all
    select 5A,'Mary Hill', ,100,1,'11th Ave.','Gate 9' from dual
    ),
    payer_account as
    (select 4 cid,99 accountnumber,'7th Ave.' address_address_line1,'p.box 123' address_address_line2 from dual
    )
    select d.id,
           c.name,
           c.payer_id,
           c.payer_accountnumber,
           c.address_line1,
           c.address_line2,
           case when payer.id is not null
                then case when c.id = c.payer_id
                          then c.address_line1
                          else payer.address_line1
                     end
                else pa.address_address_line1
           end payer_address1,
           case when payer.id is not null
                then case when c.id = c.payer_id
                          then c.address_line2
                          else payer.address_line2
                     end
                else pa.address_address_line2
           end payer_address2
      from del_history d
           inner join
           customer c
        on d.id = c.id
           left outer join
           customer payer
        on c.payer_id = payer.id
           left outer join
           payer_account pa
        on c.payer_id = pa.cid
       and c.payer_accountnumber = pa.accountnumber
  • 8. Re: Query help
    Etbin Guru
    Currently Being Moderated
    Like this ?
    with
    del_history as
    (select '1' id from dual union all
     select '2' from dual union all
     select '3' from dual union all
     select '5A' from dual union all
     select '5B' from dual
    ),
    customer as
    (select '1' id,'Ann Jane' name,'2' payer_id,12 payer_accountnumber,0 account_data,'12th Ave.' address_line1,'Bldg 45' address_line2 from dual union all
    select '2','Michael Jones','2',12,1,'5th Ave.','Gate 6' from dual union all
    select '3','John Doe','4',99,0,'10th Ave.','Gate 7' from dual union all
    select '5A','Mary Hill',null,100,0,'11th Ave.','Gate 9' from dual union all
    select '5B','Marion Hill',null,101,1,'12th Ave.','Gate 6' from dual
    ),
    payer_account as
    (select '4' cid,99 accountnumber,'7th Ave.' address_address_line1,'p.box 123' address_address_line2 
      from dual
    )
    select d.id,
           c.name,
           c.payer_id,
           c.payer_accountnumber,
           c.address_line1,
           c.address_line2,
           case when payer.id is not null
                then case when c.id = c.payer_id
                          then c.address_line1
                          else payer.address_line1
                     end
                else case when c.payer_id is not null 
                          then pa.address_address_line1 
                     end
           end payer_address1,
           case when payer.id is not null
                then case when c.id = c.payer_id
                          then c.address_line2
                          else payer.address_line2
                     end
                else case when c.payer_id is not null 
                          then pa.address_address_line2 
                     end
           end payer_address2
      from del_history d
           inner join
           (select id,
                   name,
                   nvl(payer_id,case account_data when 1 then id end) payer_id,
                   payer_accountnumber,
                   address_line1,
                   address_line2
              from customer
           ) c
        on d.id = c.id
           left outer join
           customer payer
        on c.payer_id = payer.id 
           left outer join
           payer_account pa
        on c.payer_id = pa.cid
       and c.payer_accountnumber = pa.accountnumber
    Regards

    Etbin
  • 9. Re: Query help
    788917 Newbie
    Currently Being Moderated
    Hi Etbin,

    This is very useful!Thanks so much for your help..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points