funny results using view to oracle ...
-
getting unexpected results when using a view with data from a linked oracle DB. Here's the case. I created a view in
SQL and joined to the linked oracle db. the view works fine when i
select all data from the view, however when i add a where clause such as "select * from my_view where col1 = 'abc'"
I still get all the rows in the table, however only the row(s) that meet the where condition have values,
all other rows have NULL values in the column. it's not a problem, I just wasn't expecting it. What going on???
Curtis Smith
SQL Server DBA
Well in worked in Theory ... -
This was removed by the editor as SPAM
-
Do you have outer joins in the view? In some circumstances Oracle and SQL give different answers to queries involving outer joins.
-
I do have outer joins, and I proved that to be the case. I just did not understand why.
Something else I discovered was that you cannot use a SP to load a temp table of the SP gets data from Oracle.
Curtis Smith
SQL Server DBA
Well in worked in Theory ... -
It depends on what order you apply the tests in the where clause and the outer join.
So if you join table a with a left join to table b - you want all of table a and what ever matches in b. Apply a test criteria to one of the fields in b.
Case 1 - If you apply the test and then join you get everything in a plus the few matching records from b where the test is true. So you get lots of nulls for the records in a where there is no b.
Case 2 - The other way around, join first and then test on b you will get a much shorter result set. Only items with an equal join in a and b where b passes the test. (So the outer join was pointless.)
SQL Server (if you use the old style syntax - joins in the where clause) does case 2. Oracle, using exactly the same syntax and database uses case 1. So in your example you gets lots of rows with nulls. So in a development environment where you are using both databases there's something to watch out for!
To add to the confusion if you write a query for SQL server using the older syntax and the new ANSI syntax (using the words left join etc) you can get different results. It depends where you put the test. If you put it in the from clause then it happens before the join. If you put it in the where clause it happens after. So one of the big advantages of ANSI SQL is that you can control more precisely what happens.
Unfortunately Oracle doesn't support this!
Hope that helps!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply