Referencing internal field names in VIEW

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

SQL Server Development (2000)

Referencing internal field names in VIEW

Author

Topic

mark.f1man
Starting Member

2 Posts

Posted - 2008-12-03 : 00:16:38

Hi, not completely sure how to explain this. Reasonably new to SQL server (2000). Am trying to convert some Access Queries into SQL Views.

Can a View reference field names that have been defined in the View (note this works in an Access query). Say something like:

SELECT
{expression} AS Field1
{expression} AS Field2
{expression} AS Field3
Field1 + Field2 + Field3 AS Field4
FROM
...

Field1, Field2 & Field3 are defined in the View. I am then trying to concatenate those 3 fields into Field4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 00:49:13

In T_SQL you need to do like this

SELECT Field1, Field2, Field3,Field1 + Field2 + Field3 AS Field4
FROM
(
SELECT
{expression} AS Field1
{expression} AS Field2
{expression} AS Field3

FROM
...
)t

mark.f1man
Starting Member

2 Posts

Posted - 2008-12-03 : 17:13:16

Thanks visakh16!
Helps when you know what you are doing.
Curious as to why the t is needed at the end?
FROM
...
)t
I doesn't work without it!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-03 : 17:17:35

This is because it is a derived table and it needs a name.
You can also take Paul or Mary.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 23:36:28

quote:
Originally posted by mark.f1man

Thanks visakh16!
Helps when you know what you are doing.
Curious as to why the t is needed at the end?
FROM
...
)t
I doesn't work without it!

t is table alias. its is needed to refer to derived table we created

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources