Hi,
I was wondering if it was possible to change BO so that the SQL statements it generates in reporter had the joins in the Where clause as opposed to the From clause.
Thanks!
BatCat
BatCat 
(BOB member since 2004-05-18)
You might have to switch ANSI off in your *.PRM files.
This would be not on a report by report basis, but a global change.
Andreas 
(BOB member since 2002-06-20)
Hi Andreas,
Thanks very much for replying.
I thought I had to set ANSI to yes though so I could use outer joins in SQL Server
Do I?
Thanks,
BatCat
BatCat 
(BOB member since 2004-05-18)
Just out of interest - as everyone else seems to be doing it the other way, mind if I ask why you need to do this?
Paul Williams (BOB member since 2002-07-10)
We have a piece of SQL that works perfectly when we have the joins in the where clause, but it work doesn’t when the joins are in the From clause because we’re using additional conditions.
The latest in my continuing battle is here
missing data / outer join (last post)
Thanks,
BatCat
BatCat (BOB member since 2004-05-18)
And instead of spawning new threads you might want to keep it all in one thread 
Andreas 
(BOB member since 2002-06-20)
Andreas,
To be fair, I tried to reuse an old related thread for my first query and this thread was a separate question which only became related when Paul asked why.
I thought this board wanted to encourage discussion?
Thanks,
BatCat
BatCat (BOB member since 2004-05-18)
Of course discussion is encouraged, just try to keep things together in one thread if they are closely related. A moderator can always split a topic if called for, but cannot merge topics.
Andreas (BOB member since 2002-06-20)
Batcat,
I did it the other way by deleting
left outer= *$
right outer = *$
and enabling ANSI-92 in the prm file.
I belief if you disable ANSI-92 and include the two lines in your prm file, the join would be in WHere clause. But the only problem would be having ‘*’ syntax for the outer joins.
I am not sure if this would be right for your scenario, but you can try.
kashif
Kashif Saeed 
(BOB member since 2004-06-02)
Hi Kashif,
Thanks very much for your reply, I really appreciate it.
I did it that way too and it seems to be working well, except for the condition objects (sigh).
So it looks like the only way to move the join statements would then disable their ability to work with SQL Server?
Thanks!
BatCat
BatCat (BOB member since 2004-05-18)
Were you able to get this working for Webi reports? I have done the same thing and have had no success.
billbejeck (BOB member since 2004-06-03)
Ok, all you have to do is make sure you PRM file has these lines this way.
left outer= *$
right outer = *$
OUTERJOINS_GENERATION=USUALThat will put your joins in the where clause. This is the default setup for sql server. You can still have outer joins, you just cant have multiple outer joins to one table or daisy chain outer joins. Now, I have to say, if you are getting different results from having the joins in the From clause, you have something wrong in your universe. There is no reason to have that change unless your joins are incorrect or your criteria is different.
[edit]You have to use the sql server connectivity, I read your other thread, you cant do this with generic odbc.
Scott Bowers 
(BOB member since 2002-09-30)
Also keep in mind that when the SQL parameters are switched from ANSI-92 to USUAL and visa-versa, the universe needs to switch direction of the outer joins in the schema. Otherwise, errors like “inner member of outer join” may result.
Regarding WebI, the SQL will not be USUAL but ANSI-92 unless you have the WebI server .prm file changed as well.
I have dealt with this issue many times before. I do not like ANSI-92 myself, but found you can add extra conditions in the FROM clause in the universe if you include them in the join statement. For example:
The result is that the table on the outside is limited but the row providing table still gives the results.
I may have got the example wrong, but the concept works.
n5xjb 
(BOB member since 2003-09-15)