How do I cancel an outer join in SQL Server?

system · June 27, 2003, 8:01pm

I have this query…

SELECT
  a.Id,
  b.Date,
  b.DataSource
FROM
  dbo.FirstTable a,
  dbo.SecondTable b
WHERE
  ( a.Id*=b.Id  )
  AND  (
  a.VigId  IN  (27958, 27956, 27958, 24723, 24722)
and b.DataSource in ('Stupid')
and b.DataSource is not null
  )

which gives me these rows…


ID      Date                   DataSource           
24722	NULL	                   NULL
24723	NULL	                   NULL
27956	2003-07-02 00:00:00	  Stupid
27958	2003-07-01 00:00:00	  Stupid

Note that I said I only wanted datasource = ‘Stupid’. In Oracle, this would have cancelled my outer join and I would have gotten the last two records. This is SQL server so I added ‘and datasource is not null’ but I can’t get rid of the rows created by the outer join. What is the best way to eliminate the top two records? Don’t tell me not to outer join

Cindy Clayton (BOB member since 2002-06-11)

system · June 27, 2003, 8:25pm

Remove *

( a.Id*=b.Id )

from this and check

man4urheart (BOB member since 2003-02-14)

system · June 27, 2003, 8:27pm

But that removes the outer join. :?

The problem is that SQL Server does not treat outer joins properly. When you place a condition on an outer join, the outer join is supposed to be invalid, since the results would be ambiguous.

Perhaps there is an admin setting that one of the DBA’s can use to change this behavior?

Dave

Dave Rathbun (BOB member since 2002-06-06)

system · June 27, 2003, 8:31pm

Note: I am not too familiar with MS SQL Server syntax
Maybe, you can try an inline view along the lines of:

Select * From

(
SELECT a.Id, b.Date, b.DataSource 
FROM dbo.FirstTable a, dbo.SecondTable b 
WHERE 
   a.Id*=b.Id
  AND  a.VigId  IN  (27958, 27956, 27958, 24723, 24722)
  AND b.DataSource in ('Stupid') 
) InlineViewName 

WHERE InlineViewName.DataSource is not null

Andreas (BOB member since 2002-06-20)

system · June 27, 2003, 8:34pm

You answered just in time to save man from a tounge lashing (just teasing you ‘man’. We’ve got to get your real name )

I just heard from someone in the SQL Server group that ‘my problem is that Oracle’s way of doing it it wrong’

Thanks for the confirmation! I didn’t want to waste time!

Cindy Clayton (BOB member since 2002-06-11)

system · June 27, 2003, 8:35pm

Andreas:

Note: I am not too familiar with MS SQL Server syntax
Maybe, you cna try an inline view along the lines of:
Select * From

(
SELECT a.Id, b.Date, b.DataSource 
FROM dbo.FirstTable a,   dbo.SecondTable b 
WHERE 
   a.Id*=b.Id  ) 
  AND  (   a.VigId  IN  (27958, 27956, 27958, 24723, 24722) 
) InlineViewName 

WHERE InlineViewName.DataSource is not null

Something I can easily implement with BO :? Got any ideas?

Cindy Clayton (BOB member since 2002-06-11)

system · June 27, 2003, 9:28pm

My real Name is secret

to know it you have to give me some space in your heart…

man4urheart (BOB member since 2003-02-14)

system · June 28, 2003, 12:16am

Here’s an example. Suppose you have an outer join between Customers and Orders. Not all customers have orders, which means we require an outer join to get all customers in the result set. Suppose you have 10 customers, with 5 having orders. Selecting all customers + orders (if they exist) results in 10 rows of data.

Now suppose you put a condition on order date. Only 3 of the orders match that condition. What do you want to see when that condition is added?

The only correct answer, I submit, is 3 customers. You do not want to see the other customers, because their presense would be ambiguous. Did they show up because they had no orders at all? Or no orders within the date range provided?

For that reason, I believe that SQL Server is incorrect.

Having said that, I went in search of a supporting article to back up my argument. I found this one which interestingly enough goes a step further. It shows that if a condition is in the FROM clause then the rows with missing data are, as expected, missing. But if the condition is part of the JOIN (in the FROM clause with the ANSI standard syntax) then the missing rows do show up. Very interesting. Time for some more testing.

Dave

Dave Rathbun (BOB member since 2002-06-06)

system · June 30, 2003, 2:24am

Good article. So, if you look at his example…

SQL> SELECT c.city_name, c.population, b.business_name
  2  FROM up_city c LEFT OUTER JOIN up_business b
  3  ON c.city_name = b.city_name
  4* WHERE c.population >= 5000;

CITY_NAME             POPULATION BUSINESS_NAME
-------------------- ---------- -------------------------
Ishpeming                  6686 DA Yoopers Tourist Trap
Sault Ste. Marie          16542 Museum Ship Valley Camp

This query works just as expected. Now, let's try that reader's suggestion and move the restriction on population into the FROM clause. By doing that, we explicitly make c.population >= 5000 one of the join conditions. It's one of the join conditions even though it doesn't link columns from two tables. Following are the results:

