asked on

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.

Guy Hengel [angelIII / a3]

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
;

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Swaminathan K

ASKER

I cannot add the condition , in the join clause. because this will be an view which will have this data.

PortletPaul

perhaps worth mentioning in the question?

PortletPaul

sorry perhaps someone else can solve this, I'm unable to spend more time on it right now.

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

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

Swaminathan K

ASKER

thanks .