[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

Need help on oracle sql query

Hi ,

I have an table called customer which  is in customers schema and has the below table structure.
Customers
Customer_id Number,
Customer_name varchar2(50)
customer_type number  -->always null value


I have another 2 tables in sales schema.

In this schema , i have an table called customer_sales , which has the below table structure

customer_sales
customer_id number --> ID from customer table in customers schema
Customer_name varchar2(50),
customer_type number --> which can be either 1,2,3 1--> internal customer , 2-external customer and 3 -both

Another table called Customer_profile in sales schema :
Customer_id number -> ID from customer table in customer schema.
customer_type number --> which can be either 1,2,3 1--> internal customer , 2-external customer and 3 -both


As part of the application functionality , when we create an customer from his site  , we insert record in customer table in customer schema and customer_profile in sales schema.
When we create an customer in sales person site , we insert an record in customer schema's customer table and sales schema's customer_sales.

The data is as below:
Customer:
CustomerId  Customer_name  customer_type
101                     xyz                        NULL
102                     abc                        NULL
103                      fgh                        NULL
104                      asd                        NULL
105                      wer                       NULL
106                      456                        NULL


Customer_sales
CustomerId  Customer_name  customer_type
101                  xyz                                 3
102                 abc                                  1


Customer_profile
customer_id customer_type
 103                    2

The requirenent is I need to select all customers who do not have an entry in customer_sales and customer_profile  . if it is present , then i should pull the data from either customer_sales or customer_profile . The output should be as below

Customer_id customer_name customer_type

101                     xyz                        3
102                     abc                        1
103                      fgh                        2
104                      asd                        NULL
105                      wer                       NULL
106                      456                        NULL


I should override the customer_type information for customer_type column in customer table if an entry is present in customer_sales or customer_profile table.

Any help is really appreciated.
0
sam_2012
Asked:
sam_2012
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
See if this is what you want:
select c.customerid,
	coalesce(cs.customer_name, c.customer_name) customer_name,
	coalesce(cp.customer_type, cs.customer_type, c.customer_type) customer_type
from customer c
	left outer join customer_sales cs on c.customerid=cs.customerid
	left outer join customer_profile cp on c.customerid=cp.customer_id
order by 1
/

Open in new window

0
 
awking00Commented:
Not sure if coalesce is needed for customer_name if customer_type is always null in customers table. Wouldn't the following also work?
select c.customerid, c.customer_name, coalesce(cp.customer_type, cs.customer_type) customer_type
from customer c
left outer join customer_sales cs on c.customerid=cs.customerid
left outer join customer_profile cp on c.customerid=cp.customer_id
order by 1
0
 
sam_2012Author Commented:
Thanks for the quick response , but the requiremen as changed . Iam not so confident in writing the query for the below requirement , any help is really appreciated. Its more challenging query.

Today , the application team made an major change in the application functionality , Now we create an customer from his site , we insert an record in customer table . Now the customer type column is removed and would be derived from customer_type table for each sales person site. For example customer_id=107 record.
When we create an customer from sales person site , initially , if the customer  has not made the payment , we just create his details  and do not attach any customer type to him. In this case we insert an record in both customer and customer_sales . Now we have included an new column called product_group_id , which indicates the sales person site who created this customer, but we do not store the product_group_id in customer table. Also , when an sales person is moved between regions , it is possible that we insert one record for the customer in customer table and add his customer type in customer_type of the sales person schema. For example : customerid=110.

For example:

Customer:
 CustomerId  Customer_name  
 101                     xyz                    
 102                     abc                    
 103                      fgh                    
 104                      asd                  
 105                      wer                  
 106                      456                    
 107                      ssd        
109                       jkl
110                       yui

Customer_sales
 CustomerId  Customer_name  product_group_id
101                  xyz-modified                               405
102                 abc-modified                              678
109                 jkl                                                 978
110               yui-modified                                 678

 Customer_profile
 customer_id customer_type product_group_id
  103                    2                        978
  110                    3                        678
  101                   1                        405
  107                    1                        678.


Now my requirement is when i login in the applicaiton from sales person : 678 , i need to display all customer data from customer table , if any record is present for the customer id in customer_sales table then show the data from it and not from customer table. Also , the customer type will be derived as NULL for all the records in customer and customer sales table if no matching records are found for the combination customer_id and product _group_id . Now the ouput should be as below for product_group_id=678


Customer_id customer_name customer_type

101                     xyz -modified              NA --> because no record exists in customer_type table for the combination  
                                                                                    of customer id and product_group_id
 102                     abc                        NA -->        here we are overriding the customer info in customer table with  
                                                                             customer_sales table if we find an row for product_group_id =678 in
                                                                             customer sales table.
 103                      fgh                         NA          --> same reason as customer id 101
 104                      asd                        NA          --> same reason as customer id 101
 105                      wer                       NA      --> same reson as customer id 101
 106                      456                        NA      --.  same reason as customer 101
 107                      ssd                           1             --> in this case the sales person has moved from one region to another ,
                                                                                      hence his previous existing customer payment is recorded and his
                                                                                     type is inserted in the customer_type table. But there will no entry in
                                                                                       the customer_sales for this customer.
109                         jkl                            NA           --> Same reason as customer id 101
110                    yui-modified             3                --> Need to override the customer name in customer table, pick it from
                                                                                        customer sales table , it is present for the product group id 678 and
                                                                                         also pick the customer type table if an row is present for product
                                                                                        group id 678.
0
 
sam_2012Author Commented:
awesome
0
 
slightwv (䄆 Netminder) Commented:
>>Not sure if coalesce is needed for customer_name if customer_type is always null in customers table. Wouldn't the following also work?

The reason I added to coalesce was because of the requirement:
should override the customer_type information for customer_type column

I took that to mean that if a value exists in the other table, use it over the one in the customer table.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now