Field+case...end+dos as statements

[iren]

Hi everyone,

Being a very new in SQL Server 2005, I happend to read somebody's code'S segment and got totally confused with what certain expressions mean....

Particularly:
1. What "ID+case when...." expression really means. Actually I am confused with "+" operator between field name and case expression

2. What "end+dos as ClaimNo" means in the expression below? My understanding is that END just closes CASE expression. However what is a role of "dos" and how this kind of "sumary" of END +dos
occurs to become an elliase? Or a table ?

This is the code's segment:
SELECT
ID+case when [LOINC Code] eq '13457-7' and ResultVal<100 then '3048F' when [LOINC Code] eq '13457-7' and ResultVal<120 then '3049F'
end+dos as CLAIMNO
,1 as LINENUM
,ID AS memid
,'QUESTLAB' AS provid

Could you, please, give me a hand?

Thank you in advance,

Iren

 

[gmmastros]

In SQL, the "+" operator can be used for mathematical addition OR string concatenation. Based on your code, it appears to be used for string concatenation.

Suppose you have a table that looks like this:

Fruit
[tt]
Color Type
----- ----
Red Apple
Green Grape[/tt]

You can concatenate your data during a select statement like this:

[tt][blue]
Select Color + ' ' + Type As YummyStuff
From Fruit
[/blue][/tt]

The output would be:

[tt]YummyStuff
----------
Red Apple
Green Grape[/tt]

Now, for the Case/When. You should think of Case/When as a conditional expression that returns a value.

For example:

Select case when [LOINC Code] eq '13457-7' and ResultVal<100 
            then '3048F'  
            when [LOINC Code] eq '13457-7' and ResultVal<120 
            then '3049F'              
            end As SomeAliasName

For each row, the case/when will be evaluated and return a string based on the value of [LOINC code] and ResultVal. In your original code, you have a mixture of string concatenation and case/when evaluation.

By the way, I think it's important to mention what happens when your Case/When does not handle ALL of your data. Specifically, imagine what would happen if ResultVal was 130. It wouldn't satisfy either of your 2 cases, so SQL would return a NULL value. When you concatenate a string with a NULL, the result is NULL. This means that it is possible that your code (as written above) could result in a NULL value being returned for the CLAIMNO column. Since you only show the select part of the query, it's possible that this situation cannot occur based on where clause criteria.

Anyway.... I hope this helps you to understand what's going on with the code. If you are still confused, please let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom