select

TAG_NAME = 'CUST_ALLERGIES',

case CA.MEDICATION_MONIKER

when null then 'No Allergies'

else

CRT.FullName + '~' + M.GENERIC_NAME

end --you have to end your case statement

You really need to use BOL. Here is the article for the...

Look at my example of the case statement from several posts ago. A case statement is just a way to conditionally get a value in your query.

select

[other columns],

case [look at...

jbalbo (9/16/2011)

---

Hi..

So I got the Nolocks and inner joins cleaned up

insert into @TEMPTABLE (TAG_NAME, TAG_DATA)

select TAG_NAME = 'CUST_ALLERGIES',

TAG_DATA =CRT.FullName + '~' + M.GENERIC_NAME

FROM MEDICATION M

left outer JOIN CLIENT_ALLERGY...

As Tom and I have pointed out you will have to change your join to a left from an inner. You should read that article I linked about dirty reads....

One other point. Why are you using the nolock hint on every table? http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

jbalbo (9/16/2011)

---

Hi Sean,

Thanks for the advice..

SO I set it like:

TAG_DATA = isnull(CRT.FullName + '~' + M.GENERIC_NAME)

FROM MEDICATION M WITH (NOLOCK)

INNER JOIN CLIENT_ALLERGY CA ON M.OID = CA.MEDICATION_MONIKER

Inner join CHEMICAL_REFERENCE_TYPE...

Well you didn't specify what value to use when it is null.

TAG_DATA = isnull(CRT.FullName + '~' + M.GENERIC_NAME)

Isnull requires two arguments and you only gave it one. Here is the...

sqlnaive (9/16/2011)

---

I tried this function with almost same code but seems its not giving all the records. Also after the first record all records are coming with a space in...

TAG_DATA = isnull(CRT.FullName + '~' + M.GENERIC_NAME, 'No Allergies')

--EDIT--

You might also need to change your join to CLIENT_ALLERGY from an inner join to a left join?

Hi and welcome to SSC. The point Cadavre was making is that we don't know your data and there is nobody who is going to translate business rules into your...

Not to mention how would you do that when there is more than 1 row? Which row of data is the correct row to use as the column name? The...

The thread itself is not all that interesting but the picture fits 100%.

http://www.sqlservercentral.com/Forums/Topic1171708-338-1.aspx

Credit to Lowell for the photo.

Lowell that picture is awesome. It fits perfectly in another thread from a couple days ago. I will have to "steal" your pic and paste it in there.

WOW!!! The logic in this processing is row by row when all that needs to happen is two single update statements. If you...