Hi,
Can you give the equivalent query for the following Oracle query :
SELECT col2, col3 FROM submission,
lkup so
WHERE so.txt (+) = TRIM(col2)
AND so.pid (+) = 'SO'
AND so.cde (+) = 'RW'
In the above query a constant value is used in JOIN, so I am not aware of how to use the JOIN clause for the above constant value ( 'SO' , 'RW' )
Upto my knowledge about JOIN query equivalents :
Oracle query :-
select *from test1 a, test2 b where a.sno(+) = b.col
Equivalent SQL Server query :-
select *from test1 a JOIN test2 b on a.sno = b.col
Hope the above is correct. Here my doubt is, if in the above query, b.col is replaced by a constant value say 'abcd' , then what will be the table name used in right of the JOIN clause. Or any other equivalent for this is available ?
Eg :-
Oracle query :-
select *from test1 a, test2 b where a.sno(+) = 'abcd'
Thanks,
SamHi Sam,
the syntax used by Oracle with "(+)" in the WHERE-clause is equivalent to an OUTER JOIN in SQL Server and DB2.
The example you mentioned should be a LEFT OUTER JOIN, but I'm always a bit confused when using OUTER JOINS in Oracle, so I may be wrong :-)
You can lookup Oracle syntax at http://tahiti.oracle.com
Carsten|||Hi, OK, it is RIGHT JOIN, but my requirement is different, Please go through my posting once again. Thanks for your information.|||SELECT col2, col3 FROM submission,
lkup so
WHERE so.txt (+) = TRIM(col2)
AND so.pid (+) = 'SO'
AND so.cde (+) = 'RW'
SELECT col2, col3 FROM submission
Right Outer Join lkup so
On so.txt = LTRIM(RTRIM(col2)) AND so.pid = 'SO' AND so.cde = 'RW'
Showing posts with label trim. Show all posts
Showing posts with label trim. Show all posts
Friday, February 24, 2012
JOIN queries
Hi,
Can you give the equivalent query for the following Oracle query :
SELECT col2, col3 FROM submission,
lkup so
WHERE so.txt (+) = TRIM(col2)
AND so.pid (+) = 'SO'
AND so.cde (+) = 'RW'
In the above query a constant value is used in JOIN, so I am not aware of how to use the JOIN clause for the above constant value ( 'SO' , 'RW' )
Upto my knowledge about JOIN query equivalents :
Oracle query :-
select *from test1 a, test2 b where a.sno(+) = b.col
Equivalent SQL Server query :-
select *from test1 a RIGHT JOIN test2 b on a.sno = b.col
Hope the above is correct. Here my doubt is, if in the above query, b.col is replaced by a constant value say 'abcd' , then what will be the table name used in right of the JOIN clause. Or any other equivalent for this is available ?
Eg :-
Oracle query :-
select *from test1 a, test2 b where a.sno(+) = 'abcd'
Thanks,
SamSam,
The(+) is an outter join right?
How can you do an outter join to a literal?
Can you give the equivalent query for the following Oracle query :
SELECT col2, col3 FROM submission,
lkup so
WHERE so.txt (+) = TRIM(col2)
AND so.pid (+) = 'SO'
AND so.cde (+) = 'RW'
In the above query a constant value is used in JOIN, so I am not aware of how to use the JOIN clause for the above constant value ( 'SO' , 'RW' )
Upto my knowledge about JOIN query equivalents :
Oracle query :-
select *from test1 a, test2 b where a.sno(+) = b.col
Equivalent SQL Server query :-
select *from test1 a RIGHT JOIN test2 b on a.sno = b.col
Hope the above is correct. Here my doubt is, if in the above query, b.col is replaced by a constant value say 'abcd' , then what will be the table name used in right of the JOIN clause. Or any other equivalent for this is available ?
Eg :-
Oracle query :-
select *from test1 a, test2 b where a.sno(+) = 'abcd'
Thanks,
SamSam,
The(+) is an outter join right?
How can you do an outter join to a literal?
Subscribe to:
Posts (Atom)