outer join in where clause

Hi all,

I'v a Oracle query which I want to copy in SQL Server.
the query in Oracle is as follows
select *
from table1 a
, table2 b
where a.code(+) = b.code
and a.property(+)='TEST'

The query in SQL Server
select *
from table1 right outer join table2 on table1.code = table2.code
where a.property = 'TEST' (here I don't know what to put)

=*, a.property(+) doesn't work

Thanx
LambikI think you just have to add an AND to your ON clause. Try this (not tested, but recollected vaguely from something I tried once before):

select *
from table1 a right outer join table2 b on
a.code = b.code and
a.property = 'TEST'

Looking at it carefully, I think you might have meant to put LEFT OUTER JOIN, but I'm not positive. I always get confused with the Oracle notation. In any case, it should be a simple fix.

Regards,

hmscott

Originally posted by Lambik
Hi all,

I'v a Oracle query which I want to copy in SQL Server.
the query in Oracle is as follows
select *
from table1 a
, table2 b
where a.code(+) = b.code
and a.property(+)='TEST'

The query in SQL Server
select *
from table1 right outer join table2 on table1.code = table2.code
where a.property = 'TEST' (here I don't know what to put)

=*, a.property(+) doesn't work

Thanx
Lambik|||In the original query,
I doubt the "(+)" in "a.property(+)='TEST'" is necessary or even semantically correct? If you remove the it from the query and the
results are still correct, then you can rewrite the query in SQL Server as

Select *
from table1 a, table2 b
where a.code =* b.code
and a.property ='TEST'|||Hi there!

The notation "(+)" defines the outer join in Oracle. So, if you want to have the same results in Oracle and SQL Server you have to convert the "(+)" to an outer join.

Greetings,
Carsten|||where a.code(+) = b.code
and a.property(+)='TEST'

I think what Shianmiin meant was that the both of the (+) notations in this WHERE clause refer to the same table. The first will automatically bring back all non-matching rows on table a making the second one redundant.

Our eal copy has expired

Was under the impression reporting services was free to a licensed SQL server.
Downloaded the eval copy, great product, now a need a licensed copy how do I
go about getting a licensed copy?
Is there costs involved if so I'll need a product #.
Were using SQL 2k
MikeOk, It took a while but it looks like the pages are back up. You can look
here for information about RS 2000:
http://www.microsoft.com/sql/prodinfo/previousversions/default.mspx
http://www.microsoft.com/sql/technologies/reporting/retailfulfillment.mspx
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:910F09A9-CFE9-4C98-8D7C-A353927AFA0E@.microsoft.com...
> Was under the impression reporting services was free to a licensed SQL
> server.
> Downloaded the eval copy, great product, now a need a licensed copy how do
> I
> go about getting a licensed copy?
> Is there costs involved if so I'll need a product #.
> Were using SQL 2k
> Mike

osql login with an instance

Our software installs a copy of MSDE 2000 on the clients computer during
install. On several of the computers I can not log in to the server via
osql (or my software).
The problem seems to be referencing the instance. The instance is {computer
name}\RR, so for example the guy I currently have trouble with his computer
name is AUTOMGR so I should be able to log in with:
OSQL -S AUTOMGR\RR -E
but when I try it returns:
Login failed for user 'AUTOMGR\Greg'.
Same if I try -U sa -P XXXXXX instead of -E
Where Greg I think is his login name.
So it ignores my instance name and uses Greg, I have this happening with
several of my customers.
Does anyone have any ideas?
Thanks in advance,
Rob
That was the whole error, I copied and pasted the screen when it happened,
this is from that session:
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Greg>hostname
AUTOMGR
C:\Documents and Settings\Greg>osql -S automgr\rr -E
Login failed for user 'AUTOMGR\Greg'.
C:\Documents and Settings\Greg>
RR should be our instance. The instance looks to be installed when I look
at the folders.
If this is not enough info, then I will need to wait until another user
reports it and I can investigate further.
If that is so, do you have an things you think I should look at?
e.g. How the registry looks, folder structure, is it in add and remove
programs, etc.
Thanks,
Rob
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:4vvr0mF1e0dvpU1@.mid.individual.net...
> hi,
> Rob wrote:
> 'AUTOMGR\Greg' is not related to the istance (default or named) but
> actually is the WinNT login name..
> can you please provide the "full" exception message?
> thank you
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
> -- remove DMO to reply
>
|||I will check in to this. thanks for you help.
BTW- I really like your DbaMgr2K; thanks.
-Rob
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:50028gF1e4bv2U1@.mid.individual.net...
> hi,
> Rob wrote:
> ok... by default MSDE setup generates a group login for
> Bulitin\Administrators, so that all local WinNT login members of that
> group are allowed to connect to the installed instance.. but other kind of
> WinNT logins are not granted login permissions as well... is "Greg" member
> of that WinNT group?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
> -- remove DMO to reply
>