Whereabouts do I put this WHERE clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Whereabouts do I put this WHERE clause

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-04-20 : 05:30:44

Hi,

I have this SQL which works


SELECT * FROM (SELECT     P.vehicleref, P.capid, P.manufacturer, P.model, P.derivative, P.additionalfreetext, P.cvehicle_shortmodtext, NMF.term, NMF.milespa,                                   NMF.maintained, NMF.ch, p.source, ROW_NUMBER() OVER (PARTITION BY P.VehicleRef  ORDER BY NMF.ch) AS MinCH
FROM (SELECT * FROM vwAllMatrixWithLombardAndShortModel
WHERE source <> 'web' AND vehicleref IN
(SELECT vehicleref FROM tblDealOfTheDay)) P INNER JOIN
dbPubMatrix..tblNewMatrixFigures NMF ON P.VehicleRef = NMF.[VehicleRef]) Q WHERE     MinCH = 1

I need to add this

WHERE nmf.ch > 0

Where does that clause need to go, i've tried various places all without success. Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 05:35:33

[code]SELECT *
FROM (
SELECT P.vehicleref,
P.capid,
P.manufacturer,
P.model,
P.derivative,
P.additionalfreetext,
P.cvehicle_shortmodtext,
NMF.term,
NMF.milespa,
NMF.maintained,
NMF.ch,
p.source,
ROW_NUMBER() OVER (PARTITION BY P.VehicleRef ORDER BY NMF.ch) AS MinCH
FROM vwAllMatrixWithLombardAndShortModel AS p
INNER JOIN dbPubMatrix..tblNewMatrixFigures as NMF ON P.VehicleRef = NMF.[VehicleRef]
WHERE p.source <> 'web'
AND p.vehicleref IN (SELECT vehicleref FROM tblDealOfTheDay)
and nmf.ch > 0

) as Q
WHERE MinCH = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-04-20 : 05:40:07

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources