Interesting and makes sense.

That also answers the 2nd issue. In the case of the 2nd query, ssn is in the staging table and comes back with 12,000 records. ...

Not sure what is sparse about my question.

I am just trying to take an image from one record and move it to another. I need to script it as...

I was able to resolve the issue by killing all queries open to the database and shutting down SSMS, when I came back - it worked fine. Before just...

You're right - that was it.

Didn't see the order by for some reason.

Thanks,

Tom

That was what I figured - that I set the order in the "IN" clause. But I wasn't sure if that was how it worked.

But the other issue is...

It may be in this case, but I could just as easily have created a temp file in the query and have it fail. Then it wouldn't be a...

Hi Kingston,

Actually, there was a reason for it having to be in the where clause.

I was actually using a table variable for filtering based on a parameter that had a...

That was it.

Not sure why I didn't realize this.

Thanks,

Tom

I understand.

I am still a little confused on the use for the CROSS APPLY, but it seems to work.

Which would be the better one to use the CROSS APPLY or...

That make sense.

So you can't really do correlated subqueries, I assume.

Which would be better - the normal join or the CROSS APPLY?

Thanks,

Tom

Here is a very rudimentary set of tables that has just the right amount of records to see the issue.

The first and last query work with this and the 2nd...

Good point.

MoneyTypeNumericType

2949.002949.852507

Thanks,

Tom

It apparently was working.

It was part of another query that removed rows where BalanceDue not null.

Thanks,

Tom

The case statement is part of the result set from the SELECT.

Not sure why, this doesn't work:

SELECT CASE WHEN TypeId = 8

THEN ISNULL(( SELECT Balance

...

I had just realized that after I posted it and was just going to respond to it.

Little brain fade there.

Thanks,

Tom