Simple query problem- help!
-
Hi there,
I'm a newbie and need some expert advice- I'm pretty sure this is a simple problem...
I want to use the REPLACE function in the select statement, which also has a MAX Function in the select statement but it complains when i try to assign an alias to it
For example,
SELECT REPLACE(d.agtpostcode, CHAR(32), null) as pcode, c.agtname as name, MAX(d.agtrefno) as maxcode
FROM EntaLive.dbo.agent as c ,EntaLive.dbo.AgentAddress as d
WHERE c.agtrefno = d.agtrefno
GROUP BY pcode, name
this is part of a subquery, but having tried to run just this bit it gives the same complaint- that pcode and name are invalid column names! I think it's more to do with the naming, rather than the fact that I'm using a replace function..
Any advice would be very much appreciated!!
Thanks,
Alex
-
Your problem comes from the fact that you cannot use alias in the group by clause, this is allowed only in the order by clause.
SELECT REPLACE(d.agtpostcode, CHAR(32), '') as pcode, c.agtname as name, MAX(d.agtrefno) as maxcode
FROM EntaLive.dbo.agent as c ,EntaLive.dbo.AgentAddress as d
WHERE c.agtrefno = d.agtrefno
GROUP BY REPLACE(d.agtpostcode, CHAR(32), ''), c.agtname
I replaced null by'' because you'd get nulls instead of varchars in that column when a replace would occur.
-
Hiya, that's brilliant- thanks for the reply. My next question relates to the null part.
I'm using a linked server- so that patr of the statement actualyl looks like..
SELECT * FROM OPENQUERY(BARBSLIVE, 'SELECT REPLACE(d.agtpostcode, CHAR(32), null) as pcode, c.agtname as name, MAX(d.agtrefno) as maxcode
FROM EntaLive.dbo.agent as c ,EntaLive.dbo.AgentAddress as d
WHERE c.agtrefno = d.agtrefno
GROUP BY REPLACE(d.agtpostcode, CHAR(32), null) , c.agtname')
Now, if I us '' instead of null, it will escape out- ive tried using \ but that didnt seem to work either (hence why i tried using null).
What can i do to use '' to replace the spaces, without escapignfrom this query?
Thanks again for the reply- you've been most helpful!
Alex
-
You need to double each apostrophe in the query.
SELECT * FROM OPENQUERY(BARBSLIVE, 'SELECT REPLACE(d.agtpostcode, CHAR(32), '''') as pcode, c.agtname as name, MAX(d.agtrefno) as maxcode
FROM EntaLive.dbo.agent as c ,EntaLive.dbo.AgentAddress as d
WHERE c.agtrefno = d.agtrefno
GROUP BY REPLACE(d.agtpostcode, CHAR(32), '''') , c.agtname')
-
Fantastic!
I shall be setting up a shrine in your honour this evening 😉
Alex
-
Nah...
For the whole month... so it makes more sens
. -
Remi in honor of your help on this post and others.. why just 2 days (the month is almost over)
. Perhaps this month and next month. It could be more but why give you a big head.\
-
I just like to help... I don't need no shrine.
-
Be careful! Last time I said something similar, David Burrows called me a "sycophantic fool from Germany"
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Well he knows you
. -
Yes, we have shared here more than one cyberspace beer and laughter.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
God!!! I just realized how much of bunch of Geeks we are....
-
...yes, and it's Friday and here in my place just about the right time to go home.
Enjoy your weekend!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply