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.
| Author |
Topic |
|
danny953
Starting Member
2 Posts |
 Posted - 2010-09-15 : 12:31:15
|
| I'm pretty new to SQL and have a pretty simple question. I have a very large table and I need to add a new field called region based on a two digit ID field that is already in the table. So for instance there's 5 or more IDs that could be part of one region ID. Also, I need to create another field that is simply the year taken out of an existing date field in a normal short format (mm/dd/yyyy). I want to pull the year out of that and create its own field. Can someone point me in the right direction? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-09-15 : 12:34:08
|
| probablyWe need to start speaking the same language thoughFill in the blanksWhen you say create a new field, you mean __________________When you say there's 5 or more IDs that could be part of one region ID. How?If you know what DDL is, post that, and some sample data goes a long way...you know what a pictures worth?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
 |
|
|
danny953
Starting Member
2 Posts |
Posted - 2010-09-15 : 12:49:26
|
| For instanceI have a field called TREEFARM_ID which is a two digit code. I need to group treefarm ids into regions which are also two digit ids.An example would be that treefarm ids CH, CR, CX, and CY all need to be grouped into Region "SG". |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-09-15 : 13:15:37
|
| Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html >> I have a very large table and I need to add a new field [sic: column] called region based on a two digit [sic: column, which might be a numeric or character data type] ID field [sic] that is already in the table. <<You need to learn why columns in SQL are nothing like the fields you used to work with in file systems. You need to learn basic data modeling; you need to be specific with data element names. We need "<something>_region" and "<something>_id" instead of vague generics. Learn ISO-11179 rules. >> So for instance there's 5 or more IDs [of what?] that could be part of one region ID. Also, I need to create another field [sic: column] that is simply the year taken out of an existing date field [sic: column] in a normal short format (mm/dd/yyyy [sic: yyyy-mm-dd is the only DATE format in Standard SQL]). <<No, this is a magnetic tape model of data. You need a "<Something>_Regions" table and not another physical column. Put Regions in their own table:CREATE TABLE FoobarRegions(region_name CHAR(10) NOT NULL, something_already_in_table_id INTEGER NOT NULL CHECK (something_already_in-table_id BETWEEN 00 AND 99), PRIMARY KEY (region_name, something_already_in-table_id));Now join them to "Very_Large" using "something_already_in_table_id" (see why we want DDL and not vague narratives?) in a VIEW. Files, records and fields are physical while tables, rows and columns can be virtual. >> I want to pull the year out of that and create its own field [sic: column]. <<You can use the DATEPART(YEAR, <expression>) function. But why do you want to waste space and time on a computed column? Just do it on the fly in a VIEW, SELECT or computed column. >> Can someone point me in the right direction? <<Get a copy of THINKING IN SETS and work on changing the way you approach SQL; it is not a magnetic tape file system.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-15 : 14:01:25
|
| "An example would be that treefarm ids CH, CR, CX, and CY all need to be grouped into Region "SG"."Have another table with Code and RegionCH, SGCR, SGCX, SGCY, SGthen JOIN that table when you need the Region code.Would that solve that?Do NOT put a new column into your code for additional data that can be deduced from existing data. Use a separate, lookup, table for that. Otherwise, sooner or later, the data will get out of step in a record and you won't know whether to trust the Code or the Region. (So called "Normalisation" of your data - if you want to Google for some more info.)"I need to create another field that is simply the year taken out of an existing date field in a normal short format (mm/dd/yyyy)."You could add a Computed Column to the table. This is not a real column, its just a forumla that is used to derive the value you want. Eg. " get the Year part from the Date Column"Personally I don't like Computed columns (they interfere with other things like Instead Of triggers - which you tends not to discover until WAY down the line ...) so I go for VIEWs for that type of thing instead, and then just JOIN the View to the Table whenever I need one of those "extra value" columns. |
|
|
|
|
|
|
|
|
Noob Question