Swaminathan K
asked on
Oracle query 11g
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
The data is as below:
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.
As part of the application functionality when 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.
any help is really appreciated
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
The data is as below:
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.
As part of the application functionality when 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.
any help is really appreciated
Yes, please provide the "expected result"
I produced this result:
I included the field CUSTOMER_TYPE in all tables like you had it previously. The difference here is use of PRODUCT_GROUP_ID.
I produced this result:
| CUSTOMERID | CUSTOMER_NAME | CUSTOMER_TYPE | PRODUCT_GROUP_ID |
|------------|---------------|---------------|------------------|
| 101 | xyz | (null) | (null) |
| 102 | abc-modified | (null) | (null) |
| 103 | fgh | (null) | (null) |
| 104 | asd | (null) | (null) |
| 105 | wer | (null) | (null) |
| 106 | 456 | (null) | (null) |
| 107 | ssd | 1 | 678 |
| 109 | jkl | (null) | (null) |
| 110 | yui-modified | 3 | 678 |
Using this query:
SELECT
c.customerid
, COALESCE(cs.customer_name, c.customer_name) AS customer_name
, COALESCE(cp.customer_type, cs.customer_type, c.customer_type) AS customer_type
, COALESCE(cp.product_group_id,cp.product_group_id) AS product_group_id
FROM customer c
LEFT OUTER JOIN customer_sales cs
ON c.customerid = cs.customerid
AND cs.product_group_id = 678
LEFT OUTER JOIN customer_profile cp
ON c.customerid = cp.customer_id
AND cp.product_group_id = 678
ORDER BY
customerid
;
That query is derived from the accepted answer of your previous query (by slightwv)I included the field CUSTOMER_TYPE in all tables like you had it previously. The difference here is use of PRODUCT_GROUP_ID.
CREATE TABLE CUSTOMER
("CUSTOMERID" int, "CUSTOMER_NAME" varchar2(3), "CUSTOMER_TYPE" int)
;
INSERT ALL
INTO CUSTOMER ("CUSTOMERID", "CUSTOMER_NAME")
VALUES (101, 'xyz')
INTO CUSTOMER ("CUSTOMERID", "CUSTOMER_NAME")
VALUES (102, 'abc')
INTO CUSTOMER ("CUSTOMERID", "CUSTOMER_NAME")
VALUES (103, 'fgh')
INTO CUSTOMER ("CUSTOMERID", "CUSTOMER_NAME")
VALUES (104, 'asd')
INTO CUSTOMER ("CUSTOMERID", "CUSTOMER_NAME")
VALUES (105, 'wer')
INTO CUSTOMER ("CUSTOMERID", "CUSTOMER_NAME")
VALUES (106, '456')
INTO CUSTOMER ("CUSTOMERID", "CUSTOMER_NAME")
VALUES (107, 'ssd')
INTO CUSTOMER ("CUSTOMERID", "CUSTOMER_NAME")
VALUES (109, 'jkl')
INTO CUSTOMER ("CUSTOMERID", "CUSTOMER_NAME")
VALUES (110, 'yui')
SELECT * FROM dual
;
CREATE TABLE CUSTOMER_SALES
("CUSTOMERID" int, "CUSTOMER_NAME" varchar2(12), "PRODUCT_GROUP_ID" int, "CUSTOMER_TYPE" int)
;
INSERT ALL
INTO CUSTOMER_SALES ("CUSTOMERID", "CUSTOMER_NAME", "PRODUCT_GROUP_ID")
VALUES (101, 'xyz-modified', 405)
INTO CUSTOMER_SALES ("CUSTOMERID", "CUSTOMER_NAME", "PRODUCT_GROUP_ID")
VALUES (102, 'abc-modified', 678)
INTO CUSTOMER_SALES ("CUSTOMERID", "CUSTOMER_NAME", "PRODUCT_GROUP_ID")
VALUES (109, 'jkl', 978)
INTO CUSTOMER_SALES ("CUSTOMERID", "CUSTOMER_NAME", "PRODUCT_GROUP_ID")
VALUES (110, 'yui-modified', 678)
SELECT * FROM dual
;
CREATE TABLE CUSTOMER_PROFILE
("CUSTOMER_ID" int, "CUSTOMER_TYPE" int, "PRODUCT_GROUP_ID" int)
;
INSERT ALL
INTO CUSTOMER_PROFILE ("CUSTOMER_ID", "CUSTOMER_TYPE", "PRODUCT_GROUP_ID")
VALUES (103, 2, 978)
INTO CUSTOMER_PROFILE ("CUSTOMER_ID", "CUSTOMER_TYPE", "PRODUCT_GROUP_ID")
VALUES (110, 3, 678)
INTO CUSTOMER_PROFILE ("CUSTOMER_ID", "CUSTOMER_TYPE", "PRODUCT_GROUP_ID")
VALUES (101, 1, 405)
INTO CUSTOMER_PROFILE ("CUSTOMER_ID", "CUSTOMER_TYPE", "PRODUCT_GROUP_ID")
VALUES (107, 1, 678)
SELECT * FROM dual
;
see: http://sqlfiddle.com/#!4/5b666/2
ASKER
Hi ,
I need to create an view which will fetch the data. From the view i will put an where clause and get the requried data for product group 678.
The expected output from the view is
I need to create an view which will fetch the data. From the view i will put an where clause and get the requried data for product group 678.
The expected output from the view is
101 xyz -modified NA
102 abc NA
103 fgh NA
104 asd NA
105 wer NA
106 456 NA
107 ssd 1
109 jkl NA
110 yui-modified 3
SELECT
c.customerid
, CASE
WHEN cp.product_group_id = 678 AND cs.product_group_id = 678 THEN cs.customer_name
WHEN cp.product_group_id <> 678 AND cs.product_group_id <> 678 THEN cs.customer_name
ELSE c.customer_name
END
AS customer_name
, CASE
WHEN cp.product_group_id = 678 OR cs.product_group_id = 678
THEN COALESCE(CAST(cp.customer_type AS varchar(2)), 'NA')
ELSE 'NA'
END
AS customer_type
FROM customer c
LEFT OUTER JOIN customer_profile cp
ON c.customerid = cp.customer_id
LEFT OUTER JOIN customer_sales cs
ON c.customerid = cs.customerid
ORDER BY
customerid
;
| CUSTOMERID | CUSTOMER_NAME | CUSTOMER_TYPE |
|------------|---------------|---------------|
| 101 | xyz-modified | NA |
| 102 | abc | NA |
| 103 | fgh | NA |
| 104 | asd | NA |
| 105 | wer | NA |
| 106 | 456 | NA |
| 107 | ssd | 1 |
| 109 | jkl | NA |
| 110 | yui-modified | 3 |
see http://sqlfiddle.com/#!4/23472/1
ASKER
Hi ,
I cannot use the where clause in the select query, All the data should be in an view say orgdata_vw. I need to fire an filter as below
Select * from orgdata_vw where product_group_id=678;
Output should be as below
101 xyz -modified NA
102 abc NA
103 fgh NA
104 asd NA
105 wer NA
106 456 NA
107 ssd 1
109 jkl NA
110 yui-modified 3
I cannot use the where clause in the select query, All the data should be in an view say orgdata_vw. I need to fire an filter as below
Select * from orgdata_vw where product_group_id=678;
Output should be as below
101 xyz -modified NA
102 abc NA
103 fgh NA
104 asd NA
105 wer NA
106 456 NA
107 ssd 1
109 jkl NA
110 yui-modified 3
I cannot see how to meet all your needs - somebody else may.
Why is 102's result 'abc' and not 'abc-modified'?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot.
From a quick scan I think it will be very similar to what I posted in your previous question just with a where clause to pick only related records to product_group_id from the customer_sales table.
Are you saying you want this as the results:
Open in new window