Skip to Content

Crystal Server Join querying one row at a time

Sep 18, 2017 at 01:30 PM


avatar image
Former Member

I have join between two tables . and there is a left join between the

vehicles left outer join members. on vehicles.mbr_id=members.mbr_id

in SAP Crystal report it is mentioned as an outer join

The behavior I am seeing is that Crystal Report is sending select query for each member_id from vehicles table and sending a query on members table where filter for each ID.

So instead of joining the two tables.. It is sending query for each and every member one by one and returning the result and combining them. Which is taking ages .

Why is it not joining in one query?? Any ideas

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Don Williams
Sep 18, 2017 at 01:35 PM

Connect to your Database and in the Connection Wizard select the Link Tab.

Right click on the line between the 2 tables and select Options, you can now change the link type to LOJ.

Also, are both tables in the same DB and using the same connection?

What DB are you using and which Client are you using? Some older Clients are not capable of a left OJ or only one.


Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

We are using MariaDB for the database. The Views are on different databases on the same server, So using two different connections But below database supports cross database joins without an issue.

The link type is already LOJ but in the backend it is doing a single select on the right side of the join on the database . and then merging the result on the server.

This is the query. The second query is being compiled for each memberid from the first select.

Mariadb Copart cobaltp SELECT `auction_data1`.`auction_date`, `vehicles_de_lookup1`.`lot_num`, `vehicles_de_lookup1`.`state`, `vehicles_de_lookup1`.`sort_name_of_make`, `vehicles_de_lookup1`.`sort_name_of_model`, `vehicles_de_lookup1`.`vin_serial_num`, `vehicles_de_lookup1`.`first_registration_date`, `vehicles_de_lookup1`.`usage`, `vehicles_de_lookup1`.`high_bid_amount`, `vehicles_de_lookup1`.`member_paid`, `vehicles_de_lookup1`.`sale_doc_given_to_buyer_date`, `vehicles_de_lookup1`.`left_yard_date`, `vehicles_de_lookup1`.`yard_id`, `vehicles_de_lookup1`.`co2_emission`, `vehicles_de_lookup1`.`euronorm`, `vehicles_de_lookup1`.`gross_vehicle_weight`, `vehicles_de_lookup1`.`vehicle_length`, `vehicles_de_lookup1`.`seats`, `vehicles_de_lookup1`.`sale_doc_type_description`, `vehicles_de_lookup1`.`member_id`, `vehicles_de_lookup1`.`HP`, `vehicles_de_lookup1`.`fuel_type_description`, `vehicles_de_lookup1`.`primary_damage_type_description`, `vehicles_de_lookup1`.`MemberChargersGroup_8`, `vehicles_de_lookup1`.`MemberChargersGroup_5`, `vehicles_de_lookup1`.`MemberChargersGroup_6`, `vehicles_de_lookup1`.`vat_section`, `vehicles_de_lookup1`.`CC` FROM `cobaltp`.`vehicles_de_lookup` `vehicles_de_lookup1` INNER JOIN `cobaltp`.`auction_data` `auction_data1` ON `vehicles_de_lookup1`.`id`=`auction_data1`.`vehicle_id` WHERE `auction_data1`.`auction_date`={d '2017-09-13'} EXTERNAL JOIN vehicles_de_lookup1.member_id={?Mariadb Copart memberp: member_de_lookup1.mbr_id}

Mariadb Copart memberp SELECT `member_de_lookup1`.`first_nm`, `member_de_lookup1`.`last_nm`, `member_de_lookup1`.`company_nm`, `member_de_lookup1`.`addr_line_1`, `member_de_lookup1`.`addr_line_2`, `member_de_lookup1`.`addr_line_3`, `member_de_lookup1`.`cntry_a3_cd`, `member_de_lookup1`.`city`, `member_de_lookup1`.`state_cd`, `member_de_lookup1`.`postal_cd`, `member_de_lookup1`.`email`, `member_de_lookup1`.`phone_number`, `member_de_lookup1`.`member_tax_nm`, `member_de_lookup1`.`comm_type_cd`, `member_de_lookup1`.`mbr_id` FROM `memberp`.`member_de_lookup` `member_de_lookup1` WHERE `member_de_lookup1`.`mbr_id`={?Mariadb Copart cobaltp: vehicles_de_lookup1.member_id}.


Looks like query is running as you/crystal have defined it.

In cases of joining external databases I have found it better to use a Command rather than let Crystal build query from database tables wizard.

You can write much more efficient SQL yourself.


Dell Stinnett-Christy Sep 18, 2017 at 02:50 PM

Also, if you have a subreport, then the subreport query will run individually for each time it appears on the report instead of once for the report and then being filtered by the subreport link.


10 |10000 characters needed characters left characters exceeded