Need help on oracle query

Hi,

I have 3 tables namely customer , sales_customer , sales_customer_profile .  Our implementation is such that , customer is the master table which stores all the retail organizations . sales_customer and sales_customer_profile is used by each sales division by state .

Customer
Customer_Id ,  Customer_Name , Customer_city
101                       XYZ LTD                     CHN
102                       ABC LTD                    BNG
103                       TRY LTD                     APR
104                        JKL LTD                      GHN  

sales_customer
sales_customer_id , customer_id    , state_id , customer_city , customer_name
1                                   101                        201        CHN                        XYZ LTD
2                                  101                          301       CHN                        XYZ LTD
3                                   102                       201         BNG                         ABC LTD
4                                   103                        201       APR                            TRY LTD
5                                   104                        401      GHN                           JKL LTD

sales_customer_profile
sales_customer_profile_id , customer_id , state_id , is_wholesaledealer , is_retailer , is_customer
1                                                101                    201           Y                                    N                    Y
2                                                 101                    301          N                                    N                  Y
3                                                  102                  201           N                                   Y                      N
4                                                  103                     201        N                                   N                   Y

I need to write an query which pull the data as below :

sales_customer_id        , customer_id ,      customer_name      state_id      , is_wholesaledealer , is_retailer , is_customer
2                                                101                      XYZ LTD                301                   N                             N                   Y
NULL                                         102                      ABC LTD               NULL                N                             N                  N
NULL                                          103                    TRY LTD               NULL                  N                            N                  N
NULL                                           104                    JKL LTD                NULL                  N                            N                   N

below is the query , I tried but not getting the resutls


Select sc.sales_customer_id ,
c.customer_id,
scp.state_id ,
decode(scp.is_wholesaledealer,NULL,'N',scp.is_wholesaledealer) is_wholesaledealer,
decode(scp.is_retailer,NULL,'N',scp.is_retailer) is_retailer,
decode(scp.is_customer,NULL,'N',scp.is_customer) is_customer
from
customer c
left outer join sales_customer sc on (sc.customer_id=c.customer_id)
left outer join sales_customer_profile scp on (scp.customer_id=c.customer_id)
where scp.state_id=301;

The requirement is that if i belong to state 301 , if any record is present either in customer_sales and customer_sales_profile for the state_id =301 then show the details from it , else show null values for other organizations.
sam_2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
left outer join sales_customer_profile scp on (scp.customer_id=c.customer_id)
where scp.state_id=301;

will behave like this:

innert join sales_customer_profile scp on (scp.customer_id=c.customer_id)
where scp.state_id=301;

instead, you want to do this:

left outer join sales_customer_profile scp on (scp.customer_id=c.customer_id) AND scp.state_id=301
;
0
PortletPaulfreelancerCommented:
this result:
| SALES_CUSTOMER_ID | CUSTOMER_ID | CUSTOMER_NAME | STATE_ID | IS_WHOLESALEDEALER | IS_RETAILER | IS_CUSTOMER |
|-------------------|-------------|---------------|----------|--------------------|-------------|-------------|
|                 2 |         101 |       XYZ LTD |      301 |                  N |           N |           Y |
|            (null) |         102 |       ABC LTD |   (null) |                  N |           N |           N |
|            (null) |         104 |       JKL LTD |   (null) |                  N |           N |           N |
|            (null) |         103 |       TRY LTD |   (null) |                  N |           N |           N |

Open in new window

Produced by the following query:
SELECT
      sc.sales_customer_id
    , c.customer_id
    , c.customer_name
    , scp.state_id
    , CASE
            WHEN scp.is_wholesaledealer IS NULL THEN 'N'
            ELSE scp.is_wholesaledealer END AS is_wholesaledealer
    , CASE
            WHEN scp.is_retailer IS NULL THEN 'N'
            ELSE scp.is_retailer END        AS is_retailer
    , CASE
            WHEN scp.is_customer IS NULL THEN 'N'
            ELSE scp.is_customer END        AS is_customer
FROM customer c
      LEFT JOIN sales_customer sc
                  ON (sc.customer_id = c.customer_id)

                  AND sc.state_id = 301 --<< filter state_id here

      LEFT JOIN sales_customer_profile scp
                  ON (scp.customer_id = c.customer_id)
                  AND scp.state_id = sc.state_id  --<< and state_id is filtered here too
ORDER BY
      sc.sales_customer_id
;

--ADDITIONAL info
CREATE TABLE CUSTOMER 
	("CUSTOMER_ID" int, "CUSTOMER_NAME" varchar2(7), "CUSTOMER_CITY" varchar2(3))
;

INSERT ALL 
	INTO CUSTOMER  ("CUSTOMER_ID", "CUSTOMER_NAME", "CUSTOMER_CITY")
		 VALUES (101, 'XYZ LTD', 'CHN')
	INTO CUSTOMER  ("CUSTOMER_ID", "CUSTOMER_NAME", "CUSTOMER_CITY")
		 VALUES (102, 'ABC LTD', 'BNG')
	INTO CUSTOMER  ("CUSTOMER_ID", "CUSTOMER_NAME", "CUSTOMER_CITY")
		 VALUES (103, 'TRY LTD', 'APR')
	INTO CUSTOMER  ("CUSTOMER_ID", "CUSTOMER_NAME", "CUSTOMER_CITY")
		 VALUES (104, 'JKL LTD', 'GHN')
SELECT * FROM dual
;

CREATE TABLE SALES_CUSTOMER
	("SALES_CUSTOMER_ID" int, "CUSTOMER_ID" int, "STATE_ID" int, "CUSTOMER_CITY" varchar2(3), "CUSTOMER_NAME" varchar2(7))
;

INSERT ALL 
	INTO SALES_CUSTOMER ("SALES_CUSTOMER_ID", "CUSTOMER_ID", "STATE_ID", "CUSTOMER_CITY", "CUSTOMER_NAME")
		 VALUES (1, 101, 201, 'CHN', 'XYZ LTD')
	INTO SALES_CUSTOMER ("SALES_CUSTOMER_ID", "CUSTOMER_ID", "STATE_ID", "CUSTOMER_CITY", "CUSTOMER_NAME")
		 VALUES (2, 101, 301, 'CHN', 'XYZ LTD')
	INTO SALES_CUSTOMER ("SALES_CUSTOMER_ID", "CUSTOMER_ID", "STATE_ID", "CUSTOMER_CITY", "CUSTOMER_NAME")
		 VALUES (3, 102, 201, 'BNG', 'ABC LTD')
	INTO SALES_CUSTOMER ("SALES_CUSTOMER_ID", "CUSTOMER_ID", "STATE_ID", "CUSTOMER_CITY", "CUSTOMER_NAME")
		 VALUES (4, 103, 201, 'APR', 'TRY LTD')
	INTO SALES_CUSTOMER ("SALES_CUSTOMER_ID", "CUSTOMER_ID", "STATE_ID", "CUSTOMER_CITY", "CUSTOMER_NAME")
		 VALUES (5, 104, 401, 'GHN', 'JKL LTD')
SELECT * FROM dual
;

CREATE TABLE SALES_CUSTOMER_PROFILE
	("SALES_CUSTOMER_PROFILE_ID" int, "CUSTOMER_ID" int, "STATE_ID" int, "IS_WHOLESALEDEALER" varchar2(1), "IS_RETAILER" varchar2(1), "IS_CUSTOMER" varchar2(1))
;

INSERT ALL 
	INTO SALES_CUSTOMER_PROFILE ("SALES_CUSTOMER_PROFILE_ID", "CUSTOMER_ID", "STATE_ID", "IS_WHOLESALEDEALER", "IS_RETAILER", "IS_CUSTOMER")
		 VALUES (1, 101, 201, 'Y', 'N', 'Y')
	INTO SALES_CUSTOMER_PROFILE ("SALES_CUSTOMER_PROFILE_ID", "CUSTOMER_ID", "STATE_ID", "IS_WHOLESALEDEALER", "IS_RETAILER", "IS_CUSTOMER")
		 VALUES (2, 101, 301, 'N', 'N', 'Y')
	INTO SALES_CUSTOMER_PROFILE ("SALES_CUSTOMER_PROFILE_ID", "CUSTOMER_ID", "STATE_ID", "IS_WHOLESALEDEALER", "IS_RETAILER", "IS_CUSTOMER")
		 VALUES (3, 102, 201, 'N', 'Y', 'N')
	INTO SALES_CUSTOMER_PROFILE ("SALES_CUSTOMER_PROFILE_ID", "CUSTOMER_ID", "STATE_ID", "IS_WHOLESALEDEALER", "IS_RETAILER", "IS_CUSTOMER")
		 VALUES (4, 103, 201, 'N', 'N', 'Y')
SELECT * FROM dual
;

http://sqlfiddle.com/#!4/4f187/9

Open in new window

You need to take care the when using an OUTER join that you do not override that through the where clause. In you original query you had killed the outer join by using

where scp.state_id=301

so no NULLs from scp are allowed (and so the outer join become the same as an inner join)

In my query you use

AND sp.state_id=301

So that the outer joins do allows NULLs, and I have joined sc & scp through this stated_id as well so you only have to nominate it once.
0
sam_2012Author Commented:
I cannot add the condition , in the join clause. because this will be an view which will have this data.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
perhaps worth mentioning in the question?
0
PortletPaulfreelancerCommented:
sorry perhaps someone else can solve this, I'm unable to spend more time on it right now.
0
awking00Commented:
select
max(sales_customer_id) sales_customer_id,
x. customer_id customer_id, max(x.customer_name) customer_name,
max(x.state_id) state_id,
max(nvl(is_wholesaledealer,'N')) is_wholesaledealer,
max(nvl(is_retailer,'N')) is_retailer,
max(nvl(is_customer,'N')) is_customer
from
(select
 decode(state_id,301,sales_customer_id) sales_customer_id,
 customer_id,
 decode(state_id,301,state_id) state_id,
 customer_name
 from sales_customer) x
left join
(select
 customer_id,
 decode(state_id,301,state_id) state_id,
 decode(state_id,301,is_wholesaledealer,'N') is_wholesaledealer,
 decode(state_id,301,is_retailer,'N') is_retailer,
 decode(state_id,301,is_customer,'N') is_customer
 from sales_customer_profile) y
on x.state_id = y.state_id
and x.customer_id = y.customer_id
group by x.customer_id
order by x.customer_id;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
>>decode(state_id,301,is_wholesaledealer,'N') is_wholesaledealer,
 decode(state_id,301,is_retailer,'N') is_retailer,
 decode(state_id,301,is_customer,'N') is_customer<<
'N' here is superfluous -
decode(state_id,301,is_wholesaledealer) is_wholesaledealer,
 decode(state_id,301,is_retailer) is_retailer,
 decode(state_id,301,is_customer) is_customer
0
sam_2012Author Commented:
thanks  .
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.