Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLboom
Yak Posting Veteran
63 Posts |
 Posted - 2004-02-04 : 22:51:26
|
| There's an Oracle query is something like. . .select *from a, b, cwhere a.p = b.q(+) and b.q between c.r(+) and nvl(c.s(+), b.q) andb.t = c.t(+)and other simple joins...Now, the corresponding SQL Server should be ... .select *from a left outer join b on a.p = b.q left outer join c on b.q between c.r and isnull(c.s, b.q)and b.t = c.twhere all the simple clauses...But the two queries do not give similar output. .. .why is it so?. . |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-04 : 23:30:12
|
| Well, are you certain that SQL Server is working with the exact same set of data as the Oracle query is? I know it sounds dumb, but I've wasted a lot of time assuming I had the same data on two different sources only to find out I didn't.I would suggest rewriting the join to put the BETWEEN in the WHERE clause. You also need to be aware that BETWEEN A AND B will be converted internally by SQL Server to >=A AND <=B and will NOT move the arguments around to ensure they are in the correct numeric order. If B is actually less than A, the clause will never evaluate as true. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-02-05 : 08:41:35
|
| show us what the difference is in the output. obviously, use a small sample set of data don't show us 100,000 rows if these statements return that many ...- Jeff |
|
|
|
SQLboom
Yak Posting Veteran
63 Posts |
Posted - 2004-02-09 : 03:05:35
|
| well luckily the problem is resolved.. .. it's some wrong code with Oracle... . .but of course i was curious to know the following . .. .If there's some clause in Oracle likeWHERE A.a BETWEEN B.b1(+) and nvl(B.b2(+), A.a)here we can see that "b" is with the join. Now if that is null, then column "a" which is withour join occurs.. .In SQL Server...FROM A left outer join B ON A.aBETWEEN B.b1 and ISNULL (B.b2, A.a)implies that the join is on complete ISNULL() statement... ..The two definitely give different results "with the same set of data". .. I shall have to prepare a set of data which time available, i would share .. .. |
|
|
|
|
|
|
|
|
joins query