September 16, 2011 at 2:52 pm
Ur right a BIT over my head right now ..lol
so If I use
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
where does it know to move to Tag_data and where dO I put my table joins?
BTW: The guy I was helping , does HTML and figured a way to put it in his form when null,
but now I need to figure out how to do this!!!!
September 16, 2011 at 3:01 pm
jbalbo (9/16/2011)
Hi,I have this statement:
insert into @TEMPTABLE (TAG_NAME, TAG_DATA)
select TAG_NAME = 'CUST_ALLERGIES',
TAG_DATA =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 CRT (nolock) on crt.oid = m.chemical_reference_type
INNER JOIN @Top5Allergies T ON CA.OID = T.CACA_OID_LINK
INNER JOIN CLIENTAGENCY_TO_CLIENT_ALLERGY_COLLECTION CACA (NOLOCK) ON CACA.OID_LINK = T.CACA_OID_LINK
INNER JOIN CLIENT_TO_CLIENTAGENCY_COLLECTION CAC (NOLOCK) ON CAC.OID_LINK = CACA.OID
WHERE CAC.OID = @CLIENT_OID
I want to add an isnull to it so if there is no data TAG_DATA = "No Allergies"
Thx in Advance
Joe
OK, lets try this again. This is your original post. You have learned a lot today about how this isnull logic works. You have two columns in your select statement (TAG_NAME, TAG_DATA). All you are doing is selecting two columns of information. The case statement is way to conditionally fill a single column. The way you wrote your select statement you are populating the TAG_DATA column with 'No Allergies' OR CRT.FullName + '~' + M.GENERIC_NAME. You have gotten yourself all twisted around and your mind is trying to make this really complicated. It really is not. Slow down. Breathe.
where does it know to move to Tag_data and where dO I put my table joins?
Nothing is moving, your select statement is still just a select statement. It just has a case statement for one column.
insert into @TEMPTABLE (TAG_NAME, TAG_DATA)
select TAG_NAME = 'CUST_ALLERGIES',
TAG_DATA = YOUR CASE STATEMENT GOES HERE
FROM MEDICATION M
...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 16, 2011 at 3:11 pm
I think I got the logic now, but not the sytax???
insert into @TEMPTABLE (TAG_NAME, TAG_DATA)
select TAG_NAME = 'CUST_ALLERGIES',
TAG_DATA =
case CA.MEDICATION_MONIKER
when null then 'No Allergies'
else
CRT.FullName + '~' + M.GENERIC_NAME
FROM MEDICATION M
left outer JOIN CLIENT_ALLERGY CA ON M.OID = CA.MEDICATION_MONIKER
left outer join CHEMICAL_REFERENCE_TYPE CRT on crt.oid = m.chemical_reference_type
left outer JOIN @Top5Allergies T ON CA.OID = T.CACA_OID_LINK
left outer JOIN CLIENTAGENCY_TO_CLIENT_ALLERGY_COLLECTION CACA ON CACA.OID_LINK = T.CACA_OID_LINK
left outer JOIN CLIENT_TO_CLIENTAGENCY_COLLECTION CAC ON CAC.OID_LINK = CACA.OID
WHERE CAC.OID = @CLIENT_OID
end
September 16, 2011 at 3:13 pm
Your syntax error is EXACTLY the same one from a couple posts ago. Basic syntax of a case statement...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 16, 2011 at 3:28 pm
OMG I think I got it!!!
insert into @TEMPTABLE (TAG_NAME, TAG_DATA)
select TAG_NAME = 'CUST_ALLERGIES',
TAG_DATA = case
CA.MEDICATION_MONIKER -- looks if NULL
when null then 'No Allergies'
else
CRT.FullName + '~' + M.GENERIC_NAME -- Otherwise uses these fields
end
FROM MEDICATION M
left outer JOIN CLIENT_ALLERGY CA ON M.OID = CA.MEDICATION_MONIKER
left outer join CHEMICAL_REFERENCE_TYPE CRT on crt.oid = m.chemical_reference_type
left outer JOIN @Top5Allergies T ON CA.OID = T.CACA_OID_LINK
left outer JOIN CLIENTAGENCY_TO_CLIENT_ALLERGY_COLLECTION CACA ON CACA.OID_LINK = T.CACA_OID_LINK
left outer JOIN CLIENT_TO_CLIENTAGENCY_COLLECTION CAC ON CAC.OID_LINK = CACA.OID
WHERE CAC.OID = @CLIENT_OID
Let me know what you think!!!
Thanks
Joe
September 16, 2011 at 3:32 pm
I think you are correct. I like the comments. 😉 Definitely demonstrates you understand what is happening. Remember that YOU need to understand what the code is doing. It is going to be YOUR phone that rings at 3am when the CTO says your system is failing and wants you to fix it. Hopefully you learned something through all the agony. Hope you got your problem fixed and have a great weekend.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 16, 2011 at 3:34 pm
Hey Sean REALLY appreciate it...
When can I attend your class!! 🙂
Thanks
Joe
September 16, 2011 at 3:41 pm
jbalbo (9/16/2011)
Hey Sean REALLY appreciate it...When can I attend your class!! 🙂
Thanks
Joe
Most days right here on SSC. Of course I am barely qualified to be a T.A. compared to most of the people around here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply