on 05-30-2012 10:49 PM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Pleaes check with LEFT OUTER JOIN.
This should work
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
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.
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...
cheers,
jamie
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
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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.
User | Count |
---|---|
80 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.