How to Create a User Defined Field in Select Query

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

General SQL Server Forums

New to SQL Server Programming

How to Create a User Defined Field in Select Query

Author

Topic

TWD38
Starting Member

7 Posts

Posted - 2012-02-24 : 10:20:02

Hi guys,

I have a select query that retrieves employers based on their zip code. I want to include the county name for each sub-query section. County name is not a field in my database though.

Is there a way to define the field and assign it a value? I tried this:

ELECT a.AGTY_ID_NM,
COUNTY_NAME as "SEVIER",
a.AGTY_SYS_CD

FROM DSNP.PR01_T_EMPR a

But I get an error. Surely, there is a way to do this.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-24 : 10:24:25

If country_name doesn't exist, how can you use it? I guess I don't understand your question. Please see the following links so we can help you better:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-24 : 10:24:39

Please supply the DDL, sample Data and expected results

What error are you getting?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-24 : 10:26:01

if county name is not in table where will you get it from? i think you need to add it as a field in table and populate it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TWD38
Starting Member

7 Posts

Posted - 2012-02-24 : 10:31:42

I just want to include it in my query output. I would manually define the county name in the SELECT.

Sample output:

AGTY_ID_NM COUNTY NAME AGTY_ID_NM

CITY OF QUITMAN WOOD COUNTY 2345
CITY OF MINEOLA WOOD COUNTY 2313

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-24 : 10:40:02

Not 100% sure I get it, but maybe you have the alias backwards?:


SELECT a.AGTY_ID_NM,
'SEVIER' AS COUNTY_NAME,
a.AGTY_SYS_CD
FROM DSNP.PR01_T_EMPR a

TWD38
Starting Member

7 Posts

Posted - 2012-02-24 : 10:43:32

I think I'm getting this confused with case statements where I can define a specific value for a field, but the field has to exist in the table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-24 : 10:46:55

quote:
Originally posted by TWD38

I just want to include it in my query output. I would manually define the county name in the SELECT.

Sample output:

AGTY_ID_NM COUNTY NAME AGTY_ID_NM

CITY OF QUITMAN WOOD COUNTY 2345
CITY OF MINEOLA WOOD COUNTY 2313

thats not a recommended approach as its not scalable and maintainable. If you want it to be in result why not add it as a field in table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TWD38
Starting Member

7 Posts

Posted - 2012-02-24 : 11:04:57

I don't have permissions to alter or add to the tables. I can export my results to Access and create it there though.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-25 : 13:01:59

quote:
Originally posted by TWD38

I don't have permissions to alter or add to the tables. I can export my results to Access and create it there though.

then you've keep on changing your case statement everytime a new county information have to be entered

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources