Garadin (10/24/2008)

---

So... to summarize:

Performing an operation that the optimizer HAS to take first, such as selecting all the data into a temp table or table variable would have avoided...

Garadin (10/24/2008)

---

rbarryyoung (10/24/2008)

---

The reason that this does not work is because the order that clauses appear in the WHERE section does NOT determine their order of evaluation at execution time....

adams.squared (10/24/2008)

---

If I rewrite it to the following:

declare @wh varchar(2)

set @wh='10';

select obitno, oaorsc

from

(select obitno, oaorsc

from hsdet inner join hshed on obhssq=oahssq

where oainvd>20050100) as DerivedHistTable

where convert(int,oaorsc)=@wh

I still get a conversion...

Using a Tally table/CTE probably. How high do you want you Division numbers?

Another note: Although AND branches are not order sensitive in SQL (thus leading to this problem), CTE clauses are order sensitive (because later clauses can only refer to earlier...

select obitno, oaorsc

from hsdet inner join hshed on obhssq=oahssq

where oainvd>20050100

and convert(int,oaorsc)=@wh

The reason that this does not work is because the order that clauses appear in the WHERE section does NOT...

Seggerman (10/24/2008)

---

A lakh (also written lac) is a unit in the Indian numbering system equal to one hundred thousand (100,000; 105). It is widely used both in official and other...

Really?!? That is extremely surprising to me, I cannot imagine why it wouldn't use the new index?

Can you try the following version of Query1 and post the Actual Execution...

Well, the syntax was fine, it's the semantics that were wrong... 🙂

There were a lot of security changes in SQL 2005.

Right, sorry:

SELECT COALESCE(A.Value, B.Value)

FROM TableA A

FULL OUTER JOIN TableB B ON Source.ID = B.ID

I tested the syntax this time 🙂

Tim Benninghoff (10/24/2008)

---

3) I probably wasn't very clear in the last paragraph of my original post. I get it to work by adding the user to database B...

Tim Benninghoff (10/24/2008)[hr1) If I grant connect to a user in database B mapped to the login, I don't seem to need to have the originating database marked as...

The easy way to do this is through Schemas: Put the tables in one schema (DAT) and everything else in another schema (EXE) that has access rights to DAT.

Then...

OK, Garadin just made a small mistake, which is easily fixed:

SELECT COALESCE(A.Value, B.Value)

FROM TableA A ON Source.ID = A.ID

FULL OUTER JOIN TableB B ON Source.ID = B.ID