• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

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
0
sam_2012
Asked:
sam_2012
  • 4
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Please show the expected results as a whole and not an explanation on why or why not the row is there.

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:
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

Open in new window

0
 
PortletPaulCommented:
Yes, please provide  the "expected result"

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 |

Open in new window

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
;

Open in new window

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

Open in new window

0
 
sam_2012Author Commented:
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
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

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
PortletPaulCommented:
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
;

Open in new window

| 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 |

Open in new window

see http://sqlfiddle.com/#!4/23472/1
0
 
sam_2012Author Commented:
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
0
 
PortletPaulCommented:
I cannot see how to meet all your needs - somebody else may.
0
 
slightwv (䄆 Netminder) Commented:
Why is 102's result 'abc' and not 'abc-modified'?
0
 
PortletPaulCommented:
A simple query of the sample data results in the table below. Notice columns CS_PGRP_ID & CP_PGRP_ID (the 2 product id field) are not always available (there are NULLs) and that one might be present while the other is not, and that they could also both be present but different.
| C_CUST_ID | C_CUST_NAME | CS_CUST_NAME | CS_PGRP_ID | CP_PGRP_ID | CP_CUST_TYP |
|-----------|-------------|--------------|------------|------------|-------------|
|       101 |         xyz | xyz-modified |        405 |        405 |           1 |
|       102 |         abc | abc-modified |        678 |     (null) |      (null) |
|       103 |         fgh |       (null) |     (null) |        978 |           2 |
|       104 |         asd |       (null) |     (null) |     (null) |      (null) |
|       105 |         wer |       (null) |     (null) |     (null) |      (null) |
|       106 |         456 |       (null) |     (null) |     (null) |      (null) |
|       107 |         ssd |       (null) |     (null) |        678 |           1 |
|       109 |         jkl |          jkl |        978 |     (null) |      (null) |
|       110 |         yui | yui-modified |        678 |        678 |           3 |

--That query for that table:

SELECT
      c.customerid         c_cust_id
    , c.customer_name      c_cust_name
    , cs.customer_name     cs_cust_name
    , cs.product_group_id  cs_pgrp_id
    , cp.product_group_id  cp_pgrp_id
    , cp.customer_type     cp_cust_typ
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
      c.customerid
;

Open in new window

If you were to create a view for this data, and filter that view for a product ID of 678, you would never get the expected result you have provided. You would receive a lot less, like this:
| C_CUST_ID | C_CUST_NAME | CS_CUST_NAME | CS_PGRP_ID | CP_PGRP_ID | CP_CUST_TYP |
|-----------|-------------|--------------|------------|------------|-------------|
|       107 |         ssd |       (null) |     (null) |        678 |           1 |
|       110 |         yui | yui-modified |        678 |        678 |           3 |

-- query for that result

SELECT
      c.customerid         c_cust_id
    , c.customer_name      c_cust_name
    , cs.customer_name     cs_cust_name
    , cs.product_group_id  cs_pgrp_id
    , cp.product_group_id  cp_pgrp_id
    , cp.customer_type     cp_cust_typ
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
WHERE
      cp.product_group_id  = 678
   OR cp.customer_type     = 678
ORDER BY
      c.customerid
;

Open in new window

So, right now I don't see how your expectations are going to be met.
0
 
sam_2012Author Commented:
Thanks a lot.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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