SP problem vid nytt fält

[sveand]

Har en SP för SQLserver och vill addera ett nytt fält jpproperty_name dvs i detta fall property.jpproperty_name

 

[Orginal oändrad]

CREATE PROCEDURE qry_show_list_new

(@city_id int)

 

AS

SELECT property.property_name, property.city_id, area.area, property.advertising_id, property.available, property.status_id, Min(property_bedroom.monthly) AS MinOfmonthly, country.currency, property.property_id, area.area_id

FROM (country INNER JOIN (city INNER JOIN (area INNER JOIN property ON area.area_id = property.area_id) ON (city.city_id = property.city_id) AND (city.city_id = area.city_id)) ON country.country_id = city.country_id) LEFT JOIN (bedroom RIGHT JOIN property_bedroom ON bedroom.bedroom_id = property_bedroom.bedroom_id) ON property.property_id = property_bedroom.property_id

WHERE (((property.end_dt)>GETDATE()) AND ((property.property_order) <> null))

GROUP BY property.property_name, property.city_id, area.area, property.property_order, property.advertising_id, property.available, property.status_id, country.currency, property.property_id, area.area_id

HAVING (((property.city_id)=@city_id) AND ((property.advertising_id)=1) AND ((property.available)<>0) AND ((property.status_id)=1))

ORDER BY property.property_order;

 

Med min ändring...

CREATE PROCEDURE qry_show_list_new

(@city_id int)

 

AS

SELECT property.property_name, property.city_id, area.area, property.advertising_id, property.available, property.status_id, Min(property_bedroom.monthly) AS MinOfmonthly, country.currency, property.property_id, area.area_id

FROM (country INNER JOIN (city INNER JOIN (area INNER JOIN property ON area.area_id = property.area_id) ON (city.city_id = property.city_id) AND (city.city_id = area.city_id)) ON country.country_id = city.country_id) LEFT JOIN (bedroom RIGHT JOIN property_bedroom ON bedroom.bedroom_id = property_bedroom.bedroom_id) ON property.property_id = property_bedroom.property_id

WHERE (((property.end_dt)>GETDATE()) AND ((property.property_order) <> null))

GROUP BY property.property_name, property.city_id, area.area, property.property_order, property.advertising_id, property.available, property.status_id, country.currency, property.property_id, area.area_id,property.jpproperty_name

HAVING (((property.city_id)=@city_id) AND ((property.advertising_id)=1) AND ((property.available)<>0) AND ((property.status_id)=1))

ORDER BY property.property_order;

 

Vilket ger följande fel:

Column 'property.jpproperty_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

 

Lägger jag till property.jpproperty_name vid group by så kan jag skapa SP men får då får jag ingen output vid exekvering. Denna ändring borde ju ge samma out put som tidigare fast med extra fält.

[Racek]

JAg antar att property_id är primary key i property. Om du börjar med att räkna:

SELECT p.property_id

ISNULL(Min(pb.monthly),0) AS MinOfmonthly

INTO #tmpprop

FROM property p,

property_bedroom pb

WHERE

p.property_id = pb.property_id

-- eller outerjoin?

-- p.property_id *= pb.property_id

AND p.available <> 0

AND p.status_id = 1

GROUP BY property_id

 

Så kan du kopla #tmpprop till övriga tabeller utan problem

 

 

 

[Racek]

Det skall vara GROUP by p.property_id

 

[Magnus Ahlkvist]

*= konstruktionen kommer att försvinna med nästa release av SQL Server, så den är inte rekommenderad.

 

--

En röst talade till mig och sade:

”Le och var glad, ty det kunde vara värre”.

Så jag log, och jag var glad.

Och det blev värre.

 

[Racek]

det spellar ingen roll för detta problem. Om du vill kan du använda left / right outerjoin.