SELECT column alias

Question

SELECT column alias

Paul_J

Hall of Fame

Points: 3698
More actions
July 25, 2006 at 10:36 pm

#113110

Hi all,
I currently have a query like this:
SELECT dbo.MyFunction(a) as Column1, b as Column2, (case when dbo.MyFunction(a)=’xyz’ then ‘hello’) as Column3
FROM Table1
As you can see, I'm calling the function twice which will return the same value. I was hoping I could use the alias I gave to the first function call 'Column1' in my case statement rather than calling the func again. Something like:
SELECT dbo.MyFunction(a) as Column1, b as Column2, (case when Column1=’xyz’ then ‘hello’) as Column3
FROM Table1
But this doesn't work.
Anyone got any ideas?
Thanks,
Paul

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lance Sanders SSC Eights! Points: 903 More actions · Answer 1

Try this...

SELECT Column1, Column2, (case when Column1='xyz' then 'hello' end) as Column3
FROM (
  SELECT dbo.MyFunction(a) as Column1, b as Column2
  FROM Table1
) as x

Sergiy SSC Guru Points: 110209 More actions · Answer 2

SELECT Column1, Column2, case when Column1=’xyz’ then ‘hello’ end as Column3

FROM (

SELECT dbo.MyFunction(a) as Column1, b as Column2

FROM Table1

) dt

_____________
Code for TallyGenerator

Paul_J Hall of Fame Points: 3698 More actions · Answer 3

Thanks for the info guys.

One thing though, I did not get any performance boost as expected.

Know why this is the case?

Or am I on another planet?

Sergiy SSC Guru Points: 110209 More actions · Answer 4

Probably your function contains SELECT(s) from another table(s) by the value in rows of Table1.

They name it "hidden cursor" and not recommend to use anywhere.

If it's true I would suggest to change UDF to VIEW and jon it to Table1 by column [a].

_____________
Code for TallyGenerator

SELECT column alias

Cookies on SQLServerCentral