Unable to Resolve Identifier "O"."ProspRef"

Please be aware that I still get an error when running the following query
with today's nxServer snapshot.

Ken

select O.HouseRef,O.ProspRef,O.BridgingFacilities,O.Incom pleteChain,
O.AnticipatedCompDate,O.FinanceCo,O.FinanceAmount, O.FinanceContact,

O.FinanceConfirmed,O.AnticipatedExchDate,H.Address String,

substring(O.HouseRef from 1 for 3) Branch,

(SELECT NameAndAddressString from

(SELECT OrigBranchRef, NameAndAddressString from EstatePro.Prospects

where OrigBranchRef=O.ProspRef

UNION

SELECT OrigBranchRef, NameAndAddressString from Archive.Prospects

where OrigBranchRef=O.ProspRef

) yyy

) NameAndAddressString

from Offers O

left join Houses H on H.HouseRef=O.HouseRef

where H.SaleStatusInt in (1,7,4,10)

order by AddressString,NameAndAddressString

> Please be aware that I still get an error when running the following
> query with today's nxServer snapshot.

Please be aware that the point of publishing the fix list is so YOU can
check if the issue number is SUPPOSED to be fixed. Also be aware what the
fix list page says about waiting.

It should be obvious that if the fix isnt listed as having been verified and
closed, there's not much point in testing for it (and certainly not in
telling us its not done yet; we already know that .

--

Eivind Bakkestuen
Nexus Database Systems Pty Ltd

"Eivind Bakkestuen [NDD]" <Ieivind.bakkestuenHATE@nexusdb.comSPAM> wrote in
message news:45be01c4@wic040d....
>> Please be aware that I still get an error when running the following
>> query with today's nxServer snapshot.
>
> Please be aware that the point of publishing the fix list is so YOU can
> check if the issue number is SUPPOSED to be fixed. Also be aware what the
> fix list page says about waiting.
>
> It should be obvious that if the fix isnt listed as having been verified
> and closed, there's not much point in testing for it (and certainly not in
> telling us its not done yet; we already know that .
>
> --
>
> Eivind Bakkestuen
> Nexus Database Systems Pty Ltd
>
I thought it was mentioned in the fix list!

Ken

Ken Randall wrote:
> Please be aware that I still get an error when running the following query
> with today's nxServer snapshot.
>
> Ken
>
> select O.HouseRef,O.ProspRef,O.BridgingFacilities,O.Incom pleteChain,
> O.AnticipatedCompDate,O.FinanceCo,O.FinanceAmount, O.FinanceContact,
> O.FinanceConfirmed,O.AnticipatedExchDate,H.Address String,
> substring(O.HouseRef from 1 for 3) Branch,
> (SELECT NameAndAddressString from
> (SELECT OrigBranchRef, NameAndAddressString from EstatePro.Prospects
> where OrigBranchRef=O.ProspRef
> UNION
> SELECT OrigBranchRef, NameAndAddressString from Archive.Prospects
> where OrigBranchRef=O.ProspRef
> ) yyy
> ) NameAndAddressString
> from Offers O
> left join Houses H on H.HouseRef=O.HouseRef
> where H.SaleStatusInt in (1,7,4,10)
> order by AddressString,NameAndAddressString

Can use COALESCE to pick the first sub query that returns a
NameAndAddressString.

SELECT O.HouseRef,O.ProspRef,O.BridgingFacilities,O.Incom pleteChain,
O.AnticipatedCompDate,O.FinanceCo,O.FinanceAmount, O.FinanceContact,
O.FinanceConfirmed,O.AnticipatedExchDate,H.Address String,
substring(O.HouseRef from 1 for 3) Branch,
COALESCE((SELECT NameAndAddressString
from EstatePro.Prospects
where OrigBranchRef=O.ProspRef),
(SELECT NameAndAddressString
from Archive.Prospects
where OrigBranchRef=O.ProspRef))
as NameAndAddressString
FROM Offers O
LEFT JOIN Houses H ON H.HouseRef=O.HouseRef
WHERE H.SaleStatusInt IN (1,7,4,10)
ORDER BY AddressString,NameAndAddressString;

--
Brian Evans [NDX]
Ottawa, ON, CANADA
GMT-5

"Brian Evans [NDX]" <bevanson@rogers.com> wrote in message
news:45be2000$1@wic040d....
> Ken Randall wrote:
>> Please be aware that I still get an error when running the following
>> query with today's nxServer snapshot.
>>
>> Ken
>>
>> select O.HouseRef,O.ProspRef,O.BridgingFacilities,O.Incom pleteChain,
>> O.AnticipatedCompDate,O.FinanceCo,O.FinanceAmount, O.FinanceContact,
>> O.FinanceConfirmed,O.AnticipatedExchDate,H.Address String,
>> substring(O.HouseRef from 1 for 3) Branch,
>> (SELECT NameAndAddressString from
>> (SELECT OrigBranchRef, NameAndAddressString from EstatePro.Prospects
>> where OrigBranchRef=O.ProspRef
>> UNION
>> SELECT OrigBranchRef, NameAndAddressString from Archive.Prospects
>> where OrigBranchRef=O.ProspRef
>> ) yyy
>> ) NameAndAddressString
>> from Offers O
>> left join Houses H on H.HouseRef=O.HouseRef
>> where H.SaleStatusInt in (1,7,4,10)
>> order by AddressString,NameAndAddressString
>
> Can use COALESCE to pick the first sub query that returns a
> NameAndAddressString.
>
> SELECT O.HouseRef,O.ProspRef,O.BridgingFacilities,O.Incom pleteChain,
> O.AnticipatedCompDate,O.FinanceCo,O.FinanceAmount, O.FinanceContact,
> O.FinanceConfirmed,O.AnticipatedExchDate,H.Address String,
> substring(O.HouseRef from 1 for 3) Branch,
> COALESCE((SELECT NameAndAddressString
> from EstatePro.Prospects
> where OrigBranchRef=O.ProspRef),
> (SELECT NameAndAddressString
> from Archive.Prospects
> where OrigBranchRef=O.ProspRef))
> as NameAndAddressString
> FROM Offers O
> LEFT JOIN Houses H ON H.HouseRef=O.HouseRef
> WHERE H.SaleStatusInt IN (1,7,4,10)
> ORDER BY AddressString,NameAndAddressString;
>
> --
> Brian Evans [NDX]
> Ottawa, ON, CANADA
> GMT-5

Brian,

You're a star - works perfectly. Many thanks.

Ken