cancel
Showing results for 
Search instead for 
Did you mean: 

Need all records whether a match or not with joined table

Former Member
0 Kudos

I have a report that joins an order table with the customer table.  I want to select all customers whether they have orders or not.  It is grouped by route and I want to show routes whether there are orders or not.  I can't seem to get the join to work to where it pull all the data I need.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

When you want to join two tables in crystal report with left out join and at the same time want to filter report based on right table e.g. ORDER.ORDER_DATE>='1-JAN-2014' the join will be converted to equi join

to solve the problem some people suggest to use

(ISNULL(ORDER.ORDER_DATE) OR ORDER.RDER_DATE>='1-JAN-2014')

The above solution only works when there are 0 orders for customer and you still want to show those custer but what if some customer have more than 0 orders and orders date is ON or BEFORE  '31-DEC-2013'. In such situation ISNULL(ORDER.ORDER_DATE) will not work. to solve such problem you need to  either add command object or create a view for orders table as following

1. Create command object with following sql

          Select Customer.customer_name,Order.Order_id, order.order_date

          from customer left outer join order on customer.customer_id=order.Customer_id and order.order_date>='1-JAN-2014'

2. second solution is to create a view for orders as

     create or replace view view_orderrs as

     Select * from oders where order.order_dt>='1-JAN-2014'

and then use the view in the report instead of order table as the right table.

venkateswaran_k
Active Contributor
0 Kudos

Pleaes check with LEFT OUTER JOIN.

This should work

Former Member
0 Kudos

Yes it should. I think some other table link is causing her a problem.

Former Member
0 Kudos

I only have 2 tables in my report.  Originally, I only had one link - Cust#.  Then I added the route link.  Both are Left Outer Joins.

Former Member
0 Kudos

So you have CUSTOMER  to  ORDERS, left outer. via CUST#

Then you added a route link? from where to where?

If you are linking CUSTOMER to ORDERS with 2 links, that is the problem. Add ORDERS again to the report and do the route link from CUSTOMER to ORDERS_1, left outer.

Crystal will rename the table-look at my link image above-every time a table name ends with _1, it is a table that has been added twice,

Former Member
0 Kudos

Even with only one link from Customers to Orders with a Left Outer Join on Cust# I don't get all the customers.  If I could get at least that to work, I would go forward with the Route portion.

Former Member
0 Kudos

 

Hi Gayle,

--Write a query at database end with left outer join between those two table and Check whether you get appropriate results or not?

--If you get appropriate result use that query directly in crystal designer using ADD command.

Thanks & regards,

Naga.

Former Member
0 Kudos

Then it is time to look at your data selection.

First remove the selection formula... are you getting all the data now?

If yes, and you are selecting on a field(s) in orders, you are probably blocking customers that do not have orders... You must select for customers with no orders.

If no, add      IsNull(orders.cust#) or     to the start of your record selection, and put the rest inside paranthesis, it may solve it.

Good luck... I will be back in the morning.

Former Member
0 Kudos

I remove my selections and still didn't get any customers without orders.  I finally started over and changed the table I was using to a less detailed table for the orders and I thought I got it to work, but as I looked closer, it didn't.

Message was edited by: Gayle Burke

JWiseman
Active Contributor
0 Kudos

hi Gayle,

in some cases no combination of linking will ever get records back where there is a subgrouping that is missing info.

the easy way to accomplish this is to:

1) create a brand new report just using the customer table...in the database menu ensure that Select Distinct records is applied

2) Insert a Subreport (new) based on the orders tables

3) link the main report on the customer id

the other option, if you like messing with sql, is to create a Command Object as your datasource instead of using tables. in the command object you'd use a subquery to show the orders amount...this is usually more work though as you have to make a subquery to bring back any orders related fields.

one other option is here...

http://scn.sap.com/people/jamie.wiseman/blog/2012/02/29/show-all-values-for-a-group-eg-show-all-prod...

cheers,

jamie

JWiseman
Active Contributor
0 Kudos

p.s.  here's a sample showing the command object method that i mentioned in the previous post...

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/1020d094-f847-2f10-da86-b149a69211dc

Former Member
0 Kudos

When you want to join two tables in crystal report with left out join and at the same time want to filter report based on right table e.g. ORDER.ORDER_DATE>='1-JAN-2014' the join will be converted to equi join

to solve the problem some people suggest to use

(ISNULL(ORDER.ORDER_DATE) OR ORDER.RDER_DATE>='1-JAN-2014')

The above solution only works when there are 0 orders for customer and you still want to show those custer but what if some customer have more than 0 orders and orders date is ON or BEFORE  '31-DEC-2013'. In such situation ISNULL(ORDER.ORDER_DATE) will not work. to solve such problem you need to  either add command object or create a view for orders table as following

1. Create command object with following sql

          Select Customer.customer_name,Order.Order_id, order.order_date

          from customer left outer join order on customer.customer_id=order.Customer_id and order.order_date>='1-JAN-2014'

2. second solution is to create a view for orders as

     create or replace view view_orderrs as

     Select * from oders where order.order_dt>='1-JAN-2014'

and then use the view in the report instead of order table as the right table.

Former Member
0 Kudos

 

Hi Gayle,

Click on the link between the two tables--> right click and select 'linking options' --> select left outer join if you used the customer table as left hand side or use the appropriate outer join based on the position on the table.

Thanks & regards,

Naga.

Former Member
0 Kudos

I have already tried that.  I've also tried different enforcements. 

Former Member
0 Kudos

Table A   <-- left outer join    Table B   will give you every record in table A and matching records in table B

Table A   right outer join  --->   Table B will give you every record in table B and matching records in table A

Note on Left Outer: I have found that once you use a left outer  Table A <-- table B, then all tables linked below table B ( like from table B to Table C  and table B to table D  and table D to table E) must also be left outer.

Edited by Debi Herbert

Your links would be:

Customers to Orders, left outer join   

Customers to Routes, left outer join    even though you're grouping on the route

now you will get all the customers, the route for the customer if it exists, and any existing orders for the customer

Former Member
0 Kudos

I've tried that.  I had no luck.  Debi,  how do you handle the Enforced to/from, etc?

Former Member
0 Kudos

Hi Gayle,

What is the database you are using?

Thanks & regards,

Naga.

Former Member
0 Kudos

It's an OBDC.  Does that help?

abhilash_kumar
Active Contributor
0 Kudos

Hi Gayle,

You select the Enforcement options based on which table's records you select.

For eg: If set the Enforcement to 'Enforce from' and I select records ONLY from the table on the left, the join will Not happen. If you look at the SQL Query, you should notice that the Join hasn't been enforced.

In such situations, when you're not sure which records you would select, it is safe to set the Enforcement to 'Enforce both'.

-Abhilash

Former Member
0 Kudos

I don't. It works by itself once a field is added to the report for the lower table, and ignores it if no fields are added. As you are having problems, I would remove them.

I suggest you start with a new test report with just the 3 tables and make the links as I described, and see what you get.  Other table links may be causing a problem. You could do the test, adding one table at a time starting with customer, then orders, then route. When you get it working, add one table at a time and see where it fails.

Note: I have found that mixing left outer and right outer can be troublesum. You want your links to flow in one direction for best results.

note: when using left outer, direction of links are important.  All tables linked FROM customer TO another table must be left outer. links to customer (table x TO Customer) may be equals, or left outer if you want all records in table x as well. The danger here is if the customer is not found in the first table, you will not get him.

If you like, you could insert a picture of the link page, and I will look at it.

Former Member
0 Kudos

Here is a picture of links I snatched out of a report I am working on. It is composed of all left outer links. Were I to add any EQUAL links between the these tables and any other table, it would restrict the results.