Select Using an AS Statement with a Variable

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 2000 Forums

Transact-SQL (2000)

Select Using an AS Statement with a Variable

Author

Topic

steve_b99
Starting Member

1 Post

Posted - 2003-07-02 : 05:29:05

I want to use an AS Statement in a query but using a Variable.

The statement would look something like this...

SELECT Column1, Column2 AS @Variable
FROM Table1
WHERE Column1 = 'Blah'

The variable would be determined from a Query itself so what I really want is...

SELECT Column1, Column2 AS (SELECT ColTit FROM ColHead WHERE [ID] = 1)
FROM Table1
WHERE Column1 = 'Blah'

If anyone has any ideas how to use the AS with a variable or seperate select Statement, I'd love to here from you.

Steve

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-02 : 05:56:42


SELECT Column1, Column2 AS (SELECT ColTit FROM ColHead WHERE [ID] = 1)

You can not do the above in SQL Server.
Use a variable as you suggested.


CREATE TABLE NAMES(UID SMALLINT, THENAME VARCHAR(20))
INSERT INTO NAMES (UID, THENAME)
SELECT	1, 'ILONA' UNION ALL
SELECT	2, 'AMETHYSTIUM' UNION ALL
SELECT	3, 'APHELION' UNION ALL
SELECT	4, 'ODONATA'

DECLARE @SSQL VARCHAR(1000)
DECLARE @NAME VARCHAR(1000)

SET @SSQL = ''
SET @NAME = (SELECT	THENAME FROM NAMES WHERE UID = 3)

SELECT @SSQL = @SSQL + 'SELECT SPIRITCHASER AS ' + @NAME + ' FROM AVALON'
PRINT @SSQL
EXEC (@SSQL)

DROP TABLE NAMES

Change UID value to get different column headings.

Edited by - Amethystium on 07/02/2003 07:03:23

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-02 : 08:41:06

My question is -- WHY? why are you changing the column name each time? So that is looks nice when printed in the query analyzer? Where are you using this SQL code?

why can't you say:

SELECT Column1, Column2, @Variable AS Column2Descripton
FROM Table1
WHERE Column1 = 'Blah'

or

SELECT Column1, Column2, (SELECT ColTit FROM ColHead WHERE [ID] = 1) as Column2Desc
FROM Table1
WHERE Column1 = 'Blah'

All of the information is there, you just need to look at the column "Column2Description" to find out the "name" of Column2.

If you have are writing a report or an ASP page, there's your column header in the Column2Desc field.

Don't alter the STRUCTURE of what you are returning based on the data, alter the DATA you are returning. I hope this makes a little sense.

- Jeff

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-02 : 08:56:14

Jeff,

I don't see the point of what he is doing because I don't know what he wants to do. Perhaps the exercise that he is working on requires him to have variable column headings depending on the type of result the query returns?

Anyway, he has been shown how to do it, so the choice is in his hands.

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-02 : 09:59:13

I have to say this looks mighty like a presentation issue being handled in SQL. The dynamic SQL can get around it in a roundabout manner, but the underlying queries will be left less secure and less maintainable.

If this isn't a presentation issue, I'd say the database design could benefit from reevaluation if you can.

-------
Moo.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-02 : 11:21:54

EDIT: Never mind, I think I TOTALLY missed the point.

quote:

[You can not do the above in SQL Server.
Use a variable as you suggested.

Why not?



USE Northwind
GO

CREATE TABLE myTable99 (col1 char(1))
GO

INSERT INTO myTable99
SELECT 'Q' UNION ALL
SELECT 'W' UNION ALL
SELECT 'E' UNION ALL
SELECT 'R' UNION ALL
SELECT 'T' UNION ALL
SELECT 'Y'
GO

CREATE TABLE myTable00 (col1 char(1))
GO

INSERT INTO myTable00
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
GO


SELECT col1, (SELECT col1 FROM myTable00 WHERE col1 = 'A') AS col2
  FROM myTable99

GO

DROP TABLE myTable00
GO

DROP TABLE myTable99
GO

However, you can't do



DECLARE @x char(1)

SELECT col1, @x = (SELECT col1 FROM myTable00 WHERE col1 = 'A')
  FROM myTable99

You can't select a result set and make an assignment at the same time.

Brett

8-)

Edited by - x002548 on 07/02/2003 11:23:25

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-02 : 12:33:52

Brett,

What the hell are you doing in your code may I ask!

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-02 : 13:32:43

S/He wanted to know if the could do:


SELECT Column1, Column2, (SELECT ColTit FROM ColHead WHERE [ID] = 1) as Column2Desc 
FROM Table1 
WHERE Column1 = 'Blah'

My sample just showed that it could be done.

Does it have any meaning?

Absolutley NOT...

That's what the hell the code is doing

(why do you always use that phrase?)

Brett

8-)

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-03 : 05:02:58

quote:

(why do you always use that phrase?)

I do?
This is the first time I think.

Easy though... nothing harsh intended you know.

Edited by - Amethystium on 07/03/2003 05:05:48

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources