MS SQL Server New Users - error 4425 why ?

Author

error 4425 why ?

Adrian Parker

2005-12-15, 9:23 am

Can someone please explain why you get error 4425 when joining an already
joined table to another table ?

/* This query works as expected */
SELECT t1.somedata,
(SELECT somedata
FROM table3 t3
WHERE t3.key_ref = t2.key_ref)
FROM table1 t1,
table2 t2
WHERE t1.key_ref = t2.key_ref

/* The query below fails with
Server: Msg 4425, Level 16, State 1, Line 1
Cannot specify outer join operators in a query containing joined tables.
View or function 'table3' contains outer join operators.
*/
SELECT t1.somedata,
(SELECT somedata
FROM table3 t3
WHERE t3.key_ref = t2.key_ref)
FROM table1 t1 join table2 t2 on t1.key_ref = t2.key_ref

Mike Hodgson

2005-12-15, 8:24 pm

Is table3 really a table or is it a view in disguise (or even a
table-valued function)? And if it is a view (or table-valued function),
does it have a join in it using the old-style non-ANSI join syntax?

--
*mike hodgson*
blog: http://sqlnerd.blogspot.com

Adrian Parker wrote:

>Can someone please explain why you get error 4425 when joining an already
>joined table to another table ?
>
>/* This query works as expected */
>SELECT t1.somedata,
> (SELECT somedata
> FROM table3 t3
> WHERE t3.key_ref = t2.key_ref)
> FROM table1 t1,
> table2 t2
> WHERE t1.key_ref = t2.key_ref
>
>
>/* The query below fails with
>Server: Msg 4425, Level 16, State 1, Line 1
>Cannot specify outer join operators in a query containing joined tables.
>View or function 'table3' contains outer join operators.
>*/
>SELECT t1.somedata,
> (SELECT somedata
> FROM table3 t3
> WHERE t3.key_ref = t2.key_ref)
> FROM table1 t1 join table2 t2 on t1.key_ref = t2.key_ref
>
>
>
>
>

Mike Hodgson

2005-12-15, 8:24 pm

From memory, you cannot mix-n-match join syntaxes within a query (at
least not where the join is an outer join so that it uses the old '*='
or '=*' operators). [I could be wrong about that, I didn't confirm
against any official Microsoft documentation, but buried somewhere in
the deep recesses of my warped mind is that little snippet of info.]

For example, try this batch against the AdventureWorks DB (if you're
running SQL 2005, like in my example, you'll need to change to 80 DB
compatibility mode because the old outer join operators aren't allowed
in 90 compatibility mode - just don't forget to change it back again
afterwards):

use master
go

-- Switch to SQL2000 mode so *= and =* are allowed
exec dbo.sp_dbcmptlevel @dbname=N'AdventureW
orks', @new_cmptlevel=80
go

use AdventureWorks
go

-- This is good - all using SQL-92 join syntax
select c.*,
(select v.IS_UPDATABLE* from INFORMATION_SCHEMA.VIEWS v
** **left join INFORMATION_SCHEMA.TABLES t2
** on v.TABLE_NAME = t2.TABLE_NAME
** and v.TABLE_SCHEMA = t2.TABLE_SCHEMA*
where c.TABLE_NAME = t2.TABLE_NAME
and c.TABLE_SCHEMA = t2.TABLE_SCHEMA) as & #91;MyCorrelatedSubq
uery]
from INFORMATION_SCHEMA.TABLES t1
inner join INFORMATION_SCHEMA.COLUMNS c
on c.TABLE_NAME = t1.TABLE_NAME
and c.TABLE_SCHEMA = t1.TABLE_SCHEMA
where t1.TABLE_SCHEMA = 'Sales'
and t1.TABLE_NAME = 'vIndividualCustomer
'
go

-- This fails - mixes SQL-92 and old non-ANSI outer join operators
select c.*,
(select v.IS_UPDATABLE* from INFORMATION_SCHEMA.VIEWS v,
INFORMATION_SCHEMA.TABLES t2*
where c.TABLE_NAME = t2.TABLE_NAME and c.TABLE_SCHEMA =
t2.TABLE_SCHEMA
*and v.TABLE_NAME *= t2.TABLE_NAME and v.TABLE_SCHEMA *=
t2.TABLE_SCHEMA*) as & #91;MyCorrelatedSubq
uery]
from INFORMATION_SCHEMA.TABLES t1
inner join INFORMATION_SCHEMA.COLUMNS c
on c.TABLE_NAME = t1.TABLE_NAME
and c.TABLE_SCHEMA = t1.TABLE_SCHEMA
where t1.TABLE_SCHEMA = 'Sales'
and t1.TABLE_NAME = 'vIndividualCustomer
'

It's an absurd example (why would you want to do this?) but it was a
quick and easy one to come up with; you'll see the first SELECT query
works (because it's all using SQL-92 joins) but the second SELECT throws
the error that you saw (just because I changed the outer join in the
subquery to use the old non-ANSI left outer join operator (*=).

Bottom line, make sure you're consistent. And better yet, find all
those old outer join queries in your code and change them to use the
standard SQL-92 join syntax (although that's a long term approach, but
the best approach).

Hope this helps.

--
*mike hodgson*
blog: http://sqlnerd.blogspot.com

Mike Hodgson wrote:
[color=darkred]
> Is table3 really a table or is it a view in disguise (or even a
> table-valued function)? And if it is a view (or table-valued
> function), does it have a join in it using the old-style non-ANSI join
> syntax?
>
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
>
>
> Adrian Parker wrote:
>

Adrian Parker

2005-12-16, 3:23 am

Hi Mike,

Nope, these are 3 normal tables, they're just using a join, not an outer join, so there would always be rows returned from the joined table.. As far as I can tell, there's no reason for this error.

Thanks
Adrian
"Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message news:OeHeAFdAGHA.3968@TK2MSFTNGP10.phx.gbl...
From memory, you cannot mix-n-match join syntaxes within a query (at least not where the join is an outer join so that it uses the old '*=' or '=*' operators). [I could be wrong about that, I didn't confirm against any official Microsoft documentation, but buried somewhere in the deep recesses of my warped mind is that little snippet of info.]

For example, try this batch against the AdventureWorks DB (if you're running SQL 2005, like in my example, you'll need to change to 80 DB compatibility mode because the old outer join operators aren't allowed in 90 compatibility mode - just don't forget to change it back again afterwards):

use master
go

-- Switch to SQL2000 mode so *= and =* are allowed
exec dbo.sp_dbcmptlevel @dbname=N'AdventureW
orks', @new_cmptlevel=80
go

use AdventureWorks
go

-- This is good - all using SQL-92 join syntax
select c.*,
(select v.IS_UPDATABLE from INFORMATION_SCHEMA.VIEWS v
left join INFORMATION_SCHEMA.TABLES t2
on v.TABLE_NAME = t2.TABLE_NAME
and v.TABLE_SCHEMA = t2.TABLE_SCHEMA
where c.TABLE_NAME = t2.TABLE_NAME
and c.TABLE_SCHEMA = t2.TABLE_SCHEMA) as & #91;MyCorrelatedSubq
uery]
from INFORMATION_SCHEMA.TABLES t1
inner join INFORMATION_SCHEMA.COLUMNS c
on c.TABLE_NAME = t1.TABLE_NAME
and c.TABLE_SCHEMA = t1.TABLE_SCHEMA
where t1.TABLE_SCHEMA = 'Sales'
and t1.TABLE_NAME = 'vIndividualCustomer
'
go

-- This fails - mixes SQL-92 and old non-ANSI outer join operators
select c.*,
(select v.IS_UPDATABLE from INFORMATION_SCHEMA.VIEWS v, INFORMATION_SCHEMA.TABLES t2
where c.TABLE_NAME = t2.TABLE_NAME and c.TABLE_SCHEMA = t2.TABLE_SCHEMA
and v.TABLE_NAME *= t2.TABLE_NAME and v.TABLE_SCHEMA *= t2.TABLE_SCHEMA) as & #91;MyCorrelatedSubq
uery]
from INFORMATION_SCHEMA.TABLES t1
inner join INFORMATION_SCHEMA.COLUMNS c
on c.TABLE_NAME = t1.TABLE_NAME
and c.TABLE_SCHEMA = t1.TABLE_SCHEMA
where t1.TABLE_SCHEMA = 'Sales'
and t1.TABLE_NAME = 'vIndividualCustomer
'

It's an absurd example (why would you want to do this?) but it was a quick and easy one to come up with; you'll see the first SELECT query works (because it's all using SQL-92 joins) but the second SELECT throws the error that you saw (just because I changed the outer join in the subquery to use the old non-ANSI left outer join operator (*=).

Bottom line, make sure you're consistent. And better yet, find all those old outer join queries in your code and change them to use the standard SQL-92 join syntax (although that's a long term approach, but the best approach).

Hope this helps.

--
mike hodgson
blog: http://sqlnerd.blogspot.com

Mike Hodgson wrote:
Is table3 really a table or is it a view in disguise (or even a table-valued function)? And if it is a view (or table-valued function), does it have a join in it using the old-style non-ANSI join syntax?

--
mike hodgson
blog: http://sqlnerd.blogspot.com

Adrian Parker wrote:
Can someone please explain why you get error 4425 when joining an already
joined table to another table ?

/* This query works as expected */
SELECT t1.somedata,
(SELECT somedata
FROM table3 t3
WHERE t3.key_ref = t2.key_ref)
FROM table1 t1,
table2 t2
WHERE t1.key_ref = t2.key_ref

/* The query below fails with
Server: Msg 4425, Level 16, State 1, Line 1
Cannot specify outer join operators in a query containing joined tables.
View or function 'table3' contains outer join operators.
*/
SELECT t1.somedata,
(SELECT somedata
FROM table3 t3
WHERE t3.key_ref = t2.key_ref)
FROM table1 t1 join table2 t2 on t1.key_ref = t2.key_ref

Sponsored Links