I just went back and re-read this thread, and realized that we might all be overthinking this one. If all you want to do...

Ahhhh. I get it. I QUIT... but I get it. (I don't even understand that commercial and I just used it. :ermm: )

So... to summarize:...

rbarryyoung (10/24/2008)

---

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...

Yeah, and it'll still bug me until I figure out why it's not working. I thought it might have been similar to the tally issue from yesterday, but so...

Lynn Pettis

---

Then I'd try this:

select

obitno,

oaorsc

from

hsdet

inner join hshed

...

Can you do this?

DECLARE @wh char(2)

SET @wh = '1'

SET @WH = CASE WHEN LEN(@WH) = 1 THEN '0' + @WH ELSE @WH END

select obitno, oaorsc

from hsdet inner join hshed on...

What are the results of this:

SELECT DISTINCT oaorsc

FROM hsdet inner join hshed on obhssq=oahssq

where oainvd>20050100

If the list is reasonably short, post the whole thing.

Lynn Pettis (10/24/2008)

---

Well, after fixing a couple of inconsistencies in the code (the system I am using is case sensitive so CoPay <> Copay, DateofBirth <> DateOfBirth, etc) I get...

Did you try adding the AND ISNUMERIC(oaorsc) = 1 to your CTE?

There are many ways of accomplishing this, the key is finding the most efficient.

eg. Selecting only numeric rows...

Nah, I just abuse my poor refresh button. (And have RSS feeds to Trillian) 😎

I wasn't saying to put ISNUMERIC into your original query, only to check that all data above that breakpoint of 20050100 WAS numeric. (IE. Run that select and see...

This isn't necessarily the problem, but for arguments sake, run this:

SELECT *

FROM hsdet inner join hshed on obhssq=oahssq

where oainvd>20050100 AND NOT ISNUMERIC(oaorsc) = 1

Did you test that Source wasn't a table? :hehe:

Do you mean this?

SELECT COALESCE(A.ID, B.ID)

FROM TableA A

FULL OUTER JOIN TableB B ON A.ID...

My original post assumed a starting table, which is what "Source" intended to represent. It wasn't intended to be literal ;).

P.S. Barry, that Select is messed up.

Jeff Moden (10/24/2008)

---

ggraber (10/24/2008)

---

Jeff Moden (10/24/2008)

---

Heh... that's where I was going...