odbc - sql server - outer joins?

[mchoss]

Crystal 8.5
Enterprise 8
SQL Server 2000
Win 2000 server

Does anyone know how to get outer joins to work through a SQL server ODBC connection?

At present i get an error telling me they will not.

Thanks in advance

 

[vidru]

I've never had problems with ODBC SQL Server connections and outer joins. What particular error message are you getting? What ODBC driver are you using?

Here's a .pdf specific to outer join errors with CR 8.5:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr85_left_outer_join.pdf.asp

-dave

 

[eo]

I had this problem, and it took quite some effort to resolve as the doc proposed above was very vague!

1) Navigate to your ODBC Data Sources
2) Click on the drivers tab
3) Find the name of the ODBC driver on which you are trying to do a left_outer_join (for example Pervasive Software ODBC-32; or SQL Server)
4) Scroll to the right to find DLL file name (for example W32BTINT.dll for Pervasive Software ODBC-32; or SQLSRV32.dll for SQL Server)
5) Write it down!
6) Go to

http://support.businessobjects.com/communityCS/TechnicalPapers/cr85_left_outer_join.pdf.asp

7) Go through check list on pages 5 (bottom) to 6 (top)
8) Create the text fine as per instructions on Page 6 (quite straight forward) (Steps 2 to 9)
9) Example of the txt file that solved my problem:
OracleJoinBuilder.txt -> this was for Pervasive Software 32.ODBC
Windows Registry Editor Vesrion 5.00
[HKEY_CURRENT_USER\Software\Crystal Decisions\9.0\Crystal Reports\Database\QueryBuilder\JoinBuilder]

PlusEqual

"OracleJoinBuilder" = "w32btint"

Hope this helped

Etienne Oosthuysen
Hertfordshire, England

 

[mchoss]

That's just what i have just found on the site.

Trying it now - will let you know how i get on!

Cheers

 

[mchoss]

Hmmm.

Same error: "Outer join operators cannot be specified in a query containing joined tables"

I have added the following:

[HKEY_LOCAL_MACHINE\SOFTWARE\Seagate Software\Crystal Reports\Database Options\Outerjoin]
"OJSyntax"="sqlsrv32"

I must admit, i had to add the 'Database Options' key ... is that to be expected or should it have been there anyway?

I am using the SQL Server ODBC driver (as it seems to be the only one available for SQL Server).

The query is as follows (brace yourself):

SELECT DISTINCT
I_DETAILS."incno", I_DETAILS."incdate", I_DETAILS."stn", I_DETAILS."risk", I_DETAILS."location", I_DETAILS."comments", I_DETAILS."ss_life_risk", I_DETAILS."time_mobilisation", I_DETAILS."time_arrival", I_DETAILS."time_stop", I_DETAILS."time_last_return", I_DETAILS."time_failed", I_DETAILS."map_east", I_DETAILS."map_north", I_DETAILS."created_dt",
L_MAIN_TYPES."descr",
L_SUBTYPES."descr",
L_SS_TYPE."descr",
L_SS_SUBTYPE."descr",
L_CAUSE."descr",
L_EXTING_METHOD."descr",
L_CALL_METHOD."descr",
F_CLOSURE."incno", F_CLOSURE."room_of_orig", F_CLOSURE."reason", F_CLOSURE."other_reason", F_CLOSURE."fire_spread_info", F_CLOSURE."violence_crew", F_CLOSURE."violence_assailants", F_CLOSURE."violence_sex", F_CLOSURE."violence_physical", F_CLOSURE."violence_verbal", F_CLOSURE."violence_abuse", F_CLOSURE."container_skip", F_CLOSURE."container_refuse_bin", F_CLOSURE."container_wheelie_bin", F_CLOSURE."container_recycling_container", F_CLOSURE."container_quantity", F_CLOSURE."container_refuse", F_CLOSURE."container_open_ground", F_CLOSURE."container_fly_tipping", F_CLOSURE."container_household_waste", F_CLOSURE."container_other_quantity", F_CLOSURE."vehicle_make", F_CLOSURE."vehicle_model", F_CLOSURE."vehicle_reg", F_CLOSURE."vehicle_year", F_CLOSURE."vehicle_other_info", F_CLOSURE."fs_children", F_CLOSURE."fs_youth", F_CLOSURE."fs_adult", F_CLOSURE."fs_pensionable_age", F_CLOSURE."fs_oven", F_CLOSURE."fs_grill", F_CLOSURE."fs_ring", F_CLOSURE."fs_microwave", F_CLOSURE."fs_pan", F_CLOSURE."fs_frying_pan", F_CLOSURE."fs_chip_pan", F_CLOSURE."fs_fire_plan", F_CLOSURE."fs_plan_carried_out", F_CLOSURE."fs_plan_appropriate", F_CLOSURE."smoke_fitted", F_CLOSURE."smoke_mains", F_CLOSURE."smoke_battery", F_CLOSURE."smoke_working", F_CLOSURE."smoke_operate", F_CLOSURE."smoke_sited_suitable", F_CLOSURE."smoke_further_info", F_CLOSURE."smoke_attempt", F_CLOSURE."smoke_details", F_CLOSURE."brig_cas", F_CLOSURE."brig_res", F_CLOSURE."brig_fatal", F_CLOSURE."civ_cas", F_CLOSURE."civ_res", F_CLOSURE."civ_fatal", F_CLOSURE."signedoff_by", F_CLOSURE."numsmokedet", F_CLOSURE."numheatdet", F_CLOSURE."numfloors", F_CLOSURE."officerincharge",
L_UNWANTED."descr",
L_FA_TYPES."descr",
L_FS_PRIOR_FF."descr",
L_FS_OCCDETS."descr",
L_FAIL_OVERIDE."descr",
L_AFFECTED_AFA."descr",
L_UNWANTED_AFA."descr",
L_ALLEGED_EVENTS."descr",
L_FS_ROOM_OF_ORIG."descr"
FROM
{ oj (((((((((((((((("INCIDENT"."dbo"."I_DETAILS" I_DETAILS INNER JOIN "INCIDENT"."dbo"."L_SUBTYPES" L_SUBTYPES ON
I_DETAILS."subtype" = L_SUBTYPES."code")
INNER JOIN "INCIDENT"."dbo"."L_SS_TYPE" L_SS_TYPE ON
I_DETAILS."ss_type" = L_SS_TYPE."code")
INNER JOIN "INCIDENT"."dbo"."L_SS_SUBTYPE" L_SS_SUBTYPE ON
I_DETAILS."ss_subtype" = L_SS_SUBTYPE."ss_code")
INNER JOIN "INCIDENT"."dbo"."L_CAUSE" L_CAUSE ON
I_DETAILS."cause" = L_CAUSE."code")
INNER JOIN "INCIDENT"."dbo"."L_EXTING_METHOD" L_EXTING_METHOD ON
I_DETAILS."exting_method" = L_EXTING_METHOD."code")
INNER JOIN "INCIDENT"."dbo"."L_CALL_METHOD" L_CALL_METHOD ON
I_DETAILS."call_method" = L_CALL_METHOD."code")
INNER JOIN "INCIDENT"."dbo"."F_CLOSURE" F_CLOSURE ON
I_DETAILS."incno" = F_CLOSURE."incno")
INNER JOIN "INCIDENT"."dbo"."L_UNWANTED" L_UNWANTED ON
I_DETAILS."unwanted_code" = L_UNWANTED."code")
INNER JOIN "INCIDENT"."dbo"."L_FA_TYPES" L_FA_TYPES ON
I_DETAILS."fa_app_code" = L_FA_TYPES."code")
INNER JOIN "INCIDENT"."dbo"."L_MAIN_TYPES" L_MAIN_TYPES ON
I_DETAILS."main_type" = L_MAIN_TYPES."type")
INNER JOIN "INCIDENT"."dbo"."L_FS_OCCDETS" L_FS_OCCDETS ON
F_CLOSURE."fs_details" = L_FS_OCCDETS."code")
INNER JOIN "INCIDENT"."dbo"."L_FAIL_OVERIDE" L_FAIL_OVERIDE ON
F_CLOSURE."fail_overide" = L_FAIL_OVERIDE."code")
INNER JOIN "INCIDENT"."dbo"."L_AFFECTED_AFA" L_AFFECTED_AFA ON
F_CLOSURE."afa_affected" = L_AFFECTED_AFA."code")
INNER JOIN "INCIDENT"."dbo"."L_UNWANTED_AFA" L_UNWANTED_AFA ON
F_CLOSURE."afa_building" = L_UNWANTED_AFA."code")
INNER JOIN "INCIDENT"."dbo"."L_ALLEGED_EVENTS" L_ALLEGED_EVENTS ON
F_CLOSURE."fs_alleged_event" = L_ALLEGED_EVENTS."code")
INNER JOIN "INCIDENT"."dbo"."L_FS_ROOM_OF_ORIG" L_FS_ROOM_OF_ORIG ON
F_CLOSURE."fs_room_of_origin" = L_FS_ROOM_OF_ORIG."code")
INNER JOIN "INCIDENT"."dbo"."L_FS_PRIOR_FF" L_FS_PRIOR_FF ON
F_CLOSURE."fs_prior_ff" = L_FS_PRIOR_FF."code"}
WHERE
F_CLOSURE.incno = I_DETAILS.incno AND I_DETAILS.main_type *= L_MAIN_TYPES.type AND I_DETAILS.subtype *= L_SUBTYPES.code AND I_DETAILS.ss_type *= L_SS_TYPE.code AND I_DETAILS.ss_subtype *= L_SS_SUBTYPE.ss_code AND I_DETAILS.cause *= L_CAUSE.code AND I_DETAILS.exting_method *= L_EXTING_METHOD.code AND I_DETAILS.call_method *= L_CALL_METHOD.code AND F_CLOSURE.fail_overide *= L_FAIL_OVERIDE.code AND F_CLOSURE.fs_details *= L_FS_OCCDETS.code AND F_CLOSURE.fs_alleged_event *= L_ALLEGED_EVENTS.code AND F_CLOSURE.fs_room_of_origin *= L_FS_ROOM_OF_ORIG.code AND F_CLOSURE.fs_prior_ff *= L_FS_PRIOR_FF.code AND F_CLOSURE.afa_building *= L_UNWANTED_AFA.code AND F_CLOSURE.afa_affected *= L_AFFECTED_AFA.code AND I_DETAILS.fa_app_code *= L_FA_TYPES.code AND I_DETAILS.unwanted_code *= L_UNWANTED.code

[essentially, the L_ tables are lookup tables, hence the vast number of out joins]

Is it a syntactical problem?

 

[eo]

Try this reg key:

Windows Registry Editor Vesrion 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Seagate Software\Crystal Reports\Database Options\Outerjoin]

OJSyntax

"SQLServerJoinBuilder" = "sqlsrv32"

- I tried to combine your HKEY path and my eventual solution, as I had this problem on V9, not 8.5 - maybe contact BO Support to find out the exact key to use for V8.5 (when it was still Seagate)
- As always, make sure to make a backup of your reg files before making running these changes!


Etienne Oosthuysen
Hertfordshire, England

 

[mchoss]

I noticed in their doc that they have the database options set under HKEY_CURRENT_USER

I have tried every permutation but still with no joy.

In your example above, what is the 'OJSyntax' entry, is that a key under Outerjoin or a string value under outerjoin with "SQLServerJoinBuilder" = "sqlsrv32" as its value?

For SQL server on 8.5 the 85_left_outer_join doc states that OJSyntax is the join syntax and registry key, the driver for the SQL server odbc connection is sqlsrv32.dll

I am going to write to crystal i think

 

[eo]

I see what you mean. The V9 doc states:

Database type - SQL Server 2000
Join Syntax - OJSyntax
Reg key - SQLServerJoinBuilder

and the V8.5 doc states:

Database type - SQL Server 2000
Join Syntax - OJSyntax
Reg key - OJSyntax

And the txt file also differs slightly

V9:

[HKEY_CURRENT_USER\Software\Crystal Decisions\9.0\Crystal Reports\Database\QueryBuilder\JoinBuilder]
PlusEqual
"OracleJoinBuilder" = "w32btint"

V8.5:

[HKEY_LOCAL_MACHINE\SOFTWARE\Seagate Software\Crystal Reports\Database Options\Outerjoin]
"OJSyntax"="sqlsrv32"

Sorry couldn't help more.


Etienne Oosthuysen
Hertfordshire, England