SQL> SELECT c.city_name, c.population, b.business_name
  2  FROM up_city c LEFT OUTER JOIN up_business b
  3    ON c.city_name = b.city_name
  4*  AND c.population >= 5000;

CITY_NAME            POPULATION BUSINESS_NAME
-------------------- ---------- -------------------------
Munising                   2539
Ishpeming                  6686 DA Yoopers Tourist Trap
Sault Ste. Marie          16542 Museum Ship Valley Camp

Then I should be able to rewrite my sql (which I will do the minute I walk in) as…

SELECT
  a.Id,
  b.Date,
  b.DataSource
FROM
  dbo.FirstTable a LEFT OUTER JOIN dbo.SecondTable b
ON a.Id = b.Id
WHERE  b.DataSource in ('Stupid')

and get the answer I want…only the two ‘Stupid’ rows! . Is that the way you read it?

But, it is irrelevant anyway 'cause BO still generates the ‘*’ syntax :? …unless there is a file I can play with. Am I getting the point? I’ll print off and read the article a couple more times tomorrow. It really is a good explanation and I need to get with the program with regard to the new join syntax for both Oracle and SQL Server.

Cindy Clayton (BOB member since 2002-06-11)

system · June 30, 2003, 8:28am

These problems are the reason why many sites modify their sqlserver .prm files to use ansi standard syntax (FROM X LEFT|RIGHT OUTER JOIN Y etc) rather than the old * syntax. Our SQL Server DBAs just stared at me when they saw BO generated the * syntax by default - I don’t think anybody in the SQL Server world writes outer joins that way. I’ve always been surprised that you have to make a modification to the .prm to get outer joins to behaviour nicely- I would have thought that should be normal BO behaviour. Maybe it’s historical.

If you modify the .prms then BusinessObjects will generate outer joins with the correct behaviour that Dave pointed out. There’s lots of stuff in BOB on modifying the .prm in this way (and for this purpose).

Steve Nicoll (BOB member since 2002-08-16)

system · June 30, 2003, 4:25pm

My DBAs did the same thing. Looked like this right?

Indeed there is. Found tons of stuff searching on ansi and outer and join. Thanks! I also think it is kinda stupid that BO defaults to ‘*’ when it is pretty archaic!

Also found the following…

The query that would actually run with the ‘*’ syntax now tells me…

sigh

Cindy Clayton (BOB member since 2002-06-11)

system · June 30, 2003, 5:36pm

Never mind. There were db changes going on so that new error was unrelated to my issue. Stay tuned…

Cindy Clayton (BOB member since 2002-06-11)

system · June 30, 2003, 6:22pm

OK I’m getting terribly confused and frustrated. I methodically changed my .prm file for SQL Server based on various forum suggestions and came up with quite interesting results. In a nutshell, a prm file that looks like this (the default)…

LEFT_OUTER=$*
RIGHT_OUTER=*$
OUTERJOINS_GENERATION=USUAL

gives this query…

SELECT
  EVDBV30.dbo.VwVigilanceSuspendedLots.VigId,
  EVDBV30.dbo.VwVigilanceLotManagement.SBShipDate,
  EVDBV30.dbo.VwVigilanceLotManagement.DataSource
FROM
  EVDBV30.dbo.VwVigilanceSuspendedLots,
  EVDBV30.dbo.VwVigilanceLotManagement
WHERE
  ( EVDBV30.dbo.VwVigilanceSuspendedLots.VigId*=EVDBV30.dbo.VwVigilanceLotManagement.VigId  )
  AND  (
  EVDBV30.dbo.VwVigilanceSuspendedLots.VigId  IN  (27958, 27956, 27958, 24723, 24722)
  )

which runs fine!

These parameters…

LEFT_OUTER=
RIGHT_OUTER=
OUTERJOINS_GENERATION=ANSI_92
OUTERJOINS_COMPLEX=Y

generate this query…

SELECT
  EVDBV30.dbo.VwVigilanceSuspendedLots.VigId,
  EVDBV30.dbo.VwVigilanceLotManagement.SBShipDate,
  EVDBV30.dbo.VwVigilanceLotManagement.DataSource
FROM
  (EVDBV30.dbo.VwVigilanceSuspendedLots RIGHT OUTER JOIN EVDBV30.dbo.VwVigilanceLotManagement ON (EVDBV30.dbo.VwVigilanceSuspendedLots.VigId=EVDBV30.dbo.VwVigilanceLotManagement.VigId) )
  
WHERE
  ( 
  EVDBV30.dbo.VwVigilanceSuspendedLots.VigId  IN  (27958, 27956, 27958, 24723, 24722)
  )

Which gives this error…

Note the RIGHT OUTER JOIN.

Finally, these parameters…

LEFT_OUTER=
RIGHT_OUTER=
OUTERJOINS_GENERATION=FULL_ODBC
OUTERJOINS_COMPLEX=Y

generate this query…

