Using SQL server full outer joins

[rwwlcc]

I'm an oracle developer attempting to create a BObjects universe against a SQL server database for the first time. I've hit a situation where I think I need to use the SQL Server 'FULL OUTER JOIN' facility, ie, a parent record with codes not used on any child record and the child record foreign key being non-mandatory.

Is there a way of describing this relationship in BObjects Designer?

 

[dowlingc]

I do not believe it is possible to do this in designer. You could do it using free-hand sql or by modifying the data provider sql in the report.

 

[dlgems]

Response # 1 may be correct, but Designer does allow you to specify outer joins. On the tool bar click Insert, Joins. This will display the Edit Join screen. Just below the list of tables 1 & 2 there is a box "Outer Join" that can be checked. I resently used this and it seemed to work. Good Luck!

 

[DugzDMan]

There is an outer join check box in the Edit Join dialog box. The only problem here is that it will not allow you to create a full outer join. You can select the one on the left or right, but not both.

I've recently had to do the same thing. I ended up creating my report based on my universe, then realized that I needed a full outer join. You can do this if you have the same problem. Just select the items you want in your query, then click on the SQL button. Edit the query to include the full outer joins on the items you need to join on. Be sure to select Do Net Generate SQL before running check box. If you don't do this, it automatically generates new SQL when you close the SQL window. Also, as you add or delete items BO will generate the normal SQL. So, you will need to make sure you change the SQL again. If you forgot to copy the SQL, just cancel out and go back in.

Click on run and you're good to go.

Later on, if you need to add or remove items from your query, make sure you go in and copy the SQL before adding or deleting any items. Close the SQL window and then add or delete your items. Once that is done, go back in and paste the old SQL code back in and change it to match the items you have in the query window. If you don't change the SQL, you will get an error saying the the query doens't match the result set or something similar.

After all this, if you haven't already created your query and report, then it would probably just be easier to create it using free hand SQL

 

[skrandel]

You also need to edit your SQLServen.prm and make the changes which will put the joins in the FROM clause.

OUTERJOINS_GENERATION=FULL_ODBC
OUTERJOINS_COMPLEX=Y

Don't forget to copy this file to EVERY server and desktop that will be running BO.

Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com