October 18, 2010 at 6:30 am
I don't know that this is SQL 2008 specific, but that's the version I am using in this situation.
I have a query that is using a Case statement to return a value based on the value of a field. See below:
(pay no attention to which number it is, Diag1, Diag2, etc. I have 12 of them and have tried different things on different fields to see the effect)
Case
when IsNumeric(Diag1) = 1 Then 3
when Left(Diag1,1) = 'V' Then 4
when Left(Diag1,1) = 'E' Then 5
End As CodeType1,
When this runs it evaluates properly for the values accounted for in the code. If the value in the Diag1 field does not meet one of the case criteria though, I get a "NULL" in the results. Management wants this to be nothing, a zero-length string. OK, I'll just put in an Else ''.
I did, and now get a 0 (zero) when that field does not meet any of the criteria.
case
When IsNumeric(Diag5) = 1 Then 3
When Left(Diag5,1) = 'V' Then 4
When Left(Diag5,1) = 'E' Then 5
Else ''
End As CodeType5,
Have also tried this:
case
When IsNumeric(Diag5) = 1 Then 3
When Left(Diag5,1) = 'V' Then 4
When Left(Diag5,1) = 'E' Then 5
Else ' '
End As CodeType5,
to return a single space if none of the other criteria are met, and still get a zero (0).
I have ruled out the IsNumeric function causing this, as this exhibits the same behavior:
case
When Left(Diag2,1) = 'V' Then 4
When Left(Diag2,1) = 'E' Then 5
Else ''
End As CodeType2,
If I do this:
case
When IsNumeric(Diag3) = 1 Then 3
When Left(Diag3,1) = 'V' Then 4
When Left(Diag3,1) = 'E' Then 5
Else '20'
End As CodeType3,
I get a "20" in the results if nothing else applies.
Why do I get a "0" in my results when I am trying to return a zero-length string ('')?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
October 18, 2010 at 6:39 am
A case expression can only return a single data type. As your first case expression returns an integer, all other results must be integer. As a blank string is implicitly converted to zero, you will get a zero returned.
Examine the results below:
SELECT CASE
When 1=2 Then 3
Else ''
End As Col --returns 0 as the first type is integer and a blank string is implicitly converted to zero
SELECT CASE
When 1=2 Then 3
Else 'some other value'
End As Col --returns error as this can't be converted to an integer
SELECT CASE
When 1=2 Then '3'
Else ''
End As Col --returns a blank string as the first result is now a character value
October 18, 2010 at 6:55 am
OK, thanks.
This does work:
case
When IsNumeric(Diag2) = 1 Then '3'
When Left(Diag2,1) = 'V' Then '4'
When Left(Diag2,1) = 'E' Then '5'
Else ''
End As CodeType2,
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
October 19, 2010 at 2:21 pm
CELKO (10/18/2010)
1) Learn the differences between rows and records, columns and fields; it will help.
Apologies for going off topic here, but I am confused by this statement above.
BOL states http://technet.microsoft.com/en-us/library/ms189084(SQL.90).aspx
"A table definition is a collection of columns. In tables, data is organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field within the record. "
so what is the difference ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 19, 2010 at 4:46 pm
gah (10/19/2010)
CELKO (10/18/2010)
1) Learn the differences between rows and records, columns and fields; it will help.Apologies for going off topic here, but I am confused by this statement above.
BOL states http://technet.microsoft.com/en-us/library/ms189084(SQL.90).aspx
"A table definition is a collection of columns. In tables, data is organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field within the record. "
so what is the difference ?
I have to agree with Joe on this one. Not all rows are also records. In fact, in a properly designed database, most rows are not complete records. Rather, they are a part of a record.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2010 at 9:16 am
Jeff Moden (10/19/2010)
gah (10/19/2010)
CELKO (10/18/2010)
1) Learn the differences between rows and records, columns and fields; it will help.Apologies for going off topic here, but I am confused by this statement above.
BOL states http://technet.microsoft.com/en-us/library/ms189084(SQL.90).aspx
"A table definition is a collection of columns. In tables, data is organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field within the record. "
so what is the difference ?
I have to agree with Joe on this one. Not all rows are also records. In fact, in a properly designed database, most rows are not complete records. Rather, they are a part of a record.
Given that the OP seems used to the terminology of spreadsheets, might be a good idea to give an example of your statement "Rather, they are part of a record." 🙂
I'll give it a try, but please correct me if I'm wrong.
In a database, you have the record of all the information of an account. This would include the account number, the name, address, phone, orders, etc.
In an spreadsheet, you can have that all on one row. But what if the guy has a shipping address and a billing address? A rather common affair. In the spreadsheet, you would just put in extra columns and have something like ShipAddress and BillAddress.
However, that isn't the best of designs because what if there are three shipping addresses depending on what is being ordered? Do you add ShipAddress2 and ShipAddress3 to your spreadsheet? Pretty soon it gets rather cumbersome. Thus, the invention of the Relational Database.
So, this is when you split off your addresses into a separate table, Addresses. As a result, Accounts will no longer have the Address data. The row in Accounts, while still unique, doesn't represent ALL of the record, just a part of it.
In the Addresses table, you now have the address along with the Account number (foreign key that links back to the Accounts table) along with a tag on whether it is a shipping or billing address and any additional information you would need to pull up the right shipping address for the right order.
In the Addresses table, you probably have unique rows (different addresses for the companies) but again, you only have a part of the overall record.
The difference in terminology between rows and columns as opposed to records and fields is, at a glance, a minor one, however, there is a difference. A record generally represents all the data about an entity of some sort. The record of a company's order isn't necessarily a single row of data in the database. The record is stored in several tables (if it is halfway-decently designed) and thus in several rows.
Because you are dealing with relational databases; all data in a table is related to each other (of similar type - like only addresses in an address table) and tables are related to each other (relations between Accounts, Addresses and Orders for example), the data is split up to avoid redundancy and thus you have rows and columns instead of records and fields. It is a viewpoint that will help you think with the rest of the stuff that comes with relational databases.
My apologies if you already know this. I'm hoping this answers the question a bit more thoroughly than Jeff's answer and simply enough for anyone else reading this thread who might be totally new to relational databases. Your question of what's the difference is a valid one, I'm just trying to provide an example for greater ease of understanding.
Hope I didn't step on your toes, Jeff. 😀
-- Kit
October 20, 2010 at 11:55 am
CELKO (10/20/2010)
...Columns hold values, not values or formulas.
Ah, the vagueness of the English language... I don't quite understand your last sentence, Joe.
Are you saying that columns can hold one of three things, Values or Not Values or Formulas?
Or is it that Columns hold only hold Values, that if it is a "not value" or "formula" that it isn't allowed?
Since I didn't see a a definition of what would be considered a value and what would be a "not value", the only thing I can think of that is a "not value" would be NULL. Is that correct? Are you saying that a column can hold a value, NULL or a formula (three different things)?
Just trying to understand your post for a better understanding of databases and how to work with them/design them.
Edit: to fix my own grammar to be more understandable. 🙂
-- Kit
October 21, 2010 at 9:18 pm
Kit G (10/20/2010)
Hope I didn't step on your toes, Jeff. 😀
Nope... you did just fine and your great explanation is where I was going with it all. Very well done and thanks for saving me a wad of time with your explanation. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2010 at 9:22 pm
Kit G (10/20/2010)
CELKO (10/20/2010)
...Columns hold values, not values or formulas.
Ah, the vagueness of the English language... I don't quite understand your last sentence, Joe.
Are you saying that columns can hold one of three things, Values or Not Values or Formulas?
Or is it that Columns hold only hold Values, that if it is a "not value" or "formula" that it isn't allowed?
Since I didn't see a a definition of what would be considered a value and what would be a "not value", the only thing I can think of that is a "not value" would be NULL. Is that correct? Are you saying that a column can hold a value, NULL or a formula (three different things)?
Just trying to understand your post for a better understanding of databases and how to work with them/design them.
Edit: to fix my own grammar to be more understandable. 🙂
I think he means to say that columns can only hold values whereas a lot of "spreadsheet thinkers" believe a column can hold either a value or a formula that produces a value. Of course, my next question would be... what, then, is a non-persisted computed column other than a calculated single-column view associated with the schema of a table at the row level?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2010 at 9:23 pm
CELKO (10/20/2010)
In 25 words or less it is "Logical versus Physical", but it goes beyond that. If you do not have a good conceptual model, you hit a ceiling and cannot get past a certain level of competency.A file is made up of records, records are made up of fields. Files are independent of each other, while tables in a database are interrelated. You open an entire database, not tables; you open individual files. The original idea of a database was to collect data in a way that avoided redundant data in too many files and not have it dependent on a particular programming language.
A file is ordered and can be accessed by a PHYSICAL location, while a table is not. "first record", "last record", and "next n records" make sense in a file but not in a table. Rows have no magical "id" or "row_id' like the physical record number in a file.
In fact, columns, rows and tables do not have to have any physical existence at all. VIEWs, derived tables and CTEs are virtual tables, I can have computed columns, e5tc.
Look at how many times we get a posting where someone wants to sequentially read a table. In a procedural language, "READ a,b,c FROM FileX;" does not give the same results as "READ b,c,a FROM FileX;" and some languages will let you write "READ a,a,a FROM FileX;"
A file is usually associated with a particular language -- ever try to read a FORTRAN file with a COBOL program? A database is language independent; the internal SQL datatypes are converted into host language data types.
A field exists only because of the program reading it; a column exists becasue it is in a table in a database. A field is fixed or variable length, can repeated with an OCCURS in COBOL, etc. It does not have to be a scalar value like a column. A field can change datatypes (union in 'C', VARIANT in Pascal, REDEFINES in COBOL); a column cannot. You have no idea whatsoever how a column is represented internally; you never see it. SQL is more abstract than a file system; you think of a number as a NUMBER, and not as a physical string of numerals. you think of a date as a DATE, and not as a physical string of numerals and local punctuation marks.
I wish that more programmers had worked with a magnetic tape system. It is based on physical contiguous storage for everything. This is where the idea of a primary key came from. Everything in a tape system depends the files being sorted on the same key, so you can merge data. It was awhile before Dr. Codd changed his mind and said that all keys are keys, and we don't need a special one in a relational database, like you did in a tape system.
Dr. Codd defined a row as a representation of a fact. A record is usually a combination of a lot of facts. That is, we don't normalize a file; you stuff data into it and hope that you have everything you need for an application.
Rows and columns have constraints. Records and fields can have anything in them and often do!! Talk to anyone who has tried to build a data warehouse about that ...
The other "un-learning" that I see when I teach an SQL class is with people who learned spreadsheets. They think that tables are made of rows and columns; no, tables are made of rows and rows are made of columns. Columns hold values, not values or formulas.
Nicely done, Joe. Seriously.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2010 at 1:15 pm
Jeff Moden (10/21/2010)
Kit G (10/20/2010)
CELKO (10/20/2010)
...Columns hold values, not values or formulas.
Ah, the vagueness of the English language... I don't quite understand your last sentence, Joe.
Are you saying that columns can hold one of three things, Values or Not Values or Formulas?
Or is it that Columns hold only hold Values, that if it is a "not value" or "formula" that it isn't allowed?
Since I didn't see a a definition of what would be considered a value and what would be a "not value", the only thing I can think of that is a "not value" would be NULL. Is that correct? Are you saying that a column can hold a value, NULL or a formula (three different things)?
Just trying to understand your post for a better understanding of databases and how to work with them/design them.
Edit: to fix my own grammar to be more understandable. 🙂
I think he means to say that columns can only hold values whereas a lot of "spreadsheet thinkers" believe a column can hold either a value or a formula that produces a value. Of course, my next question would be... what, then, is a non-persisted computed column other than a calculated single-column view associated with the schema of a table at the row level?
Ah. That would make sense then. Guess I've left my Excel Spreadsheet days far enough behind that I wasn't thinking of that. 🙂 But yeah, since you can have calculated columns that are non-persisted, that does seem to be equal to the formula in a field in a spreadsheet. 😀
-- Kit
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply