Bit of an obscure one here, so please bear with me. I have two copies
of a database which should be identical. Both have a complex view
which is identical. I can open the views and the data is as expected
and match. I can query it in several ways as detailed below. The 5th
version of the simple query below based on the second copy of the view
fails, but works under the first copy.
/*1 Statement below works*/
SELECT *
FROM AgentHierarchy
WHERE AdviserId = 6069819
/*2 Statement below works*/
SELECT *, AH.AdviserLastName, AH.AdviserFirstName
FROM AgentHierarchy AH
WHERE AdviserId = 6069819
/*3 Statement below works*/
SELECT *, AH.AdviserLastName + ', '+ AH.AdviserFirstName
FROM AgentHierarchy AH
WHERE AdviserId = 6069819
/*4 Statement below works*/
SELECT AH.AdviserLastName + ', '+ AH.AdviserFirstName
FROM AgentHierarchy AH
/*5 Statement below fails*/
SELECT AH.AdviserLastName + ', '+ AH.AdviserFirstName
FROM AgentHierarchy AH
WHERE AdviserId = 6069819
The error I get is to do with conversion of data within the view. It's
a little complex, but the view works fine. It looks to me like when I
run the 5th statement above, it re-runs the view and then finds an
error.
So, I took the complex view and ran that with the data output into a
temporary table with the queries above run against that, and it works
fine. The problem is that the statement I need is based around the 5th
one above (part of an update statement).
I'm struggling to understand why some of the queries above work and
one doesn't. If you look at 3 and 5 I'd expect them both to fail. If
it failed consistently I could get further into it.
The problem is that it's a little difficult to get the view itself
changed as it was supplied by a third party, but if it hasn't changed
and the data hasn't changed then it's got to be something else causing
the problem.
Anyway, as I said, it's a bit obscure, but if this sounds familiar I'd
be interested in your opinion.
Thanks in advance.