January 15, 2008 at 11:37 am
When trying to use a subquery to return just the first row, I receive the following errors:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "P.PID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "P.County_Code" could not be bound.
SELECT P.street_number_full AS StreetNumber, P.street_pre_direction AS StreetDirPrefix,
P.street_name_only AS StreetName, SH.purchase_price, SH.Deed_Type, SH.Close_Date
FROM PLATpid P LEFT OUTER JOIN PLATcharacteristics C
ON C.pid = P.pid AND C.county_code = P.county_code AND C.bldg_no = 1 Left Outer Join
PlatLegal l on P.pid = l.pid AND P.county_code = l.county_code LEFT OUTER JOIN
PLATCounty S ON S.CountyID = C.County_Code LEFT OUTER JOIN
PlatlookupProptyTp pt on p.plat_property_type_code = pt.code and P.county_code = pt.county_code LEFT OUTER JOIN
featuremaster pg on c.garage_type = pg.platcode LEFT OUTER JOIN
featuremaster pa on c.architectural_type = pa.platcode LEFT OUTER JOIN
(select top 1 SH1.PID, SH1.County_Code, SH1.purchase_price, SH1.Deed_Type, SH1.Close_Date
from vw_Sale_History SH1 WHERE P.PID=SH1.PID AND P.County_Code = SH1.County_Code
order by Close_Date DESC) SH
ON P.PID = SH.PID AND P.County_code = SH.County_Code
WHERE p.pid = '228850801001'
Any help is greatly appreciated.
Thanks!
January 15, 2008 at 3:48 pm
I would try to solve this by removing the subquery , creating an CTE with the aboce stuff . and selecting and filtering the CTE.
With myCTE as
(
SELECT P.street_number_full AS StreetNumber,
P.street_pre_direction AS StreetDirPrefix,
P.street_name_only AS StreetName,
SH.purchase_price,
SH.Deed_Type,
SH.Close_Date
FROM PLATpid P
LEFT OUTER JOIN PLATcharacteristics C ON C.pid = P.pid AND C.county_code = P.county_code AND C.bldg_no = 1
Left Outer Join PlatLegal l on P.pid = l.pid AND P.county_code = l.county_code
LEFT OUTER JOIN PLATCounty S ON S.CountyID = C.County_Code
LEFT OUTER JOIN PlatlookupProptyTp pt on p.plat_property_type_code = pt.code and P.county_code = pt.county_code
LEFT OUTER JOIN featuremaster pg on c.garage_type = pg.platcode
LEFT OUTER JOIN featuremaster pa on c.architectural_type = pa.platcode
)
select * from my_cte C
but it's kind of hard to figure out what you are trying to accomplish with the query with out having the tables and data to test 🙂
kgunnarsson
Mcitp Database Developer.
January 18, 2008 at 10:56 am
Thanks for the help! After a little more research, that worked perfect!
January 18, 2008 at 2:15 pm
btw, the reason you received the error is that a derved table in the from clause cannot see any other members of the from clause.
[font="Courier New"]
SELECT P.street_number_full AS StreetNumber, P.street_pre_direction AS StreetDirPrefix,
P.street_name_only AS StreetName, SH.purchase_price, SH.Deed_Type, SH.Close_Date
FROM PLATpid P LEFT OUTER JOIN PLATcharacteristics C
ON C.pid = P.pid AND C.county_code = P.county_code AND C.bldg_no = 1 Left Outer Join
PlatLegal l on P.pid = l.pid AND P.county_code = l.county_code LEFT OUTER JOIN
PLATCounty S ON S.CountyID = C.County_Code LEFT OUTER JOIN
PlatlookupProptyTp pt on p.plat_property_type_code = pt.code and P.county_code = pt.county_code LEFT OUTER JOIN
featuremaster pg on c.garage_type = pg.platcode LEFT OUTER JOIN
featuremaster pa on c.architectural_type = pa.platcode LEFT OUTER JOIN
(select top 1 SH1.PID, SH1.County_Code, SH1.purchase_price, SH1.Deed_Type, SH1.Close_Date
from vw_Sale_History SH1 WHERE P.PID = SH1.PID AND P.County_Code = SH1.County_Code
order by Close_Date DESC) SH
ON P.PID = SH.PID AND P.County_code = SH.County_Code
WHERE p.pid = '228850801001'
[/font]
the SH table expression fails because it references P.PID (and P.County_Code).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply