Access moved to SQL Server - Select Replace problem

[jamesnr]

Hi I'm hoping an expert can help me.

I moved an Access database to SQL, fine. However one of the SQL queries now fails. Works frin with the JET 4.0 SQL but not SQL Server. I think it needs to be broken into more than one step but not sure how to go about it. The statement is:

SELECT replace(pSection,select sectionName from sections where sectionID=pSection), pName
FROM products
WHERE pID='PWBTZ1';

the pID is usuall a variable passed as part of the ASP wrapped around the database but in this case I've provided a hard example.

Obviously in SQL the replace function needs 3 parameters but it wont except the embedded Select.

Any ideas how best to proceed??

Reece

http://www.kidzcraft.co.uk

 

[PHV]

Works frin with the JET 4.0 SQL
Really ?
I wonder why you not simply use a join:
SELECT S.sectionName, P.pName
FROM products P
INNER JOIN sections S ON P.pSection = S.sectionID
WHERE P.pID='PWBTZ1'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jamesnr]

Many thanks!! That will work..