Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need help on oracle query

Posted on 2014-09-02
8
Medium Priority
?
404 Views
Last Modified: 2014-09-03
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.
0
Comment
Question by:sam_2012
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40298172
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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 800 total points
ID: 40298196
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
 

Author Comment

by:sam_2012
ID: 40298215
I cannot add the condition , in the join clause. because this will be an view which will have this data.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 40298303
perhaps worth mentioning in the question?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40298325
sorry perhaps someone else can solve this, I'm unable to spend more time on it right now.
0
 
LVL 32

Accepted Solution

by:
awking00 earned 1200 total points
ID: 40299092
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
 
LVL 32

Expert Comment

by:awking00
ID: 40299103
>>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
 

Author Closing Comment

by:sam_2012
ID: 40301689
thanks  .
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question