SELECT
  EVDBV30.dbo.VwVigilanceSuspendedLots.VigId,
  EVDBV30.dbo.VwVigilanceLotManagement.SBShipDate,
  EVDBV30.dbo.VwVigilanceLotManagement.DataSource
FROM
{ oj EVDBV30.dbo.VwVigilanceSuspendedLots LEFT OUTER JOIN EVDBV30.dbo.VwVigilanceLotManagement ON ( EVDBV30.dbo.VwVigilanceSuspendedLots.VigId=EVDBV30.dbo.VwVigilanceLotManagement.VigId )
  }
WHERE
  ( 
  EVDBV30.dbo.VwVigilanceSuspendedLots.VigId  IN  (27958, 27956, 27958, 24723, 24722)
  )

which gives the same error as above. Note the LEFT OUTER JOIN…

Why would an outer join using the * be allowed but outer joins using ANSI_92 or FULL_ODBC be prohibited
Why does the join change from RIGHT OUTER to LEFT OUTER depending on whether I specify ANSI_92 or FULL_ODBC without any changes to which table has the outer join checkbox ticked .

Watch my location. I’ll soon be ‘Hiding in the bathroom reading ANSI SQL for dummies’ Man I love Oracle!

Cindy Clayton (BOB member since 2002-06-11)

system · July 1, 2003, 11:43am

I’m guessing you might have two separate problems here.

Firstly when using ANSI_92, BO seems to reverse the outer join (from the FULL_ODBC/USUAL way round) - why it does this I don’t know. That’s why you’ll need to have the parameter EXT_JOIN_INVERT=YES in the .prm if you use ANSI_92. In Designer, you’ll see the outer join tick box change sides when this clause is included in the .prm (you may need to restart Designer to see the change). You’ll then have to modify the .unv to correct the tick to the right side… that’s bad isn’t it? Actually, now I think about it’s probably much simpler to use FULL_ODBC (and not worry about a EXT_JOIN_INVERT clause). Pity that, because I deployed ANSI_92 here (but then we didn’t have any pre-existing SQL Server universes using outer joins, so it wasn’t too hard).

I wonder if the second problem (the error message) might be a SQL Server thing. Can you run your query via Microsoft’s Query Analyser? What’s the SQL for the view ‘EVDBV30.dbo.VwVigilanceSuspendedLots’? Does it contain outer joins? Like you I’m more at home with Oracle but maybe a search of the Microsoft Knowledge Base might help.

One last thing on the .prm changes - these are significant global changes effecting every SQL Server universe. You’ll need to carefully test every SQL universe you’ve got in the enterprise to make sure you understand the impact before deploying (to everyone, including any webi/bca servers). Wouldn’t it have been nice if BO had used FULL_ODBC/ANSI_92 by default.

Steve Nicoll (BOB member since 2002-08-16)

system · July 1, 2003, 11:46am

Cindy, out of curiousity: Why are you using ODBC with MS SQL Server and not OLE DB?

Andreas (BOB member since 2002-06-20)

system · July 1, 2003, 12:31pm

Ok here goes . Cause I have virtually no idea what OLE DB is or how to implement it, and haven’t had the time to investigate . Spank me please. I’ve been busy. It is basically ME here and I also have a life Hope’s 5th birthday is tomorrow BTW :-D.

Spoon feed me some links if you wish and I’ll read them the week of the 14. The family is taking a little beach vacation to the beach but they’re leaving me at home this trip. I’m going to try and wrap up all the FAQs, clean up my audit universe to share with the group, study up on the new join syntax for SQL Server and Oracle 9i, paint the laundry room, and read ‘Game of Thrones’

Cindy Clayton (BOB member since 2002-06-11)

system · July 1, 2003, 12:36pm

Point taken. FULL_ODBC sounds like a plan.

You’re right. It is a SQL Server thing. The first thing I did was try and run the same SQL in Query Analyzer and, unsuprisingly, got the same answer. . Evidently I cannot use an outer join between two views if one or more of them already contains an outer join. I’m not liking SQL Server at all at this point!

Will do!

Quite!

Thanks so much Steve!

Cindy Clayton (BOB member since 2002-06-11)

system · July 1, 2003, 12:47pm

Here are a few resources for MS SQL Server:

BOB - Using OLEDB instead of ODBC for connecting to SQL Server

Microsoft - Configuring OLE DB Clients

Microsoft - Managing SQL Server Clients

Microsoft - SQL Server

Microsoft - MS SQL Server Books Online

SQL Server 2000 SP3 - Books Online

Andreas (BOB member since 2002-06-20)

system · July 1, 2003, 2:25pm

Apart from any performance gain, one good thing about OleDB in a 3 tier environment is that you don’t need to declare anything like the ODBC system DSN’s on your servers. Just define the new OleDB connection in your repository and it works. The draw back at the moment is that 6.0 doesn’t support OleDB.

Steve Nicoll (BOB member since 2002-08-16)

system · July 30, 2003, 1:05pm

Thanks for your links Andreas! They were very helpful

Cindy Clayton (BOB member since 2002-06-